Microsoft KB Archive/181199

= How To Determine How ADO Will Bind Parameters =

Article ID: 181199

Article Last Modified on 3/14/2005

-

APPLIES TO


 * Microsoft ActiveX Data Objects 1.0
 * Microsoft ActiveX Data Objects 1.5
 * Microsoft ActiveX Data Objects 2.0
 * Microsoft ActiveX Data Objects 2.1 Service Pack 2
 * Microsoft ActiveX Data Objects 2.5
 * Microsoft ActiveX Data Objects 2.6
 * Microsoft ActiveX Data Objects 2.7

-



This article was previously published under Q181199



SUMMARY
Sometimes it is desirable to determine how ADO will bind stored procedure parameters. This article provides a Visual Basic application that returns the parameter name, type, size, and direction from a chosen stored procedure.



MORE INFORMATION
The following project has a list box and a command button on the startup form. All results appear in the Debug window. Note that code is supplied to use either Microsoft SQL Server or Oracle databases. You must modify the connection string and comment or uncomment the lines that create the SQL string.

 Open a new project in Visual Basic. Form1 is created by default. Place a list box and a command button control on the new form. On the Project menu, select References and then select Microsoft ActiveX Data Objects.  Place the following code in the General Declarations section of Form1: Dim Conn As New ADODB.Connection Dim Rs As ADODB.Recordset

Private Sub Form_Load Command1.Caption = "Start" End Sub

Private Sub Command1_Click Dim strSQL As String Form1.MousePointer = vbHourglass

' Adjust connection parameters for your server. 'SQL Server Connection Conn.Open "MySQLServer", "sa", "" 'SQL Server Connection StrSQL = "SELECT Name FROM sysobjects WHERE Type = 'p'"

'Oracle Connection 'Conn.Open "MyOracle", "MyUid", "MyPassword" 'strSQL = "SELECT Distinct Name FROM user_source"

Set Rs = New ADODB.Recordset Rs.Open strSQL, Conn, adOpenStatic, adLockReadOnly

Do While Not Rs.EOF List1.AddItem Rs(0) Rs.MoveNext Loop

Form1.MousePointer = vbNormal

End Sub

Private Sub List1_Click

ListADOParameters (Trim(List1.Text))

End Sub

Sub ListADOParameters(strStoredProcedureName As String)

Dim Cmd As New ADODB.Command Dim P As ADODB.Parameter Dim i As Integer

Debug.Print "-Starting---" Set Cmd.ActiveConnection = Conn Cmd.CommandText = strStoredProcedureName Cmd.CommandType = adCmdStoredProc Cmd.Parameters.Refresh

For i = 0 To Cmd.Parameters.Count - 1 Set P = Cmd.Parameters(i)

'Returns a string like this: 'Parameter[0] is [INID] with ADO DataType adNumeric, 'Size is 0, Direction is Input.

Debug.Print "Parameter[" & i & "] is [" & P.Name _ & "] with ADO DataType " _ & GetDataTypeEnum(P.Type) & ", Size is " _ & P.Size & ", Direction is " & GetDirectionEnum(P.Direction)

Next i

End Sub

Function GetDirectionEnum(lngDirectionEnum As Long) As String Dim strReturn As String Select Case lngDirectionEnum Case 1: strReturn = "Input" Case 2: strReturn = "Output" Case 3: strReturn = "Input Output" Case 4: strReturn = "Return Value" Case Else: strReturn = "Unknown DirectionEnum of " & lngDirectionEnum _ & " found." End Select GetDirectionEnum = strReturn

End Function

Function GetDataTypeEnum(lngDataTypeEnum As Long) As String 'Given ADO data-type constant, returns readable constant name. Dim strReturn As String Select Case lngDataTypeEnum Case 0: strReturn = "adEmpty" Case 16: strReturn = "adTinyInt" Case 2: strReturn = "adSmallInt" Case 3: strReturn = "adInteger" Case 20: strReturn = "adBigInt" Case 17: strReturn = "adUnsignedTinyInt" Case 18: strReturn = "adUnsignedSmallInt" Case 19: strReturn = "adUnsignedInt" Case 21: strReturn = "adUnsignedBigInt" Case 4: strReturn = "adSingle" Case 5: strReturn = "adDouble" Case 6: strReturn = "adCurrency" Case 14: strReturn = "adDecimal" Case 131: strReturn = "adNumeric" Case 11: strReturn = "adBoolean" Case 10: strReturn = "adError" Case 132: strReturn = "adUserDefined" Case 12: strReturn = "adVariant" Case 9: strReturn = "adIDispatch" Case 13: strReturn = "adIUnknown" Case 72: strReturn = "adGUID" Case 7: strReturn = "adDate" Case 133: strReturn = "adDBDate" Case 134: strReturn = "adDBTime" Case 135: strReturn = "adDBTimeStamp" Case 8: strReturn = "adBSTR" Case 129: strReturn = "adChar" Case 200: strReturn = "adVarChar" Case 201: strReturn = "adLongVarChar" Case 130: strReturn = "adWChar" Case 202: strReturn = "adVarWChar" Case 203: strReturn = "adLongVarWChar" Case 128: strReturn = "adBinary" Case 204: strReturn = "adVarBinary" Case 205: strReturn = "adLongVarBinary" Case Else: strReturn = "Unknown DataTypeEnum of " & lngDataTypeEnum _ & " found." End Select GetDataTypeEnum = strReturn End Function 

Run the project and select a stored procedure from the list. The results appear in the Debug window.

