VB and VBA Users Source Code: Returning parameter information for a stored procedure (ADO)
[
Home
|
Contents
|
Search
|
Reply
| Previous | Next ]
VB/VBA Source Code
Returning parameter information for a stored procedure (ADO)
By:
Andrew Baker
Email (spam proof):
Email the originator of this post
Date:
Wednesday, September 17, 2003
Hits:
1173
Category:
Database/SQL/ADO
Article:
The following routine is useful when trying to use commands to execute stored procedures. The function will print parameter information for a specified stored procedure. Function ADOStoredProcGetParameters(sStoredProcName As String, oCon As ADODB.Connection) Dim oCmd As New ADODB.Command Dim oParam As ADODB.Parameter Dim lThisParam As Long Set Cmd.ActiveConnection = oCon Cmd.CommandText = sStoredProcName Cmd.CommandType = adCmdStoredProc Cmd.Parameters.Refresh For lThisParam = 0 To Cmd.Parameters.Count - 1 Set oParam = Cmd.Parameters(lThisParam) Debug.Print "Parameter[" & lThisParam & "] is [" & oParam.Name & "] with ADO DataType " & GetDataTypeEnum(oParam.Type) & ", Size is " & oParam.Size & ", Direction is " & GetDirectionEnum(oParam.Direction) Next Set oParam = Nothing End Function 'Given ADO direction-type constant, returns readable direction name. Private Function GetDirectionEnum(lDirectionEnum As Long) As String Dim sReturn As String Select Case lDirectionEnum Case 1: sReturn = "Input" Case 2: sReturn = "Output" Case 3: sReturn = "Input Output" Case 4: sReturn = "Return Value" Case Else: sReturn = "Unknown DirectionEnum of " & lDirectionEnum & " found." End Select GetDirectionEnum = sReturn End Function 'Given ADO data-type constant, returns readable constant name. Private Function GetDataTypeEnum(lngDataTypeEnum As Long) As String Dim sReturn As String Select Case lngDataTypeEnum Case 0: sReturn = "adEmpty" Case 16: sReturn = "adTinyInt" Case 2: sReturn = "adSmallInt" Case 3: sReturn = "adInteger" Case 20: sReturn = "adBigInt" Case 17: sReturn = "adUnsignedTinyInt" Case 18: sReturn = "adUnsignedSmallInt" Case 19: sReturn = "adUnsignedInt" Case 21: sReturn = "adUnsignedBigInt" Case 4: sReturn = "adSingle" Case 5: sReturn = "adDouble" Case 6: sReturn = "adCurrency" Case 14: sReturn = "adDecimal" Case 131: sReturn = "adNumeric" Case 11: sReturn = "adBoolean" Case 10: sReturn = "adError" Case 132: sReturn = "adUserDefined" Case 12: sReturn = "adVariant" Case 9: sReturn = "adIDispatch" Case 13: sReturn = "adIUnknown" Case 72: sReturn = "adGUID" Case 7: sReturn = "adDate" Case 133: sReturn = "adDBDate" Case 134: sReturn = "adDBTime" Case 135: sReturn = "adDBTimeStamp" Case 8: sReturn = "adBSTR" Case 129: sReturn = "adChar" Case 200: sReturn = "adVarChar" Case 201: sReturn = "adLongVarChar" Case 130: sReturn = "adWChar" Case 202: sReturn = "adVarWChar" Case 203: sReturn = "adLongVarWChar" Case 128: sReturn = "adBinary" Case 204: sReturn = "adVarBinary" Case 205: sReturn = "adLongVarBinary" Case Else: sReturn = "Unknown DataTypeEnum of " & lngDataTypeEnum & " found." End Select GetDataTypeEnum = sReturn End Function
Terms and Conditions
Support this site
Download a trial version of the Excel Workbook Rebuilder
