VB6.0 调用存储过程的例子（方法二） 类型：转载                     VB6.0 调用存储过程的例子（方法二）      本人推荐使用方法一来取存储过程，当然前提是知道将要使用的参数化命令的详细情况，通过在代码中创建参数，其执行的速度快。 如果不知道要使用的参数化命令，本人整理了一份通过使用参数（Parameters）对象来获取存储过程的记录集的内容，但该执行方式速度没有方法一理想。 代码整理如下，你可以直接将该代码Copy到Form1窗体中进行调试。 其中函数GetDataType可以修改为自己所需的处理方式，在这里所有的代码都是为了测试方便所有，你也可以改为自己所需的相应处理。 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 strConnect As String     Dim strFieldName As String             Dim i As Integer         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"                 .Parameters.Refresh         ' 指定ADO实际地与数据源相连     End With         ' 通过Parameters对象，填充输入参数     For Each ADOPrm In ADOCmd.Parameters         If ADOPrm.Direction = adParamInput Then             ErrDataType:             On Error Resume Next             ADOPrm.Value = InputBox("存储过程参数名称:" & ADOPrm.Name & vbCrLf & _                 "该参数数据类型:" & GetDataType(ADOPrm.Type), "请输入参数值", "")             If Err <> 0 Then                 If MsgBox("所输入的参数与该参数数据类型不符，请重新输入！取消将退出存储过程的调用！", vbOKCancel, "警告") = vbCancel Then                     Exit Sub                 End If                 Err.Clear                 GoTo ErrDataType             End If             On Error GoTo 0                     End If     Next         On Error GoTo ErrHandler     Set ADORs = ADOCmd.Execute         If Not (ADORs Is Nothing) Then         If Not ADORs.EOF Then             Do Until ADORs.EOF                 For i = 0 To ADORs.Fields.Count - 1                     strFieldName = ADORs.Fields(i).Name                     Debug.Print "" & ADORs(strFieldName) & Space(4)                 Next                 Debug.Print                                 ADORs.MoveNext             Loop         End If     End If     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         MsgBox strError     Next     objCon.Errors.Clear End Sub Function GetDataType(ByRef DataType As DataTypeEnum) As String     Select Case DataType         Case DataTypeEnum.adArray             GetDataType = "DataTypeEnum.adArray"         Case DataTypeEnum.adBigInt             GetDataType = "DataTypeEnum.adBigInt"         Case DataTypeEnum.adBinary             GetDataType = "DataTypeEnum.adBinary"         Case DataTypeEnum.adBoolean             GetDataType = "DataTypeEnum.adBoolean"         Case DataTypeEnum.adBSTR             GetDataType = "DataTypeEnum.adBSTR"         Case DataTypeEnum.adChapter             GetDataType = "DataTypeEnum.adChapter"         Case DataTypeEnum.adChar             GetDataType = "DataTypeEnum.adChar"         Case DataTypeEnum.adCurrency             GetDataType = "DataTypeEnum.adCurrency"         Case DataTypeEnum.adDate             GetDataType = "DataTypeEnum.adDate"         Case DataTypeEnum.adDBDate             GetDataType = "DataTypeEnum.adDBDate"         Case DataTypeEnum.adDBTime             GetDataType = "DataTypeEnum.adDBTime"         Case DataTypeEnum.adDBTimeStamp             GetDataType = "DataTypeEnum.adDBTimeStamp"         Case DataTypeEnum.adDecimal             GetDataType = "DataTypeEnum.adDecimal"         Case DataTypeEnum.adDouble             GetDataType = "DataTypeEnum.adDouble"         Case DataTypeEnum.adEmpty             GetDataType = "DataTypeEnum.adEmpty"         Case DataTypeEnum.adError             GetDataType = "DataTypeEnum.adError """         Case DataTypeEnum.adFileTime             GetDataType = "DataTypeEnum.adFileTime """         Case DataTypeEnum.adGUID             GetDataType = "DataTypeEnum.adGUID"         Case DataTypeEnum.adIDispatch             GetDataType = "DataTypeEnum.adIDispatch"         Case DataTypeEnum.adInteger             GetDataType = "DataTypeEnum.adInteger"         Case DataTypeEnum.adIUnknown             GetDataType = "DataTypeEnum.adIUnknown"         Case DataTypeEnum.adLongVarBinary             GetDataType = "DataTypeEnum.adLongVarBinary"         Case DataTypeEnum.adLongVarChar             GetDataType = "DataTypeEnum.adLongVarChar"         Case DataTypeEnum.adLongVarWChar             GetDataType = "DataTypeEnum.adLongVarWChar"         Case DataTypeEnum.adNumeric             GetDataType = "DataTypeEnum.adNumeric"         Case DataTypeEnum.adPropVariant             GetDataType = "DataTypeEnum.adPropVariant"         Case DataTypeEnum.adSingle             GetDataType = "DataTypeEnum.adSingle"         Case DataTypeEnum.adSmallInt             GetDataType = "DataTypeEnum.adSmallInt"         Case DataTypeEnum.adTinyInt             GetDataType = "DataTypeEnum.adTinyInt"         Case DataTypeEnum.adUnsignedBigInt             GetDataType = "DataTypeEnum.adUnsignedBigInt"         Case DataTypeEnum.adUnsignedInt             GetDataType = "DataTypeEnum.adUnsignedInt"         Case DataTypeEnum.adUnsignedSmallInt             GetDataType = "DataTypeEnum.adUnsignedSmallInt"         Case DataTypeEnum.adUnsignedTinyInt             GetDataType = "DataTypeEnum.adUnsignedTinyInt"         Case DataTypeEnum.adUserDefined             GetDataType = "DataTypeEnum.adUserDefined"         Case DataTypeEnum.adVarBinary             GetDataType = "DataTypeEnum.adVarBinary"         Case DataTypeEnum.adVarChar             GetDataType = "DataTypeEnum.adVarChar"         Case DataTypeEnum.adVariant             GetDataType = "DataTypeEnum.adVariant"         Case DataTypeEnum.adVarNumeric             GetDataType = "DataTypeEnum.adVarNumeric"         Case DataTypeEnum.adVarWChar             GetDataType = "DataTypeEnum.adVarWChar"         Case DataTypeEnum.adWChar             GetDataType = "DataTypeEnum.adWChar"         Case Else             GetDataType = "无法获取数据类型"     End Select End Function

评论