Datasheet
lstCategory.DataValueField = "CategoryID"
lstCategory.DataTextField = "CategoryName"
lstCategory.DataBind()
End If
End Sub
When the Fetch button is clicked, we need to get the value from the DropDownList. For this, we use the
SelectedValue property, which is new to ASP.NET 1.1. This contains the ID of the selected category,
and we pass this into the
GetProducts routine, which will return a DataSet of the products. The
returned
DataSet is set to the DataSource of the grid and the DataBind method is called to bind
the data:
Sub Button1_Click(sender As Object, e As EventArgs)
DataGrid1.DataSource = GetProducts(lstCategory.SelectedValue)
DataGrid1.DataBind()
End Sub
There are two routines to fetch data, but one of them is the same as we've already seen – using a simple
DataSet to fetch data (in this case the Categories). What we want to see is the GetProducts routine,
which gets filtered data. The first thing to notice is that it accepts an
Integer argument – this will
contain the
CategoryID, passed in from the button click event:
Function GetProducts(ByVal categoryID As Integer) As System.Data.DataSet
Next we define the connection details, as we've seen in previous examples:
Dim connectionString As String = "Provider=Microsoft.Jet.OLEDB.4.0; " & _
"Ole DB Services=-4; " & _
"Data Source=C:\BegASPNET11\data\Northwind.mdb"
Dim dbConnection As System.Data.IDbConnection = _
New System.Data.OleDb.OleDbConnection(connectionString)
Then we define the query:
Dim queryString As String = "SELECT [Products].[ProductName], " & _
"[Products].[QuantityPerUnit], [Products].[UnitPrice], " & _
"[Products].[UnitsInStock] FROM [Products] " & _
"WHERE ([Products].[CategoryID] = @CategoryID)"
Note that the WHERE clause is filtering on CategoryID. However, the value used for the filter
(
@CategoryID) is not a real value but a placeholder. This tells ADO.NET that the value will be supplied
by a parameter.
Once the query string is set, we define our command to run the query, as follows:
Dim dbCommand As System.Data.IDbCommand = New System.Data.OleDb.OleDbCommand
dbCommand.CommandText = queryString
dbCommand.Connection = dbConnection
281
Reading From Databases
57076_Ch 8 SAN.qxd 01/12/2003 6:43 PM Page 281