Listbox problem, information to textboxes...

I'm selecting a customer from a listbox of surnames...

I need to address to display, and the date arrived back...

Can't get anything to go into the textboxes, if dr.hasrows is showing up as none I think... not sure how to go about remedey"ing" it though...

Listbox Code:

Private Sub ListBox1_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ListBox1.SelectedIndexChanged
        Dim ConnectionString As String
        Dim SQLString As String
        Dim cmd As System.Data.OleDb.OleDbCommand
        Dim conn As System.Data.OleDb.OleDbConnection
        Dim dr As System.Data.OleDb.OleDbDataReader
        ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data "
        ConnectionString += "Source=" & "Opticians.accdb "
        conn = New System.Data.OleDb.OleDbConnection(ConnectionString)
        AddressTextBox.Text = ListBox1.Text 'Gives the AddressTextBox the value that we click in the ListBox 
        SQLString = "SELECT * FROM CustomerTable " ' Selects all from CustomerTable
        SQLString += "INNER JOIN SpectacleSalesTable ON CustomerTable.CustomerID = SpectacleSalesTable.CustomerID " 'Bring EyeTestTable in, using the records where the CustomerID matches up in both tables
        SQLString += "Where '" & AddressTextBox.Text & "'" 'If the value that we clicked in the listbox, matches the surname it will try to connect
        SQLString += "= Surname"
        Try
            conn.Open()
            If ConnectionState.Open.ToString = "Open" Then
                cmd = New System.Data.OleDb.OleDbCommand(SQLString, conn)
                dr = cmd.ExecuteReader()
                If dr.HasRows Then
                    dr.Read()
                    If Not IsDBNull(dr.Item("SpecSalesTable.FrameID")) Then
                        DepositTextBox.Text = dr.Item("SpecSalesTable.FrameID").ToString 'Adds the CustomerID to the CustomerIDTextBox
                    End If
                    If Not IsDBNull(dr.Item("SpecSalesTable.DateReceived")) Then
                        DateReceivedTextBox.Text = dr.Item("SpecSalesTable.DateReceived").ToString 'Adds the EyeTestID to the EyeTestIDTextBox
                    End If
                    If Not IsDBNull(dr.Item("CustomerTable.Street")) Then
                        AddressTextBox.Text = dr.Item("CustomerTable.Street").ToString 'Adds the Street to the AddressTextBox
                    End If
                    If Not IsDBNull(dr.Item("CustomerTable.Town")) Then
                        Address2TextBox.Text = dr.Item("CustomerTable.Town").ToString 'Adds the Town to the Address2TextBox
                    End If
                    If Not IsDBNull(dr.Item("CustomerTable.County")) Then
                        Address3TextBox.Text = dr.Item("CustomerTable.County").ToString 'Adds the County to the Address3TextBox
                    End If
                End If
            End If
        Catch ex As Exception
        End Try
    End Sub

Quote:

---

Originally Posted by Kielo

Can't get anything to go into the textboxes, if dr.hasrows is showing up as none I think...

---

Well, thats your problem right there. If you have no rows then the code that fills in the textboxes doesn't execute. Your going to have to find out why the database isn't returning anything. Then worry about displaying the data.

A real quick glance at your WHERE statement... It looks incorrect to me. You have:

vb Code:

SQLString += "Where '" & AddressTextBox.Text & "'" 'If the value that we clicked in the listbox, matches the surname it will try to connect
SQLString += "= Surname"

Don't you want something like:

vb Code:

SQLString += "Where Surname = '" & AddressTextBox.Text & "'"

WOW... There is some serious unesessary work in this procdure. I will help as best I can and I'm sure others can add more.. Firstly, only connect to your DB once in the session, then close it as your session closes. By which I mean open it with your app, close it as your app closes. Place the follwing into a module with a function name and call the function when required. As it currently stands your are trying to perform a DB connection everytime the listbox item is selected (you will need to do a little work with the function).

Code:

---

        Dim ConnectionString As String
        Dim SQLString As String
        Dim cmd As System.Data.OleDb.OleDbCommand
        Dim conn As System.Data.OleDb.OleDbConnection
        Dim dr As System.Data.OleDb.OleDbDataReader
        ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data "
        ConnectionString += "Source=" & "Opticians.accdb "
        conn = New System.Data.OleDb.OleDbConnection(ConnectionString)
conn.open

---

You do not need this

Code:

---

AddressTextBox.Text = ListBox1.Text 'Gives the AddressTextBox the value that we click in the ListBox

---

You are just moving the value from one place to another, simply reference listbox1.text

Simplify your SQL query.. e.g. SQLString = "SELECT * from tableA WHERE Field1 = somedata AND fieldb = moredata"

Working on that stuff now, thanks for the comments

Hmm... still can't figure out where the original problem is, I have this working in a different setting, using different tables... but this one wont work :(

Open once on app start is VB6 (ADO) style recommenations. For ADO.Net the recommenation is open and close on use.

Okay... anyone spotted anything else? :(