Bobski Bobski - 1 month ago 13
Vb.net Question

Filling controls (comboboxes, textbox) with data selected from SQL Server

I'm working on trying to populate a bunch of controls with data. These controls include Comboboxes, textboxes, date/time etc.

I initially let the user make their selections/enter data and save the records. For comboboxes, i displayed text, however, save a value, as an example...

ValueMember DisplayMember
100 Accounting
101 Finance


In this case, if user selected Accounting, I saved it as 100.

Now I'm trying to populate these controls with the correct data and set the selections to what they were at the point of saving this record.

This is how I'm getting the data...

dim querystring as string
dim count as integer
QueryString = "Select FirstName, LastName, Dept, Position from TblClients where IdClient = 1112"

Dim cmd As New SqlCommand(queryString, Conn)
Conn.Open()
Using sdA As New SqlDataAdapter
Using dtA As New DataTable
sdA.SelectCommand = cmd
count = sdA.Fill(dtA)
If count <> 0 Then
MsgBox("Success")
End If

cboContactCategory.SelectedValue = dtA.Rows(0)("Dept").ToString

End Using
End Using
Conn.Close()


FirstName = txtFirst; LastName = txtLast, Position = cboPosition, Dept = cboDept

How would I go about setting these values?

Answer

Given the query, you are loading several fields into the DataTable, then not retrieving the data before the DataTable is disposed:

txtFirst.Text = dtA.Rows(0).Item("FirstName")
txtLast.Text = dtA.Rows(0).Item("LastName")

If you hold onto the DataTable you might want to use data binding:

txtFirst.DataBindings.Add("Text", dtClients, "FirstName")
txtLast.DataBindings.Add("Text", dtClients, "LastName")

The values from the current row will display in those controls.

If you persist the DataTable and load all the clients into it, you don't have to run a query to find someone:

dtClients.DefaultView.RowFilter = String.Format("ID = {0}", intIdToFind)
' text filter:
dtClients.DefaultView.RowFilter = String.Format("Country = '{0}'", "Belgium")

This filters the view to that one client, or all those in Belgium as needed. No need to write more SQL or query the DB. Of course if there are hundreds of thousands of Clients, you might want to load a subset at a time - those in Belgium, those who have ordered within the last 60 days or whatever.

A fully configured DataAdapter can also perform all Updates, Deletes for you. In some cases you may only need one SQL statement per table. See Searching values via a datagridview for an example.