Need to retrieve additional values based on Name key in table from DB within VB.NET

So initially I retrieved all values with the name identifier within a table I have, and placed them into a combo box. The data that was loaded into the table is:

EstablishConnection("SELECT Name FROM Publishers")

I used the Query builder within the table adapter configuration wizard to create a string inquiry

SELECT        PubID, [Company Name], Address, City, State, Zip, Telephone, Fax
FROM          Publishers
WHERE         Name=''

And in VB implemented it with the selected value of the combobox

EstablishConnection(String.Format("SELECT PubID, [Company Name], Address, City, State, Zip, Telephone, Fax FROM Publishers WHERE Name='{0}'", oleDbCmbNames.SelectedItem.ToString()))

But, it only returns one value, and not all and says that the DB is missing based on the exception that I gave it.

Public Class Form1
    Private Sub PublishersBindingNavigatorSaveItem_Click(sender As Object, e As EventArgs)
        Me.Validate()
        Me.PublishersBindingSource.EndEdit()
        Me.TableAdapterManager.UpdateAll(Me.ContactsDataSet)

    End Sub


    Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
        'TODO: This line of code loads data into the 'ContactsDataSet.Publishers' table. You can move, or remove it, as needed.
        Me.PublishersTableAdapter.Fill(Me.ContactsDataSet.Publishers)

        'TODO: This line of code loads data into the 'ContactsDataSet.Publishers' table. You can move, or remove it, as needed.
        Me.PublishersTableAdapter.Fill(Me.ContactsDataSet.Publishers)

        'Establish a connection to the DB and fill combobox with names
        EstablishConnection("SELECT Name FROM Publishers")

    End Sub

    ' Instantiate Connection
    Dim connection As OleDb.OleDbConnection

    ' This function prepares, establishs, inquires, and closes the connection to the DB
    Public Sub EstablishConnection(request As String)

        Try
            ' Initialize new connection
            connection = New OleDb.OleDbConnection

            ' Initialize SQL inquiry
            Dim command As New OleDb.OleDbCommand()

            ' Assign Inquiry request to command
            command = New OleDb.OleDbCommand(request, connection)

            ' Assign db source before establishing a connection
            connection.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=Contacts.mdb"

            ' Open connection
            connection.Open()

            ' Initialize DB reader with inquiry
            Dim oleDBReader As OleDb.OleDbDataReader = command.ExecuteReader

            If oleDbCmbNames.Items.Count = 0 Then
                ' Read db, return inquries
                While oleDBReader.Read
                    ' Add each inquiry name string to combobox
                    oleDbCmbNames.Items.Add(oleDBReader("Name").ToString)
                End While
            ElseIf oleDbCmbNames.Items.Count > 0 Then
                ' Read db, return inquries
                While oleDBReader.Read()
                    PubIDLabelTextResult.Text = oleDBReader.Item("PubID").ToString
                    Company_NameLabelTextResult.Text = oleDBReader.Item("[Company Name]").ToString
                    AddressLabelTextResult.Text = oleDBReader.Item("Address").ToString
                    CityLabelTextResult.Text = oleDBReader.Item("City").ToString
                    StateLabelTextResult.Text = oleDBReader.Item("State").ToString
                    ZipLabelTextResult.Text = oleDBReader.Item("Zip").ToString
                    TelephoneLabelTextResult.Text = oleDBReader.Item("Telephone").ToString
                    FaxLabelTextResult.Text = oleDBReader.Item("Fax").ToString
                End While
            End If

        Catch ex As Exception
            ' Return if db not found or cannot connect.
            MessageBox.Show("Could not connect to DB. Ensure DB is not missing.")
        Finally
            ' Close connection
            connection.Close()
        End Try



    End Sub

    Private Sub oleDbCmbNames_SelectedIndexChanged(sender As Object, e As EventArgs) Handles oleDbCmbNames.SelectedIndexChanged
        EstablishConnection(String.Format("SELECT PubID, [Company Name], Address, City, State, Zip, Telephone, Fax FROM Publishers WHERE Name='{0}'", oleDbCmbNames.SelectedItem.ToString().Trim))
    End Sub
End Class

Solution

So, for the question I asked in the comments I’m making the assumption that you want to use the combo box as a navigation device.. you change the combo and a bunch of other controls on the form alter their contents. I’m assuming this from what it looks like the code is trying to do. If it’s not what you’re trying to do let me know and I’ll make a revised answer

So you’ve got a dataset and a tableadapter that loads all your publishers. Twice, actually - you can remove one of those identical fill commands from the form Load event handler. No point having two calls to the same thing right after each other

Next, you need to throw all that code in EstablishConnection away, and every call to it. Using data adapters and tableadapters together, particularly when you’re just starting out, will lead to confusion. Tableadapters are great, they generate safe sql that can have parameters in and are easy to use. What you have here in your EstablishConnection sub is a very unsafe way of running SQL.. For why, take a read of http://bobby-tables.com then come back and delete that code so you just have this:

Public Class Form1
    Private Sub PublishersBindingNavigatorSaveItem_Click(sender As Object, e As EventArgs)
        Me.Validate()
        Me.PublishersBindingSource.EndEdit()
        Me.TableAdapterManager.UpdateAll(Me.ContactsDataSet)
    End Sub


    Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
        Me.PublishersTableAdapter.Fill(Me.ContactsDataSet.Publishers)
    End Sub
End Class

Yep, that all the code your form code behind needs to contain at the moment

Now, go to your form designer and delete all the combos, text boxes, labels etc that relate to a publisher. I know it sounds like I’m trying to get you to trash your project but this is so I can get to a consistent state where we’re on the same page and I know what’s on your form and how it is wired up.

After you cleared your form (you can leave the ContactsDataSet, PublisherTableAdapter and other things down the bottom, plus any controls not related to publishers) open the View menu, go to Other Windows and pick Data Sources. It’ll show a tool panel like this (and this is a great pic because it demos some other points too):

(credit)

Because, from the code above, it seems to me that you want to load your publisher details into labels instead of text boxes you can expand your contacts dataset, expand the publishers node and change the type of the nodes under it (the address, zip, phone, fax etc) from text box to label. After you changed them to label drag and drop them one by one onto the form. Alternatively, change the header node from DatagridView to Details and then drag the header node onto the form to quickly create a label for each of everything under the header node

You now have a bunch of labels that are data bound (connected) to your contacts dataset publishers table. You can click any label and look in the (Data Bindings) line of the properties grid and see their Text property is connected to a column of the publishers table, and their data source is something called a publishersBindingSource. The binding source maintains the concept of “current record” in a list of multiple records. You have 100 publishers, you set the bindingsource’s current record to the 50th, all the labels show the data for the 50th publisher. You tell the bindingsource to move to the 51st publisher and all the labels change their content automatically. You can see this in action by running your project, and clicking the arrows in the bindingnavigator at the top of the form. The call to Fill in the form load loaded 100 publishers (say), the bindingsource starts pointing to the first one, clicking next/prev/last or typing a number in will change the current record of the bindingsource that sits between the list of 100 publishers in the dataset, and your controls (which can only show one publisher at once)

Now, we add a combo to the form to act as a navigator:

add a combo to your form
in the properties window set its data source to be the PublishersBindingSource - this is vital
type the word Name into the DisplayMember setting - necessary to get the combo to show the right thing
set the DropDownStyle to be DropDownList - optional but an editable combo is really confusing in this context

Run your project

When configured like this, a combo will show the list of items it finds in the bindingsource, and the currently selected one will drive the bindingsource’s current record. Changing the selected item in the combo will cause all the labels to change because the combo influenced the bindingsource’s current record. The combo doesn’t need anything else to act as a navigator, and indeed trying to bind something else will cause it to start acting like an editor of the data in the current record, not a navigator of all records