Reading OleDb records into TextBox using ComboBox VB.NET

Question

I'm new to programming. What i'm trying to accomplish is to fill in 9 textboxes in VB.NET, reading access table TblKlanten, using a combobox (CbbNaamfirma). I cannot get this to work for the life of me; i've been searching for 6 hours for this simple thing. Can any of you help me out? I've read numerous threads on SO.com like this and they all just won't work for me. Code i have now:

Private Sub CbbNaamfirma_SelectedIndexChanged(sender As System.Object, e As           System.EventArgs) Handles CbbNaamfirma.SelectedIndexChanged
Dim Connection As New OleDb.OleDbConnection
    Connection.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source='" & Application.StartupPath & "\Database.accdb.'"
    Try
        Connection.Open()
        Dim query As String
        query = "SELECT Adres FROM TblKlanten WHERE [Naam firma] = ' " & CbbNaamfirma.Text & " ' "
        Dim cmd As New OleDbCommand(query, Connection)
        Dim Reader As OleDbDataReader = cmd.ExecuteReader
        Reader = cmd.ExecuteReader
        While Reader.Read
           TxtAdresprev.Text = Reader.GetString("Adres")
        End While
        Connection.Close()
    Catch ex As OleDbException
        MessageBox.Show(ex.Message)
    Finally
        Connection.Dispose()
    End Try
End Sub

Thank you in advance. Hope that code block turned out alright?

Answer 1

The first thing to change is the reading from the database using a parameterized query. Notice that your code cannot find anything because you add a space before and after the value of the combobox.

Then you need to start employing the using statement around the disposable objects to ensure a proper closing and disposing

Finally the GetString method from the OleDbDataReader wants a numeric index inside the returned list of fields, not the name of the field

Private Sub CbbNaamfirma_SelectedIndexChanged(sender As System.Object, e As System.EventArgs) Handles CbbNaamfirma.SelectedIndexChanged
    Dim cnString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & _
                    Application.StartupPath & "\Database.accdb"
    Dim query = "SELECT Adres FROM TblKlanten WHERE [Naam firma] = ?"
    Using Connection = New OleDb.OleDbConnection(cnString)
    Using cmd = New OleDbCommand(query, Connection)
        Try
           Connection.Open()
           cmd.Parameters.AddWithValue("@p1", CbbNaamfirma.Text) 
           Using Reader = cmd.ExecuteReader
               While Reader.Read
                  Dim posAdres = Reader.GetOrdinal("Adres")
                  TxtAdresprev.Text = Reader.GetString(posAdres)
                  .... other text boxes for other fields here.....
              End While
           End Using

       Catch ex As OleDbException
           MessageBox.Show(ex.Message)
       End Try
   End Using
   End Using
End Sub

Also your connection string seems to be wrong. No need of quotation and that stray point after the fielname is wrong