在 Visual Basic 或從 ASP 的 Microsoft Access & VBScript VBA

最新推荐文章于 2025-09-11 20:53:34 发布

weixin_34290096

最新推荐文章于 2025-09-11 20:53:34 发布

阅读量67

点赞数

文章标签：数据库

本文演示了使用ADO连接到SQL Server数据库并执行存储过程的步骤。它包括建立连接、创建和删除存储过程、执行存储过程以及处理返回结果的过程。

 Dim Conn1 As ADODB.Connection
   Dim Cmd1 As ADODB.Command
   Dim Rs1 As ADODB.Recordset


   Dim strTmp As String


   Dim Connect As String
   Dim Drop As String
   Dim Create As String
   Dim sp as string


   Dim i As Integer
   Dim l As Long


   sConnect= "driver={sql server};" &amp; _


             "server=server_name;" &amp; _
             "Database=pubs;UID=uder_id;PWD=password;"


   sCreate = "create proc sp_AdoTest( @InParam int, " &amp; _


             "@OutParam int OUTPUT ) " &amp; _
             "as "  &amp; _
             "select @OutParam = @InParam + 10 " &amp; _
             "SELECT * FROM Authors WHERE "   &amp; _
             "State <> 'CA' "  &amp; _
             "return @OutParam +10"


   sDrop   = "if exists "  &amp; _


             "(select * from sysobjects where "  &amp; _
             "id = object_id('dbo.sp_AdoTest') and " &amp; _
             "sysstat &amp; 0xf = 4)"  &amp; _
             "drop procedure dbo.sp_AdoTest"


   sSP     = "sp_Adotest"


   ' Establish connection.
   Set Conn1 = New ADODB.Connection
   Conn1.ConnectionString = sConnect
   Conn1.Open


   ' Drop procedure, if it exists &amp; recreate it.
   Set Rs1 = Conn1.Execute(sDrop, l, adCmdText)
   Set Rs1 = Nothing


   Set Rs1 = Conn1.Execute(sCreate, l, adCmdText)
   Set Rs1 = Nothing


   ' Open recordset.
   Set Cmd1 = New ADODB.Command
   Cmd1.ActiveConnection = Conn1
   Cmd1.CommandText = "sp_AdoTest"
   Cmd1.CommandType = adCmdStoredProc
   Cmd1.Parameters.Refresh
   Cmd1.Parameters(1).Value = 10
   Set Rs1 = Cmd1.Execute()


   ' Process results from recordset, then close it.
   RS1.Close
   Set Rs1 = Nothing


   ' Get parameters (assumes you have a list box named List1).


   Debug.print vbTab &amp; "RetVal Param = " &amp; Cmd1.Parameters(0).Value
   Debug.print vbTab &amp; "Input  Param = " &amp; Cmd1.Parameters(1).Value
   Debug.print vbTab &amp; "Output Param = " &amp; Cmd1.Parameters(2).Value