How to populate a ComboBox based on another ComboBox through SQL Server?

Question

I want to be able to have two ComboBoxes where one is the parent or owner of the second one, through an connection string on SQL Server. This means that whenever I select a value in the first ComboBox, the second ComboBox will filter it's results to display the corresponding values related to the first ComboBox. If you see my code below, I get an error that states:

Error 1 Method 'Private Sub cboClient_SelectedIndexChanged(sender As Object, e As System.EventArgs, EnvName As String)' cannot handle event 'Public Event SelectedIndexChanged(sender As Object, e As System.EventArgs)' because they do not have a compatible signature.

Goal:

Change the database connection
Query the proper user data from the correct database
populate the user combo box with that user data

My code:

 Private Sub cboClient_SelectedIndexChanged(sender As Object, e As EventArgs, ByVal EnvName As String) Handles cboClient.SelectedIndexChanged
        Using con2 As New SqlConnection(ConfigurationManager.ConnectionStrings("conStr").ConnectionString)
            'Getting connection string from the specific client
            Dim cmd2 As New SqlCommand("Select * from tblCONNECTIONS where EnvName = '" & "@EnvName" & "'", con2)
            con2.Open()
            Dim dt As New DataTable
            dt.Load(cmd2.ExecuteReader())
            cmd2.Parameters.Add("@EnvName", SqlDbType.VarChar)
            cmd2.Parameters("@EnvName").Value = EnvName
            If dt.Rows.Count > 0 Then
                Dim clientConString As String = dt(0)("ConnectionString")
                'Getting the yalusers info from client specific database\
                Using con3 As New SqlConnection(clientConString)
                    cmd2 = New SqlCommand("Select * from yalusers", con3)
                    con3.Open()
                    dt.Load(cmd2.ExecuteReader())
                    cboUser.DataSource = dt
                    cboUser.DisplayMember = "First_Name"
                    cboUser.ValueMember = "ID"
                    con3.Close()
                End Using

            Else
                'Show error message : missing connection string in the YALCONNECTIONS table  
            End If

            con2.Close()
        End Using
    End Sub

How can this be fixed?

Update: When I run the app, I have 6 items in the first combobox and which ever one I select, it doesn't populate into the second combobox. Why??

You should get in the habit of naming your columns explicitly instead of select * everywhere. +100 though for parameterizing your queries!!! — Sean Lange, Jun 18 '15 at 19:17

score 2 · Answer 1 · answered Jun 18 '15 at 19:14

2

You cannot add a parameter to an event handler. It must have this definition:

Private Sub cboClient_SelectedIndexChanged(sender As Object, e As EventArgs) Handles cboClient.SelectedIndexChanged
    DoSelectedIndexChanged(sender, e, something)
End Sub

Create a separate subroutine that does the work you need but do not let it handle the event.

Private Sub DoSelectedIndexChanged(sender As Object, e As EventArgs, ByVal EnvName As String) Handles cboClient.SelectedIndexChanged

answered Jun 18 '15 at 19:14

Marc Johnston

1,276
1
7
16

For that 'something' part you put there, I want to use this: EnvName. I'm getting from a table called tblconnection and it has a URL like this for the value under the connectionstring column: Data Source=111.144.252.84;Initial Catalog=ECRNA;Integrated Security=False;User ID=username;Password=password – Dave Jun 18 '15 at 19:19
This is what I'm trying to accomplish: http://stackoverflow.com/questions/23390646/how-to-set-a-value-to-a-tableadapter-parameter – Dave Jun 18 '15 at 19:23

How to populate a ComboBox based on another ComboBox through SQL Server?

1 Answers1