A example of how to retrieve multiple recordsets from ADO and how to set parameters in ADO for SQL Server Stored Procedures
"
ReDim allData(0) @# initialize array dimension
datetime = Now()
Response.Write titleString
Set conn = Server.CreateObject("ADODB.Connection")
Set cmd = Server.CreateObject("ADODB.Command")
conn.Open Application("connectionString")
With cmd
Set .ActiveConnection = conn
.CommandText = StoredProcedure
@# always use ADO constants
.CommandType = adCmdStoredProc
@# Check into the NamedParameters property at some point
@# It doesn@#t require the order to be enforced, but it is always a good idea to enforce it anyway (for the documentation aspect of coding)
@# RETURN parameter needs to be first
.Parameters.Append cmd.CreateParameter("RETURN", adInteger, adParamReturnValue, 4)
.Parameters.Append .CreateParameter("@myBigInt", adBigInt, adParamInput, 8, 996857543543543)
.Parameters.Append .CreateParameter("@myInt", adInteger, adParamInput, 4, 543543)
.Parameters.Append .CreateParameter("@mySmallint", adSmallInt, adParamInput, 2, 32765)
.Parameters.Append .CreateParameter("@myTinyint", adTinyInt, adParamInput, 1, 254)
.Parameters.Append .CreateParameter("@myBit", adBoolean, adParamInput, 4, True)
@# Only Decimal and Numeric needs Precision and NumericScale
.Parameters.Append .CreateParameter("@myDecimal", adDecimal, adParamInput, 9, 765.5432321)
With .Parameters.Item("@myDecimal")
.Precision = 10
.NumericScale = 7
End With
Set prm = .CreateParameter("@myNumeric", adNumeric, adParamInput, 5, 432.6544)
prm.Precision = 7
prm.NumericScale = 4
.Parameters.Append prm
Set prm = Nothing
.Parameters.Append .CreateParameter("@myMoney", adCurrency, adParamInput, 8, 543.1234)
.Parameters.Append .CreateParameter("@mySmallMoney", adCurrency, adParamInput, 4, 543.1234)
.Parameters.Append .CreateParameter("@myFloat", adDouble, adParamInput, 8, 5.4E+54)
.Parameters.Append .CreateParameter("@myReal", adSingle, adParamInput, 4, 2.43E+24)
.Parameters.Append .CreateParameter("@myDatetime", adDBTimeStamp, adParamInput, 8, datetime)
.Parameters.Append .CreateParameter("@mySmallDatetime", adDBTimeStamp, adParamInput, 4, datetime)
.Parameters.Append .CreateParameter("@myChar", adChar, adParamInput, 4, "QWE")
.Parameters.Append .CreateParameter("@myVarchar", adVarchar, adParamInput, 10, "Variable!")
.Parameters.Append .CreateParameter("@myText", adLongVarChar, adParamInput, Len(titleString))
.Parameters.Item("@myText").AppendChunk titleString
.Parameters.Append .CreateParameter("@myNChar", adWChar, adParamInput, 4, "WIDE")
.Parameters.Append .CreateParameter("@myNVarchar", adVarWchar, adParamInput, 10, "")
.Parameters.Append .CreateParameter("@myNText", adLongVarWChar, adParamInput, Len(titleString))
.Parameters.Item("@myNText").AppendChunk titleString
@# note the difference in these - without the {} the string implicitly converts
@# the adVarChar version is of course commented out
@#.Parameters.Append .CreateParameter("@myGuid", adVarChar, adParamInput, 36, "58F94A80-B839-4B35-B73C-7F4B4D336C3C")
.Parameters.Append .CreateParameter("@myGuid", adGUID, adParamInput, 16, "{58F94A80-B839-4B35-B73C-7F4B4D336C3C}")
Set rs = .Execute
@#get column names
ReDim colNames(rs.Fields.Count - 1)
For i = 0 to rs.Fields.Count - 1
colNames(i) = rs.Fields.Item(i).Name
Next
Do While Not (rs Is Nothing)
@# get initial recordset
If Not rs.EOF Then
@# for retrieving more than about 30 or so recordsets you would probably want to use a collection
allData(UBound(allData)) = rs.GetRows(adGetRowsRest)
End If
@# this will be nothing if no recordset is returned
Set rs = rs.NextRecordset
@# resize array if needed
If Not (rs Is Nothing) Then ReDim Preserve allData(UBound(allData) + 1)
Loop
@# must release the recordset before retrieving output parameters and/or the return value
ReleaseObj rs, True, True
ret = CStr(.Parameters.Item("RETURN").Value)
End With
ReleaseObj cmd, False, True
ReleaseObj conn, True, True
@# show stored procedure
proc = GetStoredProcedureDefinition(StoredProcedure)
With Response
outputNamedGetRowsArray allData, colNames
.Write " "
.Write "Return Value: " & ret & "
"
.Write "" & proc & ""
End With
displayAspFile Server.MapPath("adodb.command3.asp")
Response.Write "