To read the article online, visit http://www.4GuysFromRolla.com/webtech/chapters/ASPNET/ch06.3.shtml

ASP.NET: Tips, Tutorials, and Code
Chapter 6: Data Manipulation with ADO.NET


3. Using Parameters with SQL Commands

When building your ASP.NET pages, you'll need to use variables when working with SQL commands. Otherwise, you will insert the very same data every time a page is executed.

For example, if you retrieve data from a form, you'll need to use variables in your INSERT command to insert the values retrieved from the form. So far, we have not discussed how you can use variables with SQL commands.

The simplest way to use variables when working with SQL commands is to build up the command string itself with variables. An example of this can be found in Listing 6.3.1.

Listing 6.3.1 Inserting Data with Variables

 1: <%@ Import Namespace="System.Data" %>
 2: <%@ Import NameSpace="System.Data.SqlClient" %>
 3:
 4: <%
 5: Dim myConnection As SqlConnection
 6: Dim myCommand As SqlCommand
 7: Dim sqlString As String
 8:
 9: Dim FirstName As String = "Robert"
10: Dim LastName As String = "Johnson"
11:
12: myConnection = New SQLConnection( "server=localhost;uid=sa;pwd=secret;database=myData" )
13: myConnection.Open()
14: sqlString = "Insert Authors ( FirstName, LastName ) Values 
('" & FirstName & "','" & LastName & "')"
15: myCommand = New SqlCommand( sqlString, myConnection )
16: myCommand.ExecuteNonQuery()
17: myConnection.Close()
18: %>
19: New Record Inserted!
20:

The page in Listing 6.3.1 contains two variables named FirstName and LastName. You could imagine that these variables represent information retrieved from an HTML form. The variables are used to build the contents of another variable called sqlString. The sqlString variable contains a standard SQL INSERT command.

Notice that the sqlString variable is used in line 15 when initializing the SqlCommand class. When the SqlCommand is executed, the SQL command contained in the sqlString variable is executed. When the page is executed, the new author, Robert Johnson, is added to the Authors table.

The page in Listing 6.3.1 will execute without generating an error. However, it has some limitations. You will encounter three types of problems when taking this approach of using variables with SQL commands.

First, building a SQL string with variables in this manner will produce errors when the variables contain apostrophes. For example, if the value of the LastName variable is "O'Leary," SQL Server will generate an error when you attempt to execute the SQL string. SQL Server generates this error because it interprets the apostrophe as marking the end of a SQL string.


Note

One way to get around this problem with the apostrophe character is to write a function that automatically doubles up every apostrophe in a string. SQL Server will automatically interpret two apostrophes in a row as a single apostrophe.


Second, imagine that you have a very large HTML form that has, for example, 50 form fields. Building a SQL string with this number of variables would be a very tedious and error-prone operation. You'll end up with a massive string as the value of the sqlString variable.

Finally, building SQL strings out of variables in this manner makes it difficult to convert your code to use stored procedures (stored procedures are discussed in the next section, "Using Stored Procedures with ADO.NET"). You cannot use this method of building SQL strings when working with stored procedures.

Fortunately, there is a better method for using variables with SQL commands: You can explicitly create parameters for a SQL command to represent variable information.

The ASP.NET page in Listing 6.2.15 illustrates how you can use parameters with the SqlCommand class (this page is included on the CD with the name SQLParameters.aspx).

Listing 6.3.2 Using SQL Parameters

 1: <%@ Import Namespace="System.Data" %>
 2: <%@ Import NameSpace="System.Data.SqlClient" %>
 3:
 4: <%
 5: Dim myConnection As SqlConnection
 6: Dim myCommand As SqlCommand
 7: Dim FirstName As String = "Robert"
 8: Dim LastName As String = "Johnson"
 9:
10: myConnection = New SqlConnection( "server=localhost;uid=sa;pwd=secret;database=myData" )
11: myConnection.Open()
12: myCommand = New SQLCommand( "Insert Authors ( FirstName, LastName ) 
Values ( @FirstName, @LastName )", myConnection )
13:
14: myCommand.Parameters.Add( New SqlParameter( "@FirstName", 
SqlDbType.Varchar, 30 ))
15: myCommand.Parameters( "@FirstName" ).Value = FirstName
16:
17: myCommand.Parameters.Add( New SqlParameter( "@LastName", 
SqlDbType.Varchar, 30 ))
18: myCommand.Parameters( "@LastName" ).Value = LastName
19:
20: myCommand.ExecuteNonQuery()
21: myConnection.Close()
22: %>
23: Record Inserted!

In Listing 6.3.2, two parameters are created: @FirstName and @LastName. These parameters are used within the SQL INSERT command as placeholders for the values of the FirstName and LastName string variables. The SQL INSERT statement looks like this:

Insert Authors ( FirstName, LastName ) Values ( @FirstName, @LastName )

Next, the parameters are created and added to the Parameters collection of the SqlCommand class. For example, the @LastName parameter is created and added with the following statement:

myCommand.Parameters.Add( New 
SqlParameter( "@FirstName", SqlDbType.Varchar, 30 ))

The parameter is initialized with the name of the parameter (@FirstName), the data type of the parameter (SqlDbType.Varchar), and the maximum size of the value of the parameter (30 characters).

The name of the parameter must match the name that you used for the parameter placeholder in the SQL string. The name must also start with the @ character.

The data type should be a valid Microsoft SQL Server data type. When using variable-length data types, such as Varchar and VarBinary, you can also list the maximum size of the parameter. This size should match the size of the column in the underlying database table.

The value of the parameter is assigned with the following line of code:

myCommand.Parameters( "@FirstName" ).Value = FirstName

This statement assigns the value of the FirstName variable to the SqlCommand parameter named @FirstName.

The code in Listing 6.3.2 will only work with Microsoft SQL Server because it uses the classes from the System.Data.SqlClient namespace. The page in Listing 6.3.3 uses the classes from the System.Data.OleDb namespace to work with Microsoft Access (this page is included on the CD as OleDbParameters.aspx).

Listing 6.3.3 Using Parameters with Microsoft Access

 1: <%@ Import Namespace="System.Data" %>
 2: <%@ Import NameSpace="System.Data.OleDb" %>
 3:
 4: <%
 5: Dim myConnection As OleDbConnection
 6: Dim myCommand As OleDbCommand
 7: Dim FirstName As String = "Robert"
 8: Dim LastName As String = "Johnson"
 9:
10: myConnection = New OleDbConnection( "PROVIDER=Microsoft.Jet.OLEDB.4.0;
DATA Source=c:\author2.mdb" )
11: myConnection.Open()
12: myCommand = New OleDbCommand( "Insert INTO Authors ( FirstName, LastName )
 Values ( @FirstName, @LastName )", myConnection )
13:
14: myCommand.Parameters.Add( New OleDbParameter( "@FirstName", 
OleDbType.Varchar, 30 ))
15: myCommand.Parameters( "@FirstName" ).Value = FirstName
16:
17: myCommand.Parameters.Add( New OleDbParameter( "@LastName", 
OleDbType.Varchar, 30 ))
18: myCommand.Parameters( "@LastName" ).Value = LastName
19:
20: myCommand.ExecuteNonQuery()
21: myConnection.Close()
22: %>
23: Record Inserted!
24:

Notice that the data types used for initializing the OleDbParameter class are different from those used for the SqlParameter class. When working with the OleDbParameter class, you must use values from the OleDbType enumeration. So, to represent an Access Text column, you must use the value OleDbType.Varchar.


Note

The SqlDbType enumeration can be found in the System.Data namespace, and the OleDbType enumeration can be found in the System.Data.OleDb namespace.


  • Read Part 4


  • Article Information
    Article Title: Data Manipulation with ADO.NET
    Article Author: Steve Walther
    Published Date: Friday, September 07, 2001
    Article URL: http://www.4GuysFromRolla.com/webtech/chapters/ASPNET/ch06.3.shtml


    Copyright 2017 QuinStreet Inc. All Rights Reserved.
    Legal Notices, Licensing, Permissions, Privacy Policy.
    Advertise | Newsletters | E-mail Offers