Passing Variables without making them Global

[Morn]

I feel that I am forever asking questions in this place and never able to help answer other peoples.

Alas:

I want to pass a variable from form 1 to form 2, the information that the variable is to contain is a customer number that a SQL statement has pulled out of a database for me.

Having searched for 2 hrs solid and only being able to pass a variable with nothing in it to the new form I have surrendered.

I need the code or a step by step to get it, please help, I'm on the edge.

Here's the code that I have already:

To retrieve the data (code in form 1)

'retieves the customerID for next form
            Dim da As New OleDb.OleDbDataAdapter("SELECT customerID from tblCustomer WHERE customerfirstname = firstname AND customersurname = surname AND postcode = postcode", connect)

            Dim ds As New DataSet

            da.Fill(ds)

            Dim f2 As New bookingfrmPt1
            f2.custID = da

To store variable in form 2:

 Public custID

followed by a sql insert statement later in the form.

Thanks in advanced for any help, and how long does it take to understand this stuff. I am still so lost its scary.

Graham

 

[lingsn]

1) You are passing th da, which is oleDbDataAdapter, is this what you want?
2) what is the custID in form2's data type?
3) Below is the example, but not perfect (non-OOP):

            'retieves the customerID for next form
            Dim da As New OleDb.OleDbDataAdapter("SELECT customerID from tblCustomer WHERE customerfirstname = firstname AND customersurname = surname AND postcode = postcode", connect)

            Dim ds As New DataSet

            da.Fill(ds)

            Dim f2 As New bookingfrmPt1
            If ds.Tables(0).Rows.Count() > 0 Then
               f2.custID = ds.Tables(0).Rows(0).item(0)
            End If

Public custID as string = string.empty

Anywhere, it is recommended that using of the property to achieve the encapsulation.

 

[Morn]

Ok I think that I understand what you are trying to get at here, in answer to your questions.

1) You are passing the da, which is oleDbDataAdapter, is this what you want?

A) No i want to pass the result of the query to a variable and then pass that value into the next form as it is need to complete a booking in the booking table

2) what is the custID in form2's data type?

A) In the database it is set to number, as a variable I was going to declare it as a integer.

I have adapted my code a little from what was posted before, in fact I believe you have also helped me on another post. The code now utilises a DataReader.

Dim sqlsearch As String = "SELECT TOP 1 customerID FROM tblCustomer ORDER BY customer DESC"

            Dim command1 As New System.Data.OleDb.OleDbCommand(sqlsearch)

            Dim rs As System.Data.OleDb.OleDbDataReader = command1.ExecuteReader

            Do While (rs.Read)

            Loop

Would your suggested code still work in this instance?

Thanks again

Graham

 

[lingsn]

Well, it depends on the business logic that you want to implement. The code I suggested might have more that 1 records exist, where by your latest code will select only 1 of the last record. Since it is only 1 record, you may use this to get the result also.

If rs.Read then
 
end if

 

[Morn]

I do only need one piece of data in this instance.

Lastly, do i still need to create a data set to store the result before i place it as a variable or can i just do something like.

'custID holds the result from rs
Dim custID = rs

I'm sure that that is not correct but I think it illustrates my question.

Sorry to be so demanding, I just wished they covered this in uni.

Graham

 

[lingsn]

You can explore

Dim custID as string
custID = [SIZE=2][SIZE=2]command1.ExecuteScalar()[/SIZE][/SIZE]

if you need only one piece of data.

 

[Morn]

This is the best I got, I can get the passed variable to show 0 every time but other than that I am stuck.

I have also tried the method on this site: http://www.developerfusion.co.uk/show/4278/2/

Where is this going wrong?

'Selects that last entry that was inserted into the database
            Dim sqlsearch As String = "SELECT TOP +1 customerID FROM tblCustomer WHERE customerID = postcode ORDER BY customer DESC"

            Dim command1 As New System.Data.OleDb.OleDbCommand(sqlsearch)

            Dim rs As System.Data.OleDb.OleDbDataReader = command1.ExecuteReader

            If rs.Read Then

            End If


            Dim custID As String
            custID = command1.ExecuteScalar()

 

[lingsn]

I think is in this way

            'Selects that last entry that was inserted into the database
            Dim sqlsearch As String = "SELECT TOP 1 customerID FROM tblCustomer WHERE customerID = postcode ORDER BY customer DESC"

            Dim command1 As New System.Data.OleDb.OleDbCommand(sqlsearch,dbConnection)

            Dim custID As String
            custID = command1.ExecuteScalar()

 

[Morn]

I'm am lost for what to do here, I have all this and only the insert section works.

This is the code from the 1st form:

  Private Sub newcusnextButton_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles newcusnextButton.Click

        ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
        'Inputs the variable to the database
        'creates storage variables for the input fields
        Dim title As String = titleList.Text
        Dim firstname As String = firstnameText.Text
        Dim surname As String = surnameText.Text
        Dim houseNo As String = housenoText.Text
        Dim address1 As String = address1Text.Text
        Dim address2 As String = address2Text.Text
        Dim county As String = countyComboBox.Text
        Dim postcode As String = postcode1Text.Text
        Dim DofBday As Integer = dayList.Text
        Dim DofBmonth As String = monthList.Text
        Dim DofByear As Integer = yearText.Text
        Dim telephone As String = telephoneText.Text

        ' Creates the OleDb connection
        Dim connect As New OleDb.OleDbConnection
        connect.ConnectionString = "PROVIDER=Microsoft.Jet.OLEDB.4.0;Data Source = C:\Holiday.mdb"

        'Creates a variable to store the sql ? says look for the value in the parameter
        Dim sql As String = "INSERT INTO tblCustomer (Title,firstname,surname,houseNo,address1,address2, county,postcode,DofBday,DofBmonth,DofByear, telephone) VALUES (?,?,?,?,?,?,?,?,?,?,?,?)"

        'Creates a variable command so that the sql and connection variables are run
        Dim command As New System.Data.OleDb.OleDbCommand(sql, connect)

        'insted of using a concat use the param value
        command.Parameters.AddWithValue("@Title", title)
        command.Parameters.AddWithValue("@firstname", firstname)
        command.Parameters.AddWithValue("@surname", surname)
        command.Parameters.AddWithValue("@houseNo", houseNo)
        command.Parameters.AddWithValue("@address1", address1)
        command.Parameters.AddWithValue("@address2", address2)
        command.Parameters.AddWithValue("@county", county)
        command.Parameters.AddWithValue("@postcode", postcode)
        command.Parameters.AddWithValue("@DofBday", DofBday)
        command.Parameters.AddWithValue("@DofBmonth", DofBmonth)
        command.Parameters.AddWithValue("@DofByear", DofByear)
        command.Parameters.AddWithValue("@telephone", telephone)

        'opens connection
        connect.Open()

        MsgBox("A Connection to the Database is now open")

        Try
            'runs the command variable
            command.ExecuteNonQuery()

        Catch exceptionObject As Exception

            MessageBox.Show(exceptionObject.Message)

            ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
            'Selects that last entry that was inserted into the database
            Dim sqlsearch As String = "SELECT TOP +1 customerID FROM tblCustomer WHERE customerID = postcode ORDER BY customer DESC"

            Dim command1 As New System.Data.OleDb.OleDbCommand(sqlsearch)

            Dim rd As System.Data.OleDb.OleDbDataReader = command1.ExecuteReader

            While rd.Read()

            End While
            rd.Close()

            ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

            'closes(connection)
            connect.Close()
        End Try


        MsgBox("The Connection to the Database is now Closed")


        bookingfrmPt1.Show()

        Me.Close()

    End Sub

This is the code where i want to retrieve the variable to

Public Class bookingfrmPt1

    Public custID As Integer

    Private Sub bookingfrmPt1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        MsgBox(custID)
    End Sub

If i am missing something here which I am sure I am can someone help.

lingsn you have been superb and have helped me get most of it working, but the final bit of getting the variable value into form 2 just isn't happening and it is hampering the next stage as it relys on the variable data (customerID - autonumber) as a foreign key for the insert statement in form 2.

I know that its something simple, but the resources out there are sparse.

Thanks Again
Graham

 

[lingsn]

Well, no worry, learn it slowly.

From the code you submit, may be you change this part:

Dim sqlsearch As String = "SELECT TOP 1 customerID FROM tblCustomer WHERE postcode = '" & postcode & "' ORDER BY customer DESC"

I think this is the mistake that I didn't realise previously.

 

[lingsn]

Well, this is the suggested code

 

[Morn]

Ok that runs, but the value that is passes is '0' which leads me to believe that it is either not returning the correct result from the query or the variable is being passed therefore it is saying I have been passed 0.

 

[lingsn]

Had you try out my suggested code?

 

[Morn]

No I hadn't, I have inserted the necessary bits the only thing that does not work is this line from the first form.

Dim bookingfrmPt1 As Form2 = New Form2(custID)
        bookingfrmPt1.Show()

bookingfrmPt1 is the name of the form I want the variable to go to

as form2 I guess I replace with bookingfrmPt1 as that is the form that is declared in my solution


new form2 i also guess needs to be replaced with bookingfrmPt1 as again its a declared form.

However if I do this it throws errors, and if i leave it it says that form 2 is not declared.

 

[lingsn]

From the code you show me I think you are getting closer to the solution. What is the error appear after you add the form2?