ADO and Stored Procedures
More developers are starting to use stored procedures with ADO to optimize the performance of database interactions. Stored procedures are easy to create and use. You can implement stored procedures in your code in many ways. First, you can use a stored procedure with ADO as you would use a stored procedure with a SQL statement. The Listing called "Two Ways to Use a Stored Procedure," which you can download at the link to this article at http://www.sqlmag.com, shows two ways to use stored procedures with ADO. The cmdGetTitles_click event procedure sets the CommandText property of the Command object to the stored procedure's name, as Callout A in the online listing shows:
cmd.CommandText = "GetTitles"
Next, the code sets the CommandType property to the adCmdStoredProc constant:
cmd.CommandType = adCmdStoredProc
The remaining code is similar to the examples in Listing 1 and Listing 2.
The GetTitles stored procedure doesn't require any parameters and works without them. However, if you want to use a stored procedure that requires parameters, you'll need to make a minor change. For example, you can change the code in the cmdGetTitles_Click procedure to call the GetTitleByID stored procedure (in Listing 3) with this code:
cmd.CommandText = "GetTitlebyID '" &
Trim$(txtTitleID) & "_"
cmd.CommandType = adCmdStoredProc
When you execute this code, you'll see an error message because the CommandType is telling ADO that you're calling a stored procedure. However, the command you're really trying to send to SQL Server is
Exec GetTitlebyID 'BU1032'
You can see this situation by starting a new trace with the SQL Server Profiler on the database server. Then watch what happens when you execute the above Exec command with the CommandType set to adCmdText, as the online listing shows. The original command doesn't even reach SQL Server because ADO catches the command and evaluates the call to the stored procedure. Because the command is both the stored procedure name and the parameter, the call fails and generates an error. To avoid this error, You'll need to change the CommandType to use the adCmdText constant, as the following code shows:
cmd.CommandType = adCmdText
Now, when you watch this command execute in SQL Server Profiler, you'll see the Exec command:
Exec GetTitlebyID 'BU1032'
Prev. page
1
[2]
3
next page 
