asked on

Populate textboxes based on selected 2nd combobox from selected 1st combobox Using VB.net and SQL Server table

Populate textboxes based on selected 2nd combobox that was also based from the selected 1st combobox.

This code is working well but I cannot select the 2nd combobox dropdown:

Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load



        FillComboBox_Number()


       
    End Sub

Private Sub FillComboBox_Number()

Dim con As New SqlConnection(DataSQLConnString)
Dim cmd As New SqlCommand()

cmd.Connection = con
cmd.CommandType = CommandType.Text
cmd.CommandText = "Select DISTINCT [Number] ,[UniqueID] FROM [SQLTable]"
 Dim objDs As New DataSet()
 Dim dAdapter As New SqlDataAdapter()
 dAdapter.SelectCommand = cmd
 con.Open()
 dAdapter.Fill(objDs)
 con.Close()
 ComboBox_Number.ValueMember = "Number"
 ComboBox_Number.DisplayMember = "Number"

 ComboBox_Number.DataSource = objDs.Tables(0)

End Sub

Private Sub ComboBox_Number_SelectedIndexChanged(sender As Object, e As EventArgs) Handles ComboBox_Number.SelectedIndexChanged
If ComboBox_Number.SelectedValue.ToString() <> "" Then
            Dim Number As Integer = Convert.ToInt32(ComboBox_Number.SelectedValue.ToString())
            FillPostDates(Number)
            ComboBox_PostDate.SelectedIndex = 0

        End If
End Sub

Private Sub FillPostDates(Number As Integer)
        Dim con As New SqlConnection(DataSQLConnString)
        Dim cmd As New SqlCommand()
        cmd.Connection = con
        cmd.CommandType = CommandType.Text
        cmd.CommandText = "SELECT Number, PostDate FROM [SQLTable] WHERE Number=@Number"
        cmd.Parameters.AddWithValue("@Number ", Number)
        Dim objDs As New DataSet()
        Dim dAdapter As New SqlDataAdapter()
        dAdapter.SelectCommand = cmd
        con.Open()
        dAdapter.Fill(objDs)
        con.Close()
        If objDs.Tables(0).Rows.Count > 0 Then
            ComboBox_PostDate.ValueMember = "Number"
            ComboBox_PostDate.DisplayMember = "PostDate"
            ComboBox_PostDate.DataSource = objDs.Tables(0)
           

        End If


    End Sub

Private Sub ComboBox_PostDate_SelectedIndexChanged(sender As Object, e As EventArgs) Handles ComboBox_PostDate.SelectedIndexChanged
Dim Number As Integer = Convert.ToInt32(ComboBox_PostDate.SelectedValue.ToString())
        FillPostDates(Number)


End Sub

Open in new window

Also, I added this to the code in ComboBox_PostDate_SelectedIndexChanged but it is not working:

Dim cn = New SqlConnection(DataSQLConnString)
        Dim cmd As New SqlClient.SqlCommand
        Dim tbl As New DataTable
        Dim da As New SqlClient.SqlDataAdapter
        Dim reader As SqlClient.SqlDataReader
        Try




            cn.Open()
            Dim sql As String
            sql = "Select [BatchKey],[Amount]  FROM [SQLTable] WHERE PostDate ='" + ComboBox_ PostDate.Text + "'"



            cmd = New SqlClient.SqlCommand(sql, cn)
            reader = cmd.ExecuteReader
            While reader.Read

                TextBox_BatchKey.Text = (reader.Item("BatchKey"))
               TextBox_Name.Text = (reader.Item("Amount"))
                

            End While

            cn.Close()
        Catch ex As Exception
            MessageBox.Show(ex.Message)
        End Try

Open in new window

I appreciate your help.

Thank you.
Example.xlsx

Éric Moreau

do you have an error? there is a space on line 13 between the _ and the P

Queennie L

ASKER

No I don't have an error.

The first set of code above is working except the 2nd combo box is not selecting the postdate. The second set of code is when the 2nd combo box if selected it will populate 2 text boxes.

Any help will be appreciated.

Ryan Chong

No I don't have an error.

can you rebuild the project without error?

ASKER CERTIFIED SOLUTION

Ryan Chong

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Queennie L

ASKER

@Ryan Chong:

I will test it today and will let you know.

Thank you.

Queennie L

ASKER

@Ryan Chong:

I tested it and it is working.

The only thing I add is to the "ComboBox_PostDate_SelectedIndexChanged" WHERE clause including Number = ='" + ComboBox_Number + "'.

This way it only filters selected "Number" and it's respected "PostDate" and once "PostDate" is selected it populates its textboxes.

Thank you again for your help.

Queennie L

ASKER

I appreciate your help.

Ryan Chong

The only thing I add is to the "ComboBox_PostDate_SelectedIndexChanged" WHERE clause including Number = ='" + ComboBox_Number + "'.

This way it only filters selected "Number" and it's respected "PostDate" and once "PostDate" is selected it populates its textboxes.

what do you mean by that?

if this is not relevant to the initial question, you may create another question for discussion since this question was already closed.