从ADO中得到多个记录集以及怎么样在ADO中使用sql server 的存储过程
DataType
Value
Length
Data Length
BIGINT
996857543543543
15
8
INT
543543
6
4
SMALLINT
32765
5
2
TINYINT
254
3
1
BIT
True
1
1
DECIMAL
765.5432321
11
9
NUMERIC
432.6544
8
5
MONEY
543.1234
6
8
SMALLMONEY
543.1234
6
4
FLOAT
5.4E+54
8
8
REAL
2.43E+24
9
4
DATETIME
8/31/2003 11:55:25 PM
19
8
SMALLDATETIME
8/31/2003 11:55:00 PM
19
4
CHAR
QWE
3
4
VARCHAR
Variable!
9
9
TEXT
307
NCHAR
WIDE
4
8
NVARCHAR
0
0
NTEXT
614
GUID
{58F94A80-B839-4B35-B73C-7F4B4D336C3C}
36
16
Return Value: 0
CREATE PROCEDURE "dbo"."DataTypeTester"
@myBigInt bigint
, @myInt int
, @mySmallint smallint
, @myTinyint tinyint
, @myBit bit
, @myDecimal decimal(10, 7)
, @myNumeric numeric(7, 4)
, @myMoney money
, @mySmallMoney smallmoney
, @myFloat float
, @myReal real
, @myDatetime datetime
, @mySmallDatetime smalldatetime
, @myChar char(4)
, @myVarchar varchar(10)
, @myText text
, @myNChar nchar(4)
, @myNVarchar nvarchar(10)
, @myNText ntext
, @myGuid uniqueidentifier
AS
SELECT 'BIGINT' "DataType", @myBigInt "Value" , LEN(@myBigInt) "Length" , DATALENGTH(@myBigInt) "Data Length"
SELECT 'INT' , @myInt , LEN(@myInt) , DATALENGTH(@myInt)
SELECT 'SMALLINT' , @mySmallint , LEN(@mySmallint) , DATALENGTH(@mySmallint)
SELECT 'TINYINT' , @myTinyint , LEN(@myTinyint) , DATALENGTH(@myTinyint)
SELECT 'BIT' , @myBit , LEN(@myBit) , DATALENGTH(@myBit)
SELECT 'DECIMAL' , @myDecimal , LEN(@myDecimal) , DATALENGTH(@myDecimal)
SELECT 'NUMERIC' , @myNumeric , LEN(@myNumeric) , DATALENGTH(@myNumeric)
SELECT 'MONEY' , @myMoney , LEN(CAST(@mySmallMoney as varchar)) , DATALENGTH(@myMoney)
SELECT 'SMALLMONEY' , @mySmallMoney , LEN(CAST(@mySmallMoney as varchar)) , DATALENGTH(@mySmallMoney)
SELECT 'FLOAT' , @myFloat , LEN(@myFloat) , DATALENGTH(@myFloat)
SELECT 'REAL' , @myReal , LEN(@myReal) , DATALENGTH(@myReal)
SELECT 'DATETIME' , @myDatetime , LEN(@myDatetime) , DATALENGTH(@myDatetime)
SELECT 'SMALLDATETIME' , @mySmallDatetime , LEN(@mySmallDatetime) , DATALENGTH(@mySmallDatetime)
SELECT 'CHAR' , @myChar , LEN(@myChar) , DATALENGTH(@myChar)
SELECT 'VARCHAR' , @myVarchar , LEN(@myVarchar) , DATALENGTH(@myVarchar)
SELECT 'TEXT' , '' , '' , DATALENGTH(@myText)
SELECT 'NCHAR' , @myNChar , LEN(@myNChar) , DATALENGTH(@myNChar)
SELECT 'NVARCHAR' , @myNVarchar , LEN(@myNVarchar) , DATALENGTH(@myNVarchar)
SELECT 'NTEXT' , '' , '' , DATALENGTH(@myNText)
SELECT 'GUID' , @myGuid , LEN(@myGuid) , DATALENGTH(@myGuid)
-- TODO: READTEXT should do this...
/*
, @myText "text"
, @myNText "ntext"
*/
RETURN(0)
Code:
ADO Parameter Test 3 / Multiple Recordset TesterA 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 "
"
%
