调用参数存储过程

时　间:2018-06-27 10:52:47
作　者:萤火虫   ID：66752  城市：大理
摘　要:调用参数存储过程两种方法。
正　文:

具体示例代码如下：

第一种方法：

Public Sub Ex_Parameter()
    Dim cnnDB As New ADODB.Connection
    Dim cmdObject As New ADODB.Command
    Dim parInput1 As ADODB.Parameter
    Dim parInput2 As ADODB.Parameter
    Dim parOutput As ADODB.Parameter
    Set cnnDB = CurrentProject.Connection    
    cmdObject.ActiveConnection = cnnDB
    cmdObject.CommandText = "订单日期笔数查询SP"
    cmdObject.CommandType = adCmdStoredProc
    Set parInput1 = cmdObject.CreateParameter _
                    ("Para1", adDate, adParamInput, , "2018/6/26")                              
    cmdObject.Parameters.Append parInput1
    cmdObject.Execute
    Debug.Print "从" & parInput1.Value & "到" & parInput2.Value & _
                "共有" & parOutput.Value & "笔订单"
    cnnDB.Close 
    Set cnnDB = Nothing
End Sub

第二种方法：

Public Sub Ex_Parameter()
    Dim cnnDB As New ADODB.Connection
    Dim cmdObject As New ADODB.Command
    Dim parInput1 As ADODB.Parameter
    Dim parInput2 As ADODB.Parameter
    Dim parOutput As ADODB.Parameter
    Set cnnDB = CurrentProject.Connection    
    cmdObject.ActiveConnection = cnnDB
    cmdObject.CommandText = "订单日期笔数查询SP"
    cmdObject.CommandType = adCmdStoredProc         
    Set parInput2 = cmdObject.CreateParameter
    parInput2.Name = "Para2" 
    parInput2.Direction = adParamInput
    parInput2.Type = adDate 
    parInput2.Value = "2018/6/27"                               
    cmdObject.Parameters.Append parInput2
    Set parOutput = cmdObject.CreateParameter _
                    ("RowCount", adInteger, adParamOutput)                             
    cmdObject.Parameters.Append parOutput
    cmdObject.Execute
    Debug.Print "从" & parInput1.Value & "到" & parInput2.Value & _
                "共有" & parOutput.Value & "笔订单"
    cnnDB.Close 
    Set cnnDB = Nothing
End Sub