Sourcing ComboBox 2 from combobox 1

[ribbons]

Hi all,

I'm trying to fill combobox 2 with all items that match the selection in combobox 1. I followed a thread from here, coupled with my limited knowledge, but I got lost in this. any suggestions would be appreciated. I hope this is a simple fix:

Public Class Form1

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

        Dim sqlString As String
        Dim Connection As New OleDb.OleDbConnection
        Connection.ConnectionString = "Provider = Microsoft.Jet.OLEDB.4.0;" & _
                                "Data Source = I:\Siteware\SEED_SYS\SD-Data\seed.mdb;"

        sqlString = "SELECT * FROM LabNumber"  'get all records from the Labnumber table
        Dim cmd As New OleDb.OleDbCommand(sqlString, Connection)
        cmd.Parameters.Add("CompanyName", OleDb.OleDbType.LongVarChar).Value = ComboBox1.Text 'fill ComboBox1 with Company Names

        Dim ds As New DataSet  'new dataset
        Dim myrow As DataRowView
        Dim adap As New OleDb.OleDbDataAdapter(cmd)
        adap.Fill(ds, "OldLabNumber")  'fill new data adapter with all labnumbers matching those companynames

        If ds.Tables(0).Rows.Count > 0 Then
            Dim dv As New DataView
            dv.Table = ds.Tables(0)

            For Each myrow In dv
                ComboBox2.Items.Add(myrow("OldLabnumber").ToString) 'fill ComboBox 2 with all companyname matching the lab numbers
            Next
        End If

    End Sub
End Class

ribbons

 

[RiverGuy]

What undesirable behavior are you getting? I can see that with this code, you might want to clear the items from ComboBox2 before you fill each time.

 

[ribbons]

The comboboxes don't fill. I'm sure it's some simple oversight, but I have been unable to figure out if it is something careless on my part of if I have some functional part of the code wrong.

ribbons

 

[RiverGuy]

1. You don't need that DataView, you can just use the DataTable in this instance.

2. Have you tried stepping through the code to see if you are getting any rows in your For loop?

 

[ribbons]

I'm running this code behind a simple form with two comboboxes, ComboBox 1 and Combobox2. I want to see if when the form comes up, ComboBox 1 is filled with company names. If I select a name, ComboBox 2 is filled with lab numbers. Make sense?

When I run this code, the form comes up with the comboboxes, but there is no info in either of them.

I've been trying ti figure out how to fill comboboxes in this fashion for some time, but just can't seem to find a solution. Will this code work in this instance or am I completely off track?

Kathy

 

[ribbons]

I've now gotten the following to run, but the labnumbers generated are NOT governed by the choice in Combobox 1. Any ideas?

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

        Dim sqlString As String
        Dim Connection As New OleDb.OleDbConnection
        Connection.ConnectionString = "Provider = Microsoft.Jet.OLEDB.4.0;" & _
                                "Data Source = I:\Siteware\SEED_SYS\SD-Data\seed.mdb;"

        sqlString = "SELECT * FROM LabNumber"  'get all records from the Labnumber table
        Dim cmd As New OleDb.OleDbCommand(sqlString, Connection)
        cmd.Parameters.Add("CompanyName", OleDb.OleDbType.LongVarChar).Value = ComboBox1.Text 'fill ComboBox1 with Company Names

        Dim ds As New DataSet  'new dataset
        Dim myrow As DataRowView
        Dim adap As New OleDb.OleDbDataAdapter(cmd)
        adap.Fill(ds, "LabNumber")  'fill new data adapter with contents of labNumber table

        If ds.Tables(0).Rows.Count > 0 Then
            Dim dv As New DataView
            dv.Table = ds.Tables(0)

            For Each myrow In dv
                Combobox2.Items.Add(myrow("OldLabnumber").ToString) 'fill ComboBox 2 with all labnumber matching company names
            Next
        End If

    End Sub

 

[RiverGuy]

You are creating a parameterized query it looks like but your query text doesn't contain the parameter. Try changing

"SELECT * FROM LabNumber"

to

"SELECT * FROM LabNumber WHERE CompanyName = ?"

 

[ribbons]

I think I've thoroughly confused myself now.

I tried changing

"SELECT * FROM LabNumber"

to

sqlString = "SELECT * FROM LabNumber WHERE LabNumber.LotNumber = '" & ComboBox1.Text & "'"

But the second combobox is not filled. Shouldn't a parameterized query come somewhere later in the code for the contents of the first box to fill the contents of the second box?

 

[RiverGuy]

If you're going to concatenate the value to your query string, then you don't need the query parameter. Your code is in the correct place using this method--you are trying to fill a datatable and then add the row values to the combobox when the index of the first combobox changes. This isn't the preferred method to populate the combobox--either databinding, or using a datareader or a static datatable with a dataview instead would be more efficient, but your code should still work.

Again, have you debugged and stepped through the code to see if you are actually getting rows in your DataTable?

 

[ribbons]

No, there are no rows in the datatable. Is there a simpler way to do this? I first tried the datatable, but thought that direct code would be much neater and easier. Guess I was wrong.

This code above IS supposed to fill one combobox based on the results from another . . .Right??

 

[ribbons]

What does the "@" sign mean in code such as this?

 

[RiverGuy]

I don't see the "@" in the code?

 

[ck1999]

Ribbons,
Why are you comparing lotnumber = combobox1 if combobox 1 is a list of companies. This does not make sense.

Look again at riverguy code

i edited a little

"SELECT * FROM LabNumber WHERE CompanyName = '" & ComboBox1.Text & "'"

ck1999