Query Parameters and Information Handling with Databases
(Page 1 of 4 )
This article, the second of three parts, explains how to link your ASP.NET web pages with your own database by using the ADO.NET connection, as well as how to write SQL statements in a query that direct the DBMS to perform common tasks. It is excerpted from chapter 10 of
ASP.NET 2.0 DeMYSTiFieD, written by Jim Keogh (McGraw-Hill/Osborne; ISBN: 0072261412).
Query Parameters
In the preceding example, the value of the search criterion was inserted into the WHERE clause of the query. In the real world, however, the visitor to your web site usually enters the search value into a web form. Therefore, you need to have a placeholder for the search criterion in the query that is replaced by the actual value that the visitor enters when your application runs.
The placeholder is referred to as a parameter, which is similar to parameters used for functions (see Chapter 7). You then use the parameter in the query as if the parameter were the actual value. The value replaces the parameter once the value is received from the visitor to your web site.
Parameters are represented by a parameter class. You define a parameter by calling the AddWithValue() method of the Parameters class as illustrated here:
cmdSelect.Parameters.AddWithValue( "@CustFirstName", txtCustFirstName.Text)
The cmdSelect is used to call the AddWithValue() method of the Parameters class, passing it two parameters. The first parameter is the name of the parameter that you are adding to the parameter collection. The second parameter is the value that is associated with the parameter. In the preceding example, the value called txtCustFirstName.Text is the text of the txtCustFirstName textbox that appears on your web page. You use @CustFirstName in your query just as if @CustFirstName were an explicit value.
You can specify the data type and maximum number of characters that can be accepted by the system by modifying the call to AddWithValue(). Here’s how this is done:
cmdSelectCustomers.Parameters.AddWithValue ( "@CustFirstName ", SqlDbType.Varchar, 25
).Value = txtCustFirstName.Text
You’ll notice that the AddWithValue() method takes on a slightly different form than the preceding example. The first argument is the name of the parameter.
The second argument is the data type of the parameter. The data type is automatically chosen for you if you exclude the data type as was done in the preceding example. The data type must reflect the namespace that is associated with the DBMS. SqlDbType is used for the SqlDb namespace, which is for Microsoft SQL Server. The OleDbType is used for Microsoft Access. Namespaces for other DBMSs have similar data type names.
The third argument is the maximum number of characters that can be assigned to the parameter. In this example the customer first name can have up to 25 characters. If you exclude the size parameter, then the maximum size is automatically determined by the value of the parameter.
Here’s the full code for Microsoft SQL Server:
<%@ Import Namespace="System.Data" %>
<%@ Import Namespace="System.Data.SqlClient" %>
<Script Runat="Server">
Sub Button_Click( s As Object, e As EventArgs )
Dim custDb As SqlConnection
Dim cmdSelectCustomers As SqlCommand
Dim dtrCustomers As SqlDataReader
custDb = New SqlConnection("Server=localhost;uid=myID;pwd=mypassword;
database=customer")
cmdSelectCustomers = New SqlCommand("Select custFirstName, custLastName From
custContact Where custNumber=@CustNumber", custDb)
cmdSelectCustomers.Parameters.AddWithValue ( "@CustNumber", txtCustNumber.Text )
custDb.Open()
dtrCustomers = cmdSelectCustomers.ExecuteReader()
While dtrCustomers.Read()
txtCustFirstName.text = dtrCustomers( "custFirstName" )
txtCustLastName.text = dtrCustomers( "custLastName" )
End While
dtrCustomers.Close()
custDb.Close()
End Sub
</Script>
<html>
<head><TITLE>Customer Locator</title></HEAD>
<body>
<form Runat="Server">
<b>Customer Number:</b>
<br>
<asp:TextBox ID="txtCustNumber" Runat="Server" />
<p>
<asp:Button Text="Locate" OnClick="Button_Click" Runat="Server" />
<p>
<b>Customer First Name:</b>
<br>
<asp:TextBox ID="txtCustFirstName" Runat="Server" />
<p>
<b>Customer Last Name:</b>
<br>
<asp:TextBox ID="txtCustLastName" Runat="Server" />
<p>
</form>
</body>
</HTML>
This code prompts the web site visitor to enter a customer number into a textbox and select the Locate button to search the database for the name that is associated with the customer number. Once the customer number is located, the DBMS returns the customer first name and customer last name, which are then displayed in textboxes on the form.
The code begins by defining a button click event handler for the Locate button. You’ll notice that the event handler contains nearly the same code that we discussed previously in this chapter.
However, there is one difference, in that we define and use the @CustNumber parameter. The @CustNumber parameter has the text value that the visitor entered into the txtCustNumber textbox and is compared with the value of custNumber column of the table in the Where clause of the query. After the query executes, the code copies the value of the custFirstName and custLastName columns to the corresponding textboxes that appear on the form.
The web page itself displays three textboxes, for the customer number and customer first and last names, as well as the Locate button.
Here is the Microsoft Access version of this application:
<%@ Import Namespace="System.Data" %>
<%@ Import Namespace="System.Data.OleDb " %>
<Script Runat="Server">
Sub Button_Click( s As Object, e As EventArgs )
Dim custDb As OleDbConnection
Dim cmdSelectCustomers As OleDbCommand
Dim dtrCustomers As OleDbDataReader
custDb = New OleDbConnection( "PROVIDER=Microsoft.Jet.OLEDB.4.0;Data Source=c:
cust.mdb")
cmdSelectCustomers = New OleDbCommand("Select custFirstName, custLastName From
custContact Where custNumber=@CustNumber", custDb)
cmdSelectCustomers.Parameters.AddWithValue ( "@CustNumber", txtCustNumber.Text )
custDb.Open()
dtrCustomers = cmdSelectCustomers.ExecuteReader()
While dtrCustomers.Read()
txtCustFirstName.text = dtrCustomers( "custFirstName" )
txtCustLastName.text = dtrCustomers( "custLastName" )
End While
dtrCustomers.Close()
custDb.Close()
End Sub
</Script>
<html>
<head><title>Customer Locator</title></head>
<body>
<form Runat="Server">
<b>Customer Number:</b>
<br>
<asp:TextBox ID="txtCustNumber" Runat="Server" />
<p>
<asp:Button Text="Locate" OnClick="Button_Click" Runat="Server" />
<p>
<b>Customer First Name:</b>
<br>
<asp:TextBox ID="txtCustFirstName" Runat="Server" />
<p>
<b>Customer Last Name:</b>
<br>
<asp:TextBox ID="txtCustLastName" Runat="Server" />
<p>
</form>
</body>
</html>
Next: Inserting a Row >>
More ASP.NET Articles
More By McGraw-Hill/Osborne
|
 This article is excerpted from chapter 10 of ASP.NET 2.0 DeMYSTiFieD, written by Jim Keogh (McGraw-Hill/Osborne; ISBN: 0072261412). Check it out today at your favorite bookstore. Buy this book now.
|
|
