ASP

本类阅读TOP10

·asp常用数据库连接方法和技巧
·无组件生成BMP验证码
·一些常用的辅助代码 (网络收藏)
·JavaScript实现的数据表格:冻结列、调整列宽和客户端排序
·VisualStudio.NET_2003及其 MSDN 下载地址
·ASP模拟MVC模式编程
·图片以二进制流输出到网页
·MD5加密算法 ASP版
·ASP.NET编程中的十大技巧
·改进 ASP 的字符串处理性能

分类导航
VC语言Delphi
VB语言ASP
PerlJava
Script数据库
其他语言游戏开发
文件格式网站制作
软件工程.NET开发
ADO中sqlserver存储过程使用

作者:未知 来源:月光软件站 加入时间:2005-2-28 月光软件站

从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:<!--#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 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 "<br />" .Write "Return Value: " & ret & "<br /><br />" .Write "<pre>" & proc & "</pre>" End With displayAspFile Server.MapPath("adodb.command3.asp") Response.Write "</div></body></html>" %>



相关文章

相关软件