Question Find row in dataset, fill in textboxs with data from row.

[iop693]

Hi, new to these foruns, sorry if this is in the wrong place

Okayy, basically i'm designing a program atm for my college coursework, and have been trying to get a search button to work. Basically, I need a way so that after the customer id has been found, my variable (in this case inc) needs to set itself to the row which the customer id that has been found is in. the idea is that it finds the row, and fills in the textboxes which i have with the data Here's the bit of code i have atm:

(And btw, inc is set as an integer earlier on in the program)

Dim CustomerID As Integer
'asks for the customer's ID number so that their results can be found.
CustomerID = Val(InputBox("what is the Customer number?"))

Try
    Sql = "Select * from tblQuotes WHERE CustomerID = " & CustomerID

    'dataAdapter obtains data from database
    da = New OleDbDataAdapter(Sql, con)

    da.Fill(ds, "CustomerID")
    'count number of rows(records) in the dataset
    'finds the row which the customer id is in
    inc =WHATEVER THE ROW NUMBER IS 
    If ds.Tables("CustomerID").Rows.Count > 0 Then
        TxtName.Text = ds.Tables("TblQuotes").Rows(inc).Item("CustomerName")
        TxtHouse.Text = ds.Tables("TblQuotes").Rows(inc).Item("HouseNumberName")
        TxtTown.Text = ds.Tables("TblQuotes").Rows(inc).Item("TownCity")

etc...

hopefully with the text WHATEVER THE ROW NUMBER IS being replaced by actual code to get it to that row.

Also, if this is not possible, can anyone suggest a better way of doing it?

Thanks in advance for any help

 

[Menthos]

Ok, so it appears you are trying to fill your textboxes with the details related to the entered CustomerID?

If that is the case and your CustomerID is unique in your table of details, then your SQL query should only return one row of data and therefore you won't need to know any row number to retrieve the details.

Unless I'm missing something, your code is using the SQL statement to add a datatable called CustomerID to your dataset - I'm assuming that datatable should be called TblQuotes based on the following statements where you fill your textboxes.... so with all that something roughly along the lines of should work:

da.Fill(ds, "TblQuotes")
If ds.Tables("TblQuotes").Rows.Count > 0 Then
   Dim dr as datarow = ds.Tables("TblQuotes").Rows(0)
   TxtName.Text = dr.Item("CustomerName")
   TxtHouse.Text = dr.Item("HouseNumberName")
   TxtTown.Text = dr.Item("TownCity")
End If

 

[jmcilhinney]

Don't use a data adapter when there's no binding and you won't be getting more than on row. You should use a data reader:

Dim customerID As Integer

'...

Using connection As New OleDbConnection("connection string here"),
      command As New OleDbCommand("SQL query here", connection)
    command.Parameters.AddWithValue("@CustomerID", customerID)
    connection.Open()

    Using reader As OleDbDataReader = command.ExecuteReader(CommandBehavior.SingleRow)
        If reader.HasRows Then
            Me.TextBox1.Text = reader.GetString(0)
            'etc.
        End If
    End Using
End Using

Also, Val returns a Double and you're assigning it to an Integer variable, so that's a crash waiting to happen.