Visual Basic Programming Code Examples

Returning parameter information from a stored procedure The following function can be used to give information on the parameters required to use a command to execute a stored procedure. 'Purpose : A debug procedure which returns parameter information about a stored procedure. ' Useful when you have difficulties using commands and parameters for the first time. 'Inputs : sStoredProcName The name of the stored procedure you want to return the ' parameter information from. ' oCon An open ADO connection to hte database where the SP resides 'Outputs : Returns debug.print results 'Notes : This function uses UBound with an error handler and allows you to specify a default return value 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 oCmd.ActiveConnection = oCon oCmd.CommandText = sStoredProcName oCmd.CommandType = adCmdStoredProc oCmd.Parameters.Refresh For lThisParam = 0 To oCmd.Parameters.Count - 1 Set oParam = oCmd.Parameters(lThisParam) Debug.Print "Parameter[" & lThisParam & "] is [" & oParam.Name & "] with ADO DataType " & GetDataTypeEnum(oParam.Type) & ", Size is " & oParam.Size & ", Precision is " & oParam.Precision & ", NumericScale is " & oParam.NumericScale & ", Direction is " & GetDirectionEnum(oParam.Direction) Next Set oParam = Nothing Set oCmd = 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 'Demonstration routine Sub Test() Dim oConn As ADODB.Connection Dim sConnectionString As String Set oConn = New ADODB.Connection sConnectionString = "Provider=SQLOLEDB.1;Password=xxxx;User ID=xxxxx;Data Source=xxxxx" oConn.Open sConnectionString ADOStoredProcGetParameters "Authenticate_User", oConn End Sub