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

Question

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?

That snippet doesnt really do anything except check if there are rows. The data you seem to want will be in the datatable, but you threw that away after the count. You could pull that data out or use databinding — Ňɏssa Pøngjǣrdenlarp, Oct 26 '16 at 17:35
I added a line after the Count If statement. Is it something along these lines? the thing is I have to make sure that field is not empty (NULL) before setting the data to a control. Not all of them are required on saving so I need to be able to check that there is a value — BobSki, Oct 26 '16 at 17:37

score 1 · Accepted Answer · edited May 23 '17 at 10:34

1

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.

edited May 23 '17 at 10:34

Community

1
1

answered Oct 26 '16 at 17:50

Ňɏssa Pøngjǣrdenlarp

38,411
12
59
178

where am i disposing of it though? – BobSki Oct 26 '16 at 17:51
1

`Using dtA As New DataTable` the `End Using` disposes of the target object...there are several other things you might want to consider (typing) – Ňɏssa Pøngjǣrdenlarp Oct 26 '16 at 17:52
I dont persist on using the DataTable at the time that is the only way of me being able to accomplish things. I like the idea of DefaultView since I am actually loading a dataGrid with clients, and then upon double clicking it takes user to another form and display all the info (for editing) in each control. However on Update or Delete, I need to make sure that its updated in sql server otherwise there's no reason for me to use it in the way you described. – BobSki Oct 26 '16 at 18:16
cboDept.selecteditem=dta.rows(0).item("Dept") ? this doesn't seem to work :/ – BobSki Oct 26 '16 at 18:17
I dont know how you have the cbo set up. DataSource? Items? – Ňɏssa Pøngjǣrdenlarp Oct 26 '16 at 18:21
I load it from sql server with displayMember displayValue – BobSki Oct 26 '16 at 18:22
I guess that means you are using a DataSource (I vaguely recall a previous question, but...) – Ňɏssa Pøngjǣrdenlarp Oct 26 '16 at 18:23
This did it (sorry i accidentally clicked ont he chat......cboDept.SelectedValue = dt.Rows(0).Item("Dept") – BobSki Oct 26 '16 at 18:50

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

1 Answers1