Datasheet

Now we come to the definition of the parameter. Like many of the other examples, this uses a
database-specific object – an
OleDbParameter, which defines what is being passed into the query:
Dim dbParam_categoryID As System.Data.IDataParameter = _
New System.Data.OleDb.OleDbParameter
We then set the properties of the parameter. The ParameterName indicates the name of the parameter,
and we set the value to be the same as the placeholder. The
Value property stores the value for the
parameter, and is set to the
CategoryID passed into the procedure from the button click event – it's the
ID of the category selected from the list. The
DbType property indicates the database type – Int32 is the
database equivalent of an
Integer:
dbParam_categoryID.ParameterName = "@CategoryID"
dbParam_categoryID.Value = categoryID
dbParam_categoryID.DbType = System.Data.DbType.Int32
At this point, even though we have a Parameter object, it's not associated with the command, so we
add it to the
Parameters collection of the command:
dbCommand.Parameters.Add(dbParam_categoryID)
When ADO.NET processes the command, it matches parameters in the collection with the placeholders
in the query and substitutes the placeholder with the value in the parameter.
The rest of the code is as we've seen it before. We create a
DataAdapter to run the command, and use
the
Fill() method to fetch the data into our DataSet:
Dim dataAdapter As System.Data.IDbDataAdapter = _
New System.Data.OleDb.OleDbDataAdapter
dataAdapter.SelectCommand = dbCommand
Dim dataSet As System.Data.DataSet = New System.Data.DataSet
dataAdapter.Fill(dataSet)
Return dataSet
End Function
As you can see, there really isn't that much code; even though we've introduced a new object, much of
the code remains the same, so you've added to existing knowledge.
Filtering Queries
There's a very important point to know about filtering data, as you may see code elsewhere that uses a
bad method of doing it – it simply builds up the SQL string (as we've done), but instead of using
parameters, it just appends the filter value to the SQL string. For example, you might see this:
Dim queryString As String = "SELECT [Products].[ProductName], " & _
"[Products].[QuantityPerUnit], [Products].[UnitPrice], " & _
"[Products].[UnitsInStock] FROM [Products] " & _
"WHERE ([Products].[CategoryID] = " & CategoryID & ")"
282
Chapter 8
57076_Ch 8 SAN.qxd 01/12/2003 6:43 PM Page 282