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, Jun 07 '20 at 14:04
@evry1falls No, `SelectItem` is just fine. By coincidence, the type of `SqlParameter.Value` is also `Object`. — Joel Coehoorn, Jun 07 '20 at 15:03
@Bhavdeep Singh, try this [solution](https://stackoverflow.com/a/9600313/13393566) — evry1falls, Jun 07 '20 at 16:23

jmcilhinney · Answer 1 · 2020-06-08T03:59:14.490

1

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

edited Jun 08 '20 at 03:59

answered Jun 07 '20 at 14:59

jmcilhinney

50,448
5
26
46

1

You can bind a datareader directly, without needed to waste the extra memory for a DataTable. – Joel Coehoorn Jun 07 '20 at 19:04
@JoelCoehoorn Yes, that seems to be so. But doesn't it have to fill something? An IList, IBindingList? Something? A reader can't go backwards. – Mary Jun 08 '20 at 03:30
@JoelCoehoorn, generally speaking, complex data-binding in WinForms requires an `IList` or an `IListSource` but I didn't realise that that did not extend to the `BindingSource` class. I just did some reading and it turns out that the `BindingSource` will create its own `IBindingList` if the bound object is not already an `IList` or `IListSource`. I will update my answer accordingly. – jmcilhinney Jun 08 '20 at 03:48
@JoelCoehoorn, that said, I wonder whether the `IBindingList(Of DataRecordInternal)` saves that much over a `DataTable`. Probably some but not a whole lot because it's still a data structure that must store all the data. I guess it's probably akin to the `DataRowCollection` in the `Rows` property, although each item also doesn't have to store two copies of the data, so it probably is about half the size due to that. – jmcilhinney Jun 08 '20 at 03:58

Joel Coehoorn · Answer 2 · 2020-06-07T16:25:17.470

0

"I want to access the next row of Data"

Do this by calling DR.Read() again.

edited Jun 07 '20 at 16:25

answered Jun 07 '20 at 15:05

Joel Coehoorn

399,467
113
570
794

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

2 Answers2