VB中运用PARAMETER调用存储过程的一段代码

    Dim cn As ADODB.Connection
    Dim rsAdo As ADODB.Recordset
    Dim cmdAdo As ADODB.Command
    Dim prmAdo As ADODB.Parameter
    dim strParaName as string
   
    Set cn = New ADODB.Connection
    Set cmdAdo = New ADODB.Command

On Error GoTo errhandle
   
    With cn
        .ConnectionString =strCn
        .CursorLocation = adUseClient
        .ConnectionTimeout = 30
        .Open
    End With
   
    Set cmdAdo.ActiveConnection = cn
    With cmdAdo
        .CommandType = adCmdStoredProc
        .CommandText = "proGetCurNum"
    End With

    strParaName="input"

    Set prmAdo = cmdAdo.CreateParameter(strParaName, adDBDate, adParamInput)
    cmdAdo.Parameters.Append prmAdo
    cmdAdo.Parameters(strParaName).Value = date()

    Set prmAdo = cmdAdo.CreateParameter(strParaName, adDBDate, adParamInput)
    cmdAdo.Parameters.Append prmAdo
    cmdAdo.Parameters(strParaName).Value = DateAdd("d", 1, date())

    Set prmAdo = cmdAdo.CreateParameter(strParaName, adSmallInt, adParamInput)
    cmdAdo.Parameters.Append prmAdo
    cmdAdo.Parameters(strParaName).Value = lngWorkNo
    Set rsAdo = cmdAdo.Execute

    If rsAdo Is Nothing Then Exit Sub
    If rsAdo.State = adStateClosed Then Exit Sub   
    If Not rsAdo.EOF Then
    .....

     cn.Close
exitLine:
    Set cmdWork = Nothing
    Set prmAdo = Nothing
    Set rsWorkMax = Nothing
    Set cn = Nothing
    ......
   

---

2、ADO：

①声明ADODB.Command及初始化

Private lAdcom_IsOk As ADODB.Command

Set lAdcom_IsOk = New ADODB.Command

 

②设置ActiveConnection、CommandText、CommandType属性

With lAdcom_IsOk

    .ActiveConnection= gAdcon_fin

    .CommandText = "sp_e_isok"

    .CommandType = adCmdStoredProc

 

③通过Parameters.Refresh产生参数，并加工参数

    .Parameters.Refresh

‘对于Sql Server如果无需存储返回参数需删除第一个参数

    .Parameters.Delete (0)

‘对于Oracle需设置每个参数的Size属性

.Parameters(0).Size=4

.Parameters(1).Size=50

 

④通过Parameter传入参数

    .Parameters(0) = 2

    .Parameters(1) = Trim(Txt_cid.Text)

 

⑤Execute执行查询

    .Execute

End With