VB6.0 调用存储过程的例子（方法一）
类型：转载

                VB6.0 调用存储过程的例子（方法一）


            (说明:以下代码摘自微软的MSDN,经过测试没问题。使用该方法的前提条件是必须知道将要使用的参数化命令的详细情况。)



打开Form1窗体，Copy以下的代码到窗体中，该段代码将测试存储过程ADOTestRPE的返回值、输入参数及输出参数，测试的过程中，可能需要修改链接字符串。



Sub CreateParms()

    Dim ADOCmd As New ADODB.Command

    Dim ADOPrm As New ADODB.Parameter

    Dim ADOCon As ADODB.Connection

    Dim ADORs As ADODB.Recordset

    Dim sParmName As String

    Dim strConnect As String

    Dim rStr As String

   

    On Error GoTo ErrHandler

   

    strConnect = "driver={SQL Server};server=(local);uid=sa;pwd=;database=pubs"

   

    Set ADOCon = New ADODB.Connection

    With ADOCon

        .Provider = "MSDASQL"

        .CursorLocation = adUseServer  'Must use Server side cursor.

        .ConnectionString = strConnect

        .Open

    End With

   

    Set ADOCmd.ActiveConnection = ADOCon

    With ADOCmd

        .CommandType = adCmdStoredProc

        .CommandText = "ADOTestRPE"

    End With

   

    'Parameter 0 is the stored procedure Return code.

    sParmName = "Return"

    Set ADOPrm = ADOCmd.CreateParameter(sParmName, adInteger, _

      adParamReturnValue, , 0)

    ADOCmd.Parameters.Append ADOPrm

    ADOCmd.Parameters(sParmName).Value = -1

   

    'Parameter 1 is the setting for the stored procedure Output

    ' parameter.

    sParmName = "Output"

    Set ADOPrm = ADOCmd.CreateParameter(sParmName, adInteger, _

      adParamOutput)

    ADOCmd.Parameters.Append ADOPrm

    ADOCmd.Parameters(sParmName).Value = 999

   

    'Parameter 2

    sParmName = "R1Num"     'Number of rows to return in Resultset 1.

    Set ADOPrm = ADOCmd.CreateParameter(sParmName, adInteger, _

      adParamInput)

    ADOCmd.Parameters.Append ADOPrm

    ADOCmd.Parameters(sParmName).Value = 1

   

    'Parameter 3

    sParmName = "P1Num"     'Number of PRINT statements in Resultset 1.

    Set ADOPrm = ADOCmd.CreateParameter(sParmName, adInteger, _

     adParamInput)

    ADOCmd.Parameters.Append ADOPrm

    ADOCmd.Parameters(sParmName).Value = 0

   

    'Parameter 4

    sParmName = "E1Num"     'Number of RAISERROR statements in Resultset

                            '1.

    Set ADOPrm = ADOCmd.CreateParameter(sParmName, adInteger, _

      adParamInput)

    ADOCmd.Parameters.Append ADOPrm

    ADOCmd.Parameters(sParmName).Value = 0

   

    'Parameter 5

    sParmName = "R2Num"     'Number of rows to return in Resultset 2.

    Set ADOPrm = ADOCmd.CreateParameter(sParmName, adInteger, _

      adParamInput)

    ADOCmd.Parameters.Append ADOPrm

    ADOCmd.Parameters(sParmName).Value = 2

   

    'Parameter 6

    sParmName = "P2Num"     'Number of PRINT statements in Resultset 2.

    Set ADOPrm = ADOCmd.CreateParameter(sParmName, adInteger, _

      adParamInput)

    ADOCmd.Parameters.Append ADOPrm

    ADOCmd.Parameters(sParmName).Value = 0

   

    'Parameter 7

    sParmName = "E2Num"     'Number of RAISERROR statements in Resultset

                            ' 2.

    Set ADOPrm = ADOCmd.CreateParameter(sParmName, adInteger, _

     adParamInput)

    ADOCmd.Parameters.Append ADOPrm

    ADOCmd.Parameters(sParmName).Value = 0

   

    Set ADORs = ADOCmd.Execute

   

    Do While (Not ADORs Is Nothing)

        If ADORs.State = adStateClosed Then Exit Do

        While Not ADORs.EOF

            For i = 0 To ADORs.Fields.Count - 1

                rStr = rStr & " : " & ADORs(i)

            Next i

            Debug.Print Mid(rStr, 3, Len(rStr))

            ADORs.MoveNext

            rStr = ""

        Wend

        Debug.Print "----------------------"

        Set ADORs = ADORs.NextRecordset

    Loop

   

    Debug.Print "Return: " & ADOCmd.Parameters("Return").Value

    Debug.Print "Output: " & ADOCmd.Parameters("Output").Value

   

    GoTo Shutdown



ErrHandler:

    Call ErrHandler(ADOCon)

    Resume Next



Shutdown:

    Set ADOCmd = Nothing

    Set ADOPrm = Nothing

    Set ADORs = Nothing

    Set ADOCon = Nothing

End Sub



Private Sub Command1_Click()

    Call CreateParms

End Sub



Sub ErrHandler(objCon As Object)

    Dim ADOErr As ADODB.Error

    Dim strError As String



    For Each ADOErr In objCon.Errors

        strError = "Error #" & ADOErr.Number & vbCrLf & ADOErr.Description _

            & vbCr & _

            "   (Source: " & ADOErr.Source & ")" & vbCr & _

            "   (SQL State: " & ADOErr.SQLState & ")" & vbCr & _

            "   (NativeError: " & ADOErr.NativeError & ")" & vbCr

        If ADOErr.HelpFile = "" Then

            strError = strError & "   No Help file available" & vbCr & vbCr

        Else

            strError = strError & "   (HelpFile: " & ADOErr.HelpFile & ")" _

                & vbCr & "   (HelpContext: " & ADOErr.HelpContext & ")" & _

                vbCr & vbCr

        End If

       

        Debug.Print strError

    Next



    objCon.Errors.Clear

End Sub

评论