<% '******************************************************* '* ASP 101 Sample Code - http://www.asp101.com/ * '* * '* This code is made available as a service to our * '* visitors and is provided strictly for the * '* purpose of illustration. * '* * '* http://www.asp101.com/samples/license.asp * '* * '* Please direct all inquiries to webmaster@asp101.com * '******************************************************* %> <% ' Selected constants from adovbs.inc Const adCmdStoredProc = 4 Const adInteger = 3 Const adCurrency = 6 Const adParamInput = 1 Const adParamOutput = 2 Const adExecuteNoRecords = 128 ' For more info see: ' http://www.asp101.com/articles/john/adovbs/default.asp ' Declare our vars Dim cnnStoredProc ' Connection object Dim cmdStoredProc ' Command object Dim rstStoredProc ' Recordset object (for part 2) Dim paramId ' Parameter object ' Establish connection to database (DB) Set cnnStoredProc = Server.CreateObject("ADODB.Connection") cnnStoredProc.Open "Provider=SQLOLEDB;Data Source=10.2.2.133;" _ & "Initial Catalog=samples;User Id=samples;Password=password;" _ & "Connect Timeout=15;Network Library=dbmssocn;" ' ******************************************************** ' *** Stored Proc #1 - Parameter In & Return Value Out *** ' ******************************************************** ' Create Command object we'll use to execute the SP Set cmdStoredProc = Server.CreateObject("ADODB.Command") ' Set our Command to use our existing connection cmdStoredProc.ActiveConnection = cnnStoredProc ' Message so people know what the sample's doing: Response.Write "

Running the stored procedure: " _ & "GetSalesById.

" & vbCrLf ' Set the SP's name and tell the Command object ' that the name we give is supposed to be a SP cmdStoredProc.CommandText = "GetSalesById" cmdStoredProc.CommandType = adCmdStoredProc ' Message so people know what the sample's doing: Response.Write "

Passing it two parameters:
" & vbCrLf _ & "@Id = 1
" & vbCrLf _ & "@Sales = (an output parameter)
" & vbCrLf _ & "

" & vbCrLf ' Create and add the SP's required parameters to our ' Command object. The first is an input param which ' I'm passing in with the value 1. The second is an ' output param which will return our value. This ' particular SP passes back a return value and doesn't ' return any records. Set paramId = cmdStoredProc.CreateParameter("@Id", adInteger, adParamInput) paramId.Value = 1 cmdStoredProc.Parameters.Append paramId ' If you find it simpler, it is possible to combine ' multiple commands into one long line like this: cmdStoredProc.Parameters.Append cmdStoredProc.CreateParameter("@Sales", adCurrency, adParamOutput) ' Run the SP by executing the command. I added the ' adExecuteNoRecords option so that ADO knows not to ' bother returning the empty recordset. cmdStoredProc.Execute , , adExecuteNoRecords ' Message so people know what the sample's doing: Response.Write "

It returned the value: " ' Spit out our return value which we pull out of the ' parameters collection. Response.Write cmdStoredProc.Parameters("@Sales").Value ' Message so people know what the sample's doing: Response.Write ".

" & vbCrLf ' Kill our objects Set paramId = Nothing Set cmdStoredProc = Nothing ' A little spacing for the display Response.Write vbCrLf & "

 

" & vbCrLf & vbCrLf ' ***************************************************** ' *** Stored Proc #2 - Parameter In & Recordset Out *** ' ***************************************************** ' Create Command object we'll use to execute the SP Set cmdStoredProc = Server.CreateObject("ADODB.Command") ' Set our Command to use our existing connection cmdStoredProc.ActiveConnection = cnnStoredProc ' Message so people know what the sample's doing: Response.Write "

Running the stored procedure: " _ & "GetNameInfoById.

" & vbCrLf ' Set the SP's name and tell the Command object ' that the name we give is supposed to be a SP cmdStoredProc.CommandText = "GetNameInfoById" cmdStoredProc.CommandType = adCmdStoredProc ' Message so people know what the sample's doing: Response.Write "

Passing it one parameter:
" & vbCrLf _ & "@Id = 1
" & vbCrLf _ & "

" & vbCrLf ' Create and add the SP's required parameter to our ' Command object. Only the one this time... still ' passing in the same value = 1. This time the SP ' passes back a full recordset. Set paramId = cmdStoredProc.CreateParameter("@Id", adInteger, adParamInput) paramId.Value = 1 cmdStoredProc.Parameters.Append paramId ' Run the SP by executing the command and grab ' the returned recordset. Set rstStoredProc = cmdStoredProc.Execute ' Message so people know what the sample's doing: Response.Write "

It returned a recordset which I used " _ & "to print out this name: " ' Spit out our data which I pull out of the recordset. Response.Write Trim(rstStoredProc.Fields("first_name")) Response.Write " " Response.Write Trim(rstStoredProc.Fields("last_name")) ' Message so people know what the sample's doing: Response.Write ".

" & vbCrLf ' Kill our objects Set paramId = Nothing Set rstStoredProc = Nothing Set cmdStoredProc = Nothing ' Close and kill our connection cnnStoredProc.Close Set cnnStoredProc = Nothing %>