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
– Ňɏssa Pøngjǣrdenlarp, Commented Oct 26, 2016 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
– BobSki, Commented Oct 26, 2016 at 17:37

Community · Accepted Answer · 2017-05-23 10:34:16Z

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, 2017 at 10:34

CommunityBot

11 silver badge

answered Oct 26, 2016 at 17:50

Ňɏssa Pøngjǣrdenlarp

39k12 gold badges66 silver badges193 bronze badges

Sign up to request clarification or add additional context in comments.

8 Comments

BobSki Over a year ago

where am i disposing of it though?

Ňɏssa Pøngjǣrdenlarp Over a year ago

Using dtA As New DataTable the End Using disposes of the target object...there are several other things you might want to consider (typing)

BobSki Over a year ago

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 Over a year ago

cboDept.selecteditem=dta.rows(0).item("Dept") ? this doesn't seem to work :/

Ňɏssa Pøngjǣrdenlarp Over a year ago

I dont know how you have the cbo set up. DataSource? Items?

|

Collectives™ on Stack Overflow

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

1 Answer 1

8 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

8 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related