Here is a sample against NWind using stored procedures
'''****************************************
<HTML>
<HEAD>
<TITLE>Using Stored Procedures</TITLE>
<%
@LANGUAGE="VBSCRIPT"
%>
<!--METADATA TYPE="typelib" UUID="00000205-0000-0010-8000-00AA006D2EA4"
NAME="ADODB Type Library" -->
<!--
The above line declares the ADO type library so that we can use constants.
For details see
http://msdn.microsoft.com/library/d...wss/_exch2k_working_with_asp_applications.asp
-->
</HEAD>
<BODY>
Calling Parameterised Stored Procedures from ASP
<%
Dim dbUserName
Dim dbPassword
Dim dbCatalog
Dim dbServer
dbServer = "YourServerName."
dbcatalog = "Northwind"
dbUserName = "ia"
dbPassword = "001test"
Dim oConn
Dim strConn
Dim oCmd
Dim rsData
Dim strSQL
strConn="Provider=SQLOLEDB.1;Persist Security Info=False;User ID=" &
dbUserName & ";Password=" & dbPassword & ";Initial Catalog=" & dbcatalog &
";Data Source=" & dbServer
Response.Write "Please select a category<BR>"
strSQL = "select CategoryID, CategoryName from categories"
SET oConn = Server.CreateObject("ADODB.Connection")
SET oCmd = Server.CreateObject("ADODB.Command")
oConn.Open strconn
SET rsdata = oconn.Execute (strsql)
%>
<FORM method="post" action="lesson3.asp" ID="Form1">
<select name="iCatID">
<%
' Build the list box
while not rsdata.eof
response.write "<OPTION VALUE = """ +
CSTR(rsdata.fields("CategoryID").value) + """>" +
rsdata.fields("CategoryName").value + "</Option>" + VBCR
rsdata.movenext
wend
%>
</select>
<INPUT type="submit" value="Enter"> <input type="reset" value="reset">
</FORM>
<%
if (Request.Form("iCatID")<>"") then
Set oConn = Server.CreateObject("ADODB.Connection")
Set oCmd = Server.CreateObject("ADODB.Command")
oConn.Open strConn
Set oCmd.ActiveConnection = oConn
' Setup Call to Stored Procedure and append parameters
oCmd.CommandText = "spGetProducts"
ocmd.Commandtype = adCmdStoredProc
oCmd.Parameters.Append ocmd.CreateParameter("@Return_Value", adInteger,
adParamReturnValue)
oCmd.Parameters.Append oCmd.CreateParameter("@CategoryID", adInteger,
adParamInput)
' Assign value to input parameter
oCmd.Parameters("@CategoryID") = Request.Form("iCatID")
' Fire the Stored Proc and assign resulting recordset
' to our previously created object variable
Set rsData = oCmd.Execute
response.Write "Passed in Value = " & Request.Form("iCatID") & "<BR>"
Response.Write "Error Value = " & clng(oCmd("@RETURN_VALUE").Value) &
"<BR>"
%>
<BR>
<BR>
<B>Results</B><BR>
<table width="90%" cellspacing="1" cellpadding="5" border="1">
<%
Dim iCount
'Load the Header Row
response.write "<tr>"
For iCount = 0 to rsData.Fields.Count - 1
response.write "<th>"
response.write rsData.Fields(iCount).Name
response.write "</th>"
Next
response.write "</tr>"
' Load the data Rows
while not rsData.EOF
response.write "<tr>"
For iCount = 0 to rsData.Fields.Count - 1
response.write "<td>"
response.write rsData.Fields(iCount).value
response.write "</td>"
next
response.write "</tr>"
rsData.MoveNext
wend
%>
</table>
<BR>
<FONT color="red">Done.
<br>
</FONT>
<%
end if
%>
</BODY>
</HTML>
''''****************************************
