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
Advertisement
Answer
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