Query Parameters and Information Handling with Databases

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).

Contributed by McGraw-Hill/Osborne
Rating:

/ 13
April 13, 2006

Rate this Article:
MEH MEH++

SEARCH ASP FREE

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 reﬂect 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>

Inserting a Row

You can insert new information into a database by using the insert statement in a query. The insert statement inserts a new row and places data into one or more columns of the row, depending on the nature of your application.

The insert statement requires the table name, the column names, and a value for each column. Here’s the insert statement:

Insert Into custContact (custNumber, custFirstName, custLastName) Values ('0987', 'Mike',
'Jones')

This statement inserts a new row that contains three columns of the custContact table. The names of the columns are specified within the first set of parentheses, each separated from the next by a comma. The second set of parentheses contains the values that are to be placed in each column. Notice that the values are in the same order as the column names. That is, the customer number is placed in the custNumber column, the customer first name is placed in the custFirstName column, and so on.

It is important to remember that the logon used to access the database must have proper permission to insert data into the table. Likewise, you must be sure that the data being inserted into a column is of a compatible data type with the column. For example, a numeric value must be placed into a column that has a numeric data type.

The following is the complete code that you need to insert a new row into a Microsoft SQL Server database:

<%@ Import Namespace="System.Data.SqlClient" %>
<%
Dim custDb As SqlConnection
Dim cmdInsertCustomers As SqlCommand
custDb = New SqlConnection("Server=localhost;uid=myID;pwd=mypassword; database=customer")
cmdInsertCustomers = New SqlCommand("Insert Into custContact (custNumber, custFirstName,
custLastName) Values ('0987', 'Mike', 'Jones')", custDb)
custDb.Open()
cmdInsertCustomers.ExecuteNonQuery()
custDb.Close()
%>

Here is the same code for Microsoft Access:

<%@ Import Namespace="System.Data.OleDb" %>
<%
Dim custDb As OleDbConnection
Dim cmdInsertCustomers As OleDbCommand
custDb = New OleDbConnection( "PROVIDER=Microsoft.Jet.OLEDB.4.0;Data Source=c: cust.mdb")
cmdInsertCustomers = New OleDbCommand("Insert Into custContact (custNumber,
custFirstName, custLastName) Values ('0987', 'Mike', 'Jones')", custDb)
custDb.Open()
cmdInsertCustomers.ExecuteNonQuery()
custDb.Close()
%>

Let’s incorporate the code into a form so that you can enter information directly from your web page. This form is very similar to the preceding form example. Here is the Microsoft SQL Server version:

<%@ 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 cmdInsertCustomers As SqlCommand
      custDb = New SqlConnection("Server=localhost;uid=myID;pwd=mypassword;
database=customer")
      cmdInsertCustomers = New SqlCommand("Insert Into custContact (CustNumber,
CustFirstName, CustLastName) Values (txtCustNumber.Text, txtCustFirstName.text,
txtCustLastName.text)", custDb)
      cmdInsertCustomers.Parameters. AddWithValue( "@CustNumber", txtCustNumber.Text )
      cmdInsertCustomers.Parameters. AddWithValue( "@CustFirstName", txtCustFirstName.Text
)
      cmdInsertCustomers.Parameters.
AddWithValue( "@CustLastName", txtCustLastName.Text )
      custDb.Open()
      cmdInsertCustomers.ExecuteNonQuery()
  custDb.Close()
End Sub
</Script>
<html>
   <head><title>New Customer</title></head>
   <body>
      <form Runat="Server">
         <b>Customer Number:</b>
         <br>
         <asp:TextBox ID="txtCustNumber" Runat="Server" />
           <b>Customer First Name:</b>
           <br>
           <asp:TextBox ID="txtCustFirstName" Runat="Server" />
         <p>
            <br />
            <b>Customer Last Name:</b>
            <br>
            <asp:TextBox ID="txtCustLastName" Runat="Server" />
         <p>
         <p>
           <asp:Button Text="Add Customer" OnClick="Button_Click" Runat="Server" />
         <p>
      </form>
   </body>
</html>

Here is the Microsoft Access version:

<%@ 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 cmdInsertCustomers As OleDbCommand
      custDb = New OleDbConnection( "PROVIDER=Microsoft.Jet.OLEDB.4.0;Data Source=c:
cust.mdb")
      cmdInsertCustomers = New OleDbCommand("Insert Into custContact (CustNumber,
CustFirstName, CustLastName) Values (txtCustNumber.Text, txtCustFirstName.text,
txtCustLastName.text)", custDb)
      cmdInsertCustomers.Parameters. AddWithValue( "@CustNumber", txtCustNumber.Text )
      cmdInsertCustomers.Parameters. AddWithValue( "@CustFirstName", txtCustFirstName.Text
)
      cmdInsertCustomers.Parameters. AddWithValue( "@CustLastName", txtCustLastName.Text )
      custDb.Open()
         cmdInsertCustomers.ExecuteNonQuery()
     custDb.Close()
End Sub
</Script>
<html>
   <head><title>New Customer</title></head>
   <body>
      <form Runat="Server">
         <b>Customer Number:</b>
         <br>
         <asp:TextBox ID="txtCustNumber" Runat="Server" />
           <br />
           <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>
        <p>
          <asp:Button Text="Add Customer" OnClick="Button_Click" Runat="Server" />
        <p>
     </form>
</body>
</html>

Updating a Row

You can change data already in a database from within your application by creating an update query. An update query replaces the existing value in a column with the value that you specify in the query.

The update query must contain four pieces of information. These are:

Table name Name of the table that contains the rows that are being updated
Column name(s) Name(s) of the columns that are being updated
Value(s) The value(s) that is replacing the current value of the column(s)
Selection criteria Identify the row(s) that you want updated

Here is the update query. We are telling the DBMS to find the row in the custContact table where the custNumber is 1234. Once it is found, replace the content of the custFirstName with Bobby.

Update custContact SET custFirstName = 'Bobby'
Where custNumber = "1234"

Here is how to update a row in Microsoft SQL Server by using a customer number and customer first name. This example is very similar to the preceding form example in this chapter except that the query is different.

<%@ 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 cmdUpdateCustomers As SqlCommand
      custDb = New SqlConnection("Server=localhost;uid=myID;pwd=mypassword;
database=customer")
      cmdUpdateCustomers = New SqlCommand("Update custContact SET custFirstName =
txtCustFirstName.Text Where custNumber = @CustNumber", custDb)
      cmdUpdateCustomers.Parameters. AddWithValue( "@CustNumber", txtCustNumber.Text )
      cmdUpdateCustomers.Parameters. AddWithValue( "@CustFirstName", txtCustFirstName.Text
)
      cmdUpdateCustomers.Parameters.
AddWithValue( "@CustLastName", txtCustLastName.Text )
      custDb.Open()
         cmdUpdateCustomers.ExecuteNonQuery()
     custDb.Close()
End Sub
</Script>
<html>
   <head><title>New Customer</title></head>
   <body>
      <form Runat="Server">
         <b>Customer Number:</b>
         <br>
         <asp:TextBox ID="txtCustNumber" Runat="Server" />
         <BR />
         <b>Customer First Name:</b>
         <br>
         <asp:TextBox ID="txtCustFirstName" Runat="Server" />
         <p>
           <asp:Button Text="Update Customer" OnClick="Button_Click" Runat="Server" />
         <p>
      </form>
   </body>
</html>

Here is the Microsoft Access version:

<%@ 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 cmdUpdateCustomers As OleDbCommand
      custDb = New OleDbConnection( "PROVIDER=Microsoft.Jet.OLEDB.4.0;Data Source=c:
cust.mdb")
      cmdUpdateCustomers = New OleDbCommand("Update custContact SET custFirstName =
txtCustFirstName.Text Where custNumber = @CustNumber", custDb)
      cmdUpdateCustomers.Parameters. AddWithValue( "@CustNumber", txtCustNumber.Text )
      cmdUpdateCustomers.Parameters. AddWithValue( "@CustFirstName", txtCustFirstName.Text
)
      cmdUpdateCustomers.Parameters. AddWithValue( "@CustLastName", txtCustLastName.Text )
      custDb.Open()
         cmdUpdateCustomers.ExecuteNonQuery()
     custDb.Close()
End Sub
</Script>
<html>
   <head><title>New Customer</title></head>
   <body>
      <form Runat="Server">
         <b>Customer Number:</b>
         <br>
         <asp:TextBox ID="txtCustNumber" Runat="Server" />
         <br />
           <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>
           <asp:Button Text="Update Customer" OnClick="Button_Click" Runat="Server" />
         <p>
      </form>
   </body>
</html>

Deleting a Row

One or more rows can be removed from a table by using the Delete statement in a query. The Delete statement requires the name of the table and a Where clause that identifies the row or rows that are to be deleted.

Let’s say that we want to delete the record for customer 1234. Here’s the Delete statement that we’ll send to the DBMS:

Delete custContact Where custNumber = "1234"

This statement tells the DBMS to find the row in the custContact table that has 1234 in its custNumber column and then delete the row. Nothing happens if the row isn’t found. Keep in mind that the login used to contact to the DBMS must be authorized to delete the row; otherwise, the row will not be deleted.

Here’s how to use a form to delete a row in Microsoft SQL Server: