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

评论