- Differences Between ADO and ADO.NET
- Objects That Are Found in ADO.NET
- Retrieve Data by Using the DataReader Object
- Retrieve Results from SQL Server by Using the DataTable Object
- Locate Records with the DataTable Object
- Filter and Sort Records Using the DataView Object
This chapter is from the book
This chapter is from the book
This chapter is from the book
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
Using the data table sets up the scene for using the list box in retrieving data in the next How-To. Remember: By using the DataTable object, you can assign both the display value and the data item to be tracked.