3.2 Retrieve Results from SQL Server by Using the DataTable Object

The data reader is great when you just want to load data into a ListBox or ComboBox control manually, but you can save some coding by binding the ListBox control to a data table at runtime, as well as providing the ability to get not only the displayed value, but the key column as well. This How-To demonstrates how to bind a limited ListBox control to a data table.

Although getting the quick information is great, you need to be able to refer back to the table of information, and you don't want to have to open another connection to get there. You know that the DataTable object should allow you to perform this task. How do you get results from SQL Server by using the DataTable object?

Technique

Using the Windows Forms controls that were introduced in How-To 3.1, you will use a familiar object from Chapter 1 called the DataAdapter object. This time, instead of using the OleDbDataAdapter data control, you will use the OleDbDataAdapter class from the System.Data.OleDb Namespace.

Using a similar technique that was used when filling a DataSet, you will instantiate the data adapter by assigning the SQL string and connection object. Then, instead of filling a DataSet object, you will fill a DataTable object. Because you will only be dealing with a table's worth of data, you just need to use a data table. That way, you will be able to perform lookups more conveniently, as shown in the next How-To.

For now, the next step will be to assign the following properties of the list box:

DataSource. This will be set to the DataTable object-in this case, dtCust.
DisplayMember. This specifies which column from the data table to use for display in the list box.
ValueMember. Here, you will specify which column you want to use for the value that is retrieved when an item is selected from the list box.

By programming the ListBox control using this technique, you can access the ValueMember column in the SelectItem property of the list box.

Steps

Open and run the VB.NET-Chapter 3 solution. From the main form, click on the command button with the caption How-To 3.2. When the form loads, click on the Load List command button. You will see the list below fill with all the company names that start with A.

To save time, you can make a copy of the form that was created in the first How-To in this chapter.

Replace the btnLoadList Click event with the following code listed here in Listing 3.3. That's it. After creating the SQL string that will be used and storing it in strSQL, the data adapter called odaCust is created. The odtCust data table is then filled using odaCust. Last, the DataSource, DisplayMember, and ValueMember properties are set for the lstCustomers list box. This was all accomplished with a Try-Catch-End-Try block of code.

Listing 3.3 `frmHowTo3_2.vb`: Loading a List Box By Using the DataTable Object

Private Sub btnLoadList_Click(ByVal sender As System.Object,
                    ByVal e As System.EventArgs) Handles btnLoadList.Click

        Dim odaCust As OleDb.OleDbDataAdapter
        Dim dtCust As DataTable = New DataTable()

        Dim strSQL As String

        '-- Create the SQL String
        strSQL = "Select CustomerID, CompanyName From Customers " & _
                  "Where CustomerID Like '" &
                    Me.txtCustLimit.Text & "%'"


        '-- Set up the exception catch
        Try

            '-- Create an instance of the data adapter,
            '  and then fill the data table
            odaCust = New OleDb.OleDbDataAdapter(strSQL, _
BuildCnnStr("(local)", "Northwind"))
            odaCust.Fill(dtCust)

            '-- Bind the data to the list box
            lstCustomers.DataSource = dtCust
            lstCustomers.DisplayMember = "CompanyName"
            lstCustomers.ValueMember = "CustomerID"

        Catch oexpData As OleDb.OleDbException
            MsgBox(oexpData.Message)
        End Try

    End Sub

How It Works

When the user clicks on the btnLoadList button, the data adapter called odaCust is instantiated. The data adapter is passed strSQL and the connection string that is created by the function called BuildCnnStr, which was introduced in the first How-To in this chapter. The data table is then filled, and then the DataSource, DisplayMember, and ValueMember properties of the ListBox control are assigned.

Comments