HOWTO: Determine How ADO Will Bind Parameters

• ActiveX Data Objects (ADO), versions 1.0, 1.5, 2.0, 2.1 SP2

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 start up form. All results display 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.

1. Open a new project in Visual Basic 5.0. Form1 is created by default.



2. Place a list box and a command button control on the new form.



3. On the Project menu, select References and then select Microsoft ActiveX Data Objects.



4. 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  

© Microsoft Corporation 1999, All Rights Reserved.
Contributions by Ron Nelson, Microsoft Corporation

REFERENCES

For additional information, please see the following article in the Microsoft Knowledge Base:

Q175018 HOWTO: Acquire and Install the Microsoft Oracle ODBC Driver

Additional query words: stored procedures ado binding parameters

Keywords : kbADO kbADO150 kbADO200 kbDatabase kbSQLServ kbStoredProc kbGrpVBDB kbGrpMDAC kbDSupport kbADO210sp2 kbMDAC210SP2
Version : WINDOWS:1.0,1.5,2.0,2.1 SP2
Platform : WINDOWS
Issue type : kbhowto