How To Determine How ADO Will Bind Parameters (181199)

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.

1. Open a new project in Visual Basic. 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 
   					

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