从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:<![CDATA[<!--#include virtual="/testsite/global_include.asp" -->
<%
dim conn as adodb.connection
dim cmd as adodb.command
dim prm as adodb.parameter
dim rs as adodb.recordset
dim ret as long
dim proc as string
dim alldata() as variant
dim colnames() as variant
dim i as long
dim datetime as datetime
const storedprocedure = "[dbo].[datatypetester]"
const titlestring = "<html><head><title>ado parameter test 3 / multiple recordset tester</title><link rel=""stylesheet"" href=""/templates/style.css"" type=""text/css"" /></head><body><div align=""left""><h3>a example of how to retrieve multiple recordsets from ado and how to set parameters in ado for sql server stored procedures</h3>"
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 doesnt 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 "<br />"
.write "return value: " & ret & "<br /><br />"
.write "<pre>" & proc & "</pre>"
end with
displayaspfile server.mappath("adodb.command3.asp")
response.write "</body></html>"
%>]]>
|
.text:''):(d.getSelection?d.getSelection():'');void(keyit=window.open('http://my.poco.cn/fav/storeIt.php?t='+escape(d.title)+'&u='+escape(d.location.href)+'&c='+escape(t)+'&img=http://www.h-strong.com/blog/logo.gif','keyit','scrollbars=no,width=475,height=575,status=no,resizable=yes'));keyit.focus(); "添加到POCO网摘"){kind=logo}
Rss Feed
