Home arrow ASP arrow Page 3 - Pass That Parameter Over Here!
ASP

Pass That Parameter Over Here!


Stored procedures are a feature of popular database management systems such as Oracle and Microsoft SQL Server 2000. This article will give you a quick overview of stored procedures and how to pass parameters to them from ASP.

Author Info:
By: Mitchell Harper
Rating: 5 stars5 stars5 stars5 stars5 stars / 18
December 02, 2002
TABLE OF CONTENTS:
  1. · Pass That Parameter Over Here!
  2. · Creating the stored procedure
  3. · Executing our stored procedure
  4. · Conclusion

print this article
SEARCH DEVARTICLES

Pass That Parameter Over Here! - Executing our stored procedure
(Page 3 of 4 )

To execute our stored procedure, create a new ASP page and save it as storedproc.asp. Enter the following code into storedproc.asp:

1. <html>

2. <head>

3. <title> Using Parameters with stored procedures </title>

4. </head>

5. <body>

6. <!-- METADATA Type="TypeLib" File="c:\program files\common files\system\ado\msado15.dll" -->

7.

8. <%

9.

10. dim strState

11. dim dbConn

12. dim dbComm

13.

14. strState = "CA"

15.

16. set dbConn = Server.CreateObject("ADODB.Connection")

17. set dbComm = Server.CreateObject("ADODB.Command")

18.

19. dbConn.Open "Provider=SQLOLEDB; Data Source=(local); Initial Catalog=pubs; UId=sa; Pwd="

20.

21. dbComm.ActiveConnection = dbConn

22. dbComm.CommandType = adCmdStoredProc

23. dbComm.CommandText = "sp_GetAuthorCountByState"

24.

25. dbComm.Parameters.Append dbComm.CreateParameter("stateName", adChar, adParamInput, 2, strState)

26. dbComm.Parameters.Append dbComm.CreateParameter("numResults", adInteger, adParamReturnValue)

27.

28. dbComm.Execute

29.

30. Response.Write "The number of authors whose state matched " & strState & " was " & dbComm.Parameters(1).Value

31.

32. %>

33. </body>

34. </html>


The code above uses ADO connection and command objects to connect to and execute queries against SQL Server 2000.

On line 6 we’re using a META tag to import all the database constants including connection options, lock types, etc from the MSADO15.dll type library located in the c:\program files\common files\system\ado folder. This saves us the hassle of having to remember the values for the CommandType parameter of our command object, etc because it is already pre-declared for us.

On line 14 we set the value of the strState variable to “CA”. We will be using this variable as the value of our input parameter.

Lines 16-19 declare our connection and command objects and open a connection to our database server. On lines 21-23 we set the ActiveConnection parameter of our command object to point to objConn (Our open connection object), set our command object to parse all of its commands as stored procedures and finally let our command object know that we will be working with the stored procedure named sp_GetAuthorCountByState.

Remember back to when we created our stored procedure in Query Analyser? We declared one input parameter and one output parameter. Our input parameter was used to “pass in” the value of the state that we wanted our authors to live in and the output parameter was used to hold the number of authors who lived in that state.

On lines 25 and 26, we’re creating our input and output variables respectively. We simply call the append method of the parameters collection of our command object and parse a new parameter object using the CreateParamater method of our command object.

In our ASP script, we create a new input parameter named stateName. The stateName parameter is of type adChar (character) and is two bytes long. The last value used to create our input parameter is the actual value that we want to assign to it. In our case, we are assigning the value of the strState variable to our parameter.

Creating our output parameter is a bit simpler because we don’t need to specifiy every value for it. We create our new output parameter and name it numResults. It’s of type adInteger (integer). The last value specified for our output parameter, adParamReturnValue lets ADO know that we are expecting our parameter to contain a return value after we have execute our stored procedure.

The two parameters that we have passed to our stored procedure are indexed so that we can refer to them easily from the parameters collection of our command object. The input parameter, stateName has an index of zero. The output parameter, numResults has an index of one. If we had another parameter, it would have an index of two, etc.

It should be no surprise that line 28 executes our stored procedure. If all goes well, our output parameter will contain the number of rows whose state value matched the value of our input parameter (“CA”). Lastly, we use the Response.Write method to print out the number of results (dbComm.Paramaters(1).Value) returned and the state (dbComm.Paramaters(0).Value) that we were filtering on.

If the stored procedure executed OK, you should be greeted with a blank page containing the number of results that matched our query and the actual value of the state that we were searching for:

The result of our ASP script when using parameteri
blog comments powered by Disqus
ASP ARTICLES

- Central Scoreboard with Flash and ASP
- Calorie Counter Using WAP and ASP
- Creating PGP-Encrypted E-Mails Using ASP
- Be My Guest in ASP
- Session Replacement in ASP
- Securing ASP Data Access Credentials Using t...
- The Not So Ordinary Address Book
- Adding and Displaying Data Easily via ASP an...
- Sending Email From a Form in ASP
- Adding Member Services in ASP
- Removing Unconfirmed Members
- Trapping HTTP 500.100 - Internal Server Error
- So Many Rows, So Little Time! - Case Study
- XDO: An XML Engine Class for Classic ASP
- Credit Card Fraud Prevention Using ASP and C...

Watch our Tech Videos 
Dev Articles Forums 
 RSS  Articles
 RSS  Forums
 RSS  All Feeds
Write For Us 
Weekly Newsletter
 
Developer Updates  
Free Website Content 
Contact Us 
Site Map 
Privacy Policy 
Support 

Developer Shed Affiliates

 




© 2003-2017 by Developer Shed. All rights reserved. DS Cluster - Follow our Sitemap
Popular Web Development Topics
All Web Development Tutorials