Populate combo box based on choice of another combo box

Shodin

Hi all,

This is probably an easy question for you all, but being the neophyte that I am, I'm having some problems getting this to work.

The end result of what I'm trying to do is populate a 2nd combo box based upon the choice of the 1st combo box. Both combo boxes grab data from two different tables.

So here's where I'm at.

When the form loads, I've populated the first combo box with the data I want from the correct table in the DB. Where I'm getting stuck is populating the 2nd combo box based upon the choice of the 1st combo box.

Here it is in a little more detail. I've got a Customer and a Show table. The Customer data is loaded into the 1st combo box when the form loads from a view I've created in SQLE 2k5. This is, of course, the data source. The display member and value member is the company name and ID, respectively.

Now, I've written a stored procedure to get the Show info based upon the ID of the Company. So my problem here is actually two-fold I suppose.

1) How do I go about hooking all the adapters and binding sources for these tables and combo boxes to 'talk' to each other
2) How do I pass the Company ID parameter into those bindings so the stored procedure can execute properly in that 2nd combo box

stlarmon

Private Sub ComboBox1_SelectedIndexChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles ComboBox1.SelectedIndexChanged

Dim da As New SqlDataAdapter("usp_Show", myConn) 'Creates Data Adapter based on stored procedure

da.SelectCommand.CommandType = CommandType.StoredProcedure

da.SelectCommand.Parameters.AddWithValue("@CustomerID", ComboBox1.SelectedValue) 'Sets the Parameter

Dim ds As New DataSet

da.Fill(ds) 'Fills a dataset with results.

ComboBox2.DataSource = ds.Tables(0)

ComboBox2.DisplayMember = ds.Tables(0).Columns("ShowName").ColumnName.ToString

ComboBox2.ValueMember = ds.Tables(0).Columns("ShowID").ColumnName.ToString

End Sub

The code above is fired when combobox1 Selectedindex is changed. The Dataadapter is created to execute the stored procedure. The Parameter field is populated with the result on the ComboBox1 Selected Value (Customer ID) The data Adapter then populates the results into a dataset. That dataset is used as the data source for the 2nd Combobox, and the display and valuemembers for it are set.

Hope this helps!!!

Shodin

I think I'm almost there, but I'm not sure if I did the SqlDataAdapter part correctly as I'm getting an error. Here's what I've got. My additions are in green, and the place where I'm getting the error is in red.

Private Sub cmbCustomer_SelectedIndexChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles cmbCustomer.SelectedIndexChanged

Dim ThisTestDBConnectionString As New SqlClient.SqlConnection

Dim da As New SqlClient.SqlDataAdapter("sp_GetShowInfo", ThisTestDBConnectionString ) 'Creates Data Adapter based on stored procedure

da.SelectCommand.CommandType = CommandType.StoredProcedure

da.SelectCommand.Parameters.AddWithValue("@CustID", cmbCustomer.SelectedValue) 'Sets the Parameter

Dim ds As New DataSet

da.Fill(ds) 'Fills a dataset with results.

The error I'm getting is:
System.InvalidOperationException was unhandled
Message="The ConnectionString property has not been initialized."

Not sure what I'm supposed to do here to get this working tho.

cmbShow.DataSource = ds.Tables(0)

cmbShow.DisplayMember = ds.Tables(0).Columns("ShowName").ColumnName.ToString

cmbShow.ValueMember = ds.Tables(0).Columns("ShowID").ColumnName.ToString
End Sub

stlarmon

Your missing the connectionString from your SQLConnection

Change:

Dim ThisTestDBConnectionString As New SqlClient.SqlConnection

to

Dim ThisTestDBConnectionString As New SqlClient.SqlConnection("Data Source=Computer;Initial Catalog=Database;Integrated Security=SSPI;")

Of course you have replace "Computer" with the name of the computer thats acting as the sql server

and change the "Database" to the Name of your SQL Database

Shodin

Dang, now I'm getting a "Cannot open database "ThisTestDB" requested by the login. The login failed. Login failed for user 'MyComp\Home'."

It's using Windows Authentication, so I added:
user='<my username>';password='<my password>';
to that connection and it still didn't work.

BTW, Is there a way to do all of that automatically When I did the first combo box, I just clicked the ComboBox Tasks, checked the 'Use data bound items' box and then selected the appropriate data source, display member and value member.

Shodin

Ah HA! I think I figured this out.

What I did was assign the SP as the data source, and the relevant fields in the Data Binding Mode area of the Combo Tasks window. This automatically set up my table adapter and binding source.

Then I just copied the statement that was generated when I assigned the values to the 1st combo box and changed it as necessary. I eventually finagled the code based upon what VBE 2k5 gave me as an option to get it to at least come up.

Now I just need to play w/it a little to make sure that it's working like I hope it's working.

Here's the only code I actually needed:

Me.Sp_GetShowTableAdapter.Fill(Me._MyTestDBDataSet.sp_GetShow, cmbCustomer.SelectedIndex)