I wish to go to the next row of SQL Data depending on Combobox selection

Question

Basically, if the Combo box selection (Field) is "Engineering", then only a row which corresponds to that selection will display.I have achieved this with the code below. I want to access the next row of Data which similar and only corresponds to this (Field) selection and fill the Text box Controls appropriately. All data saved under that combo box selection in that row is displayed. This works fine. The Module of Code below this under Button1_Click is my attempt at accessing the next row, however incorrect data is displayed. Is there anyway of fixing and display the data required which is - the next whole row of SQL data where the combo box selection meets criteria in the database.


Private Sub BtnSearch_Click(sender As Object, e As EventArgs) Handles BtnSearch.Click
    Dim Command As New SqlCommand("Select * From Table_Upload where Field = @Field", Connection)
    Dim DA As New SqlDataAdapter(Command)
    Dim DT As New DataTable()
    Dim DR As SqlDataReader

    Command.Parameters.Add("@Field", SqlDbType.VarChar).Value = CmbField.SelectedItem
    DA.Fill(DT)
    CmbField.DataSource = DT
    CmbField.DisplayMember = "Field"
    Connection.Open()
    DR = Command.ExecuteReader()

    If DR.Read() Then
        TxtTitle.Text = DR(0)
        TxtComp.Text = DR(1)
        TxtDate.Text = DR(2)
        TxtSalary.Text = DR(3)
        CmbEmp.SelectedItem = DR(4)
        TxtDesc.Text = DR(5)
        TxtExp.Text = DR(6)
        TxtPhone.Text = DR(7)
        TxtEmail.Text = DR(8)
        TxtAddr.Text = DR(9)
    End If

    Connection.Close()
End Sub

CODE TO GO NEXT : Private Sub Button1_Click(sender As Object, e As EventArgs) Handles BtnNext.Click Dim Command As New SqlCommand("Select * From Table_Upload where Field = @Field", Connection) Dim DA As New SqlDataAdapter(Command) Dim DS As New DataSet() Dim DR As SqlDataReader

Command.Parameters.Add("@Field", SqlDbType.VarChar).Value = CmbField.SelectedItem

DA.Fill(DS, "Table_Upload")

CmbField.DisplayMember = "Field"

Connection.Open()
DR = Command.ExecuteReader()
If DR.Read() Then
    TxtTitle.Text = DR(+1).ToString()
    TxtComp.Text = DR(+1).ToString()
    TxtDate.Text = DR(+1).ToString()
    TxtSalary.Text = DR(+1).ToString()
    CmbEmp.SelectedItem = DR(+1).ToString()
    TxtDesc.Text = DR(+1).ToString()
    TxtExp.Text = DR(+1).ToString()
    TxtPhone.Text = DR(+1).ToString()
    TxtEmail.Text = DR(+1).ToString()
    TxtAddr.Text = DR(+1).ToString()
End If
Connection.Close()

End Sub

First you need to use Using ... End Using Method. Your Code line Command.Parameters.Add("@Field", SqlDbType.VarChar).Value = CmbField.SelectedItem, should return an Error, because CmbField.SelectedItem is an Object — evry1falls
@evry1falls No, SelectItem is just fine. By coincidence, the type of SqlParameter.Value is also Object. — Joel Coehoorn

jmcilhinney jmcilhinney · Accepted Answer · 2020-06-07T14:59:13

Once you have your data reader, create a DataTable and call its Load method to load all the data. You can then bind the DataTable to a BindingSource and the BindingSource to your TextBoxes. You can then call MoveNext to advance through the data.

myDataTable.Load(myDataReader)
myBindingSource.DataSource = myDataTable
myTextBox.DataBindings.Add("Text", myBindingSource, "MyColumn")

EDIT:

After doing some reading and testing based on Joel Coehoorn's comment below, I have learned that, while you cannot bind a data reader directly to controls in Windows Forms, you can bind one to a BindingSource and it will generate an IBindingList(Of DataRecordInternal), which is an IList and thus satisfies the requirements for WinForms binding. A DataRecordInternal can be indexed by column name or ordinal in the same way a DataRow or DataRowView can, so they can be treated in much the same way for the purposes of binding, e.g.

Using connection As New SqlConnection("connection string here"),
      command As New SqlCommand("SELECT * FROM MyTable", connection)
    connection.Open()

    Using reader = command.ExecuteReader()
        BindingSource1.DataSource = reader
        ComboBox1.DisplayMember = "Name"
        ComboBox1.ValueMember = "Id"
        ComboBox1.DataSource = BindingSource1
        TextBox1.DataBindings.Add("Text", BindingSource1, "Description")
    End Using
End Using

Either way

I wish to go to the next row of SQL Data depending on Combobox selection

2 Answers