DevX Home    Today's Headlines   Articles Archive   Tip Bank   Forums   

Results 1 to 2 of 2

Thread: ADOX problem

  1. #1
    Yoel Martinez Guest

    ADOX problem

    I'm trying to get information about a stored procedure using the ADOX.Procedures
    collection, every thing is fine if I use an Access DB but when i change the
    connection to SQL and try to get a reference to an specific command an "No
    such an interface defined" error ocurr.
    Please If any body have an idea of what the problem is let me know.

  2. #2
    Igor Guest

    Re: ADOX problem

    "Yoel Martinez" <> wrote:
    Hi Yoel,
    First you check out this article on the web:
    that explains how to retrieve stored procedures parameters using ADOX in
    ASP. It was very helpful to me, but I would like to warn you about something.
    You need to create connection string for the active connection to SQL Server,
    and the author of the article does it like this:

    connStr = "Driver={SQL Server}; Server=" & serverName & "; " & _
    "Database=" & dbName & "; UID=" & sqlLogin & "; PWD=" & sqlPwd

    pay attention to the DRIVER value. When I tried to use “Provider=SQLOLEDB;”
    instead I’ve got an error (don’t remember which one), but with “Driver={SQL
    Server};” everything worked fine.
    If you have trouble reading VBScript code this is simplified VB version
    of what is in that article:

    Private Sub RetrieveParametersFromSP()
    Dim oCat As New ADOX.Catalog
    Dim oCmd As New ADODB.Command
    Dim strConn As String
    Dim strServerName As String
    Dim strDBName As String
    Dim strUID As String
    Dim strPWD As String
    Dim strSPName As String
    Dim oProc As Procedure
    Dim oPar As Parameter

    strServerName = "ProductionSQLServer"
    strDBName = "Test"
    strUID = "sa"
    strPWD = "123456"

    strConn = "Driver={SQL Server}; Server=" & strServerName & "; " & _
    "Database=" & strDBName & "; UID=" & strUID & "; PWD=" & strPWD & ";"
    ' connecting to SQL Server
    oCat.ActiveConnection = strConn
    '1. retrieving all stored procedures available in this Database
    ' and populating some List Box with stored procedure's names
    For Each oProc In oCat.Procedures
    lstStoredProcNames.AddItem oProc.Name
    Next oProc

    ' 2. Then imagine that you know the name of the Stored procedure an it
    is stored in strSPName
    ' retrieve all the parameters of that stored procedure
    oCmd.ActiveConnection = strConn
    oCmd.CommandType = adCmdStoredProc
    oCmd.CommandText = strSPName
    oCmd.Parameters.Refresh ' this will retrieve information about parameters

    For Each oPar In oCmd.Parameters
    Debug.Print oPar.Name
    ' converting parameter's direction constatn which is integer to
    ' "spelled out" version
    Select Case oPar.Direction
    Case adParamUnknown
    Debug.Print "adParamUnknown"
    Case adParamInput
    Debug.Print "adParamInput"
    Case adParamOutput
    Debug.Print "adParamOutput"
    Case adParamInputOutput
    Debug.Print "adParamInputOutput"
    Case adParamReturnValue
    Debug.Print "adParamReturnValue"
    End Select
    ' converting the type of the parameter
    Select Case oPar.Type
    Case adBigInt
    Debug.Print "adBigInt"
    Case adBinary
    Debug.Print "adBinary"
    Case adBoolean
    Debug.Print "adBoolean"
    Case adBSTR
    Debug.Print "adBSTR"
    Case adChapter
    Debug.Print "adChapter"
    Case adChar
    Debug.Print "adChar"
    Case adCurrency
    Debug.Print "adCurrency"
    Case adDate
    Debug.Print "adDate"
    Case adDBDate
    Debug.Print "adDBDate"
    Case adDBFileTime
    Debug.Print "adDBFileTime"
    Case adDBTime
    Debug.Print "adDBTime"
    Case adDBTimeStamp
    Debug.Print "adDBTimeStamp"
    Case adDecimal
    Debug.Print "adDecimal"
    Case adDouble
    Debug.Print "adDouble"
    Case adEmpty
    Debug.Print "adEmpty"
    Case adError
    Debug.Print "adError"
    Case adFileTime
    Debug.Print "adFileTime"
    Case adGUID
    Debug.Print "adGUID"
    Case adIDispatch
    Debug.Print "adIDispatch"
    Case adInteger
    Debug.Print "adInteger"
    Case adIUnknown
    Debug.Print "adIUnknown"
    Case adLongVarBinary
    Debug.Print "adLongVarBinary"
    Case adLongVarChar
    Debug.Print "adLongVarChar"
    Case adLongVarWChar
    Debug.Print "adLongVarWChar"
    Case adNumeric
    Debug.Print "adNumeric"
    Case adPropVariant
    Debug.Print "adPropVariant"
    Case adSingle
    Debug.Print "adSingle"
    Case adSmallInt
    Debug.Print "adSmallInt"
    Case adTinyInt
    Debug.Print "adTinyInt"
    Case adUnsignedBigInt
    Debug.Print "adUnsignedBigInt"
    Case adUnsignedInt
    Debug.Print "adUnsignedInt"
    Case adUnsignedSmallInt
    Debug.Print "adUnsignedSmallInt"
    Case adUnsignedTinyInt
    Debug.Print "adUnsignedTinyInt"
    Case adUserDefined
    Debug.Print "adUserDefined"
    Case adVarBinary
    Debug.Print "adVarBinary"
    Case adVarChar
    Debug.Print "adVarChar"
    Case adVariant
    Debug.Print "adVariant"
    Case adVarNumeric
    Debug.Print "adVarNumeric"
    Case adVarWChar
    Debug.Print "adVarWChar"
    Case adWChar
    Debug.Print "adWChar"
    Case Else
    Debug.Print "other"
    End Select

    Debug.Print oPar.Size
    Debug.Print "------------------------"
    Next oPar
    Set oCat = Nothing
    Set oCmd = Nothing
    End Sub
    Try it out and see how it goes.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
HTML5 Development Center
Latest Articles
Questions? Contact us.
Web Development
Latest Tips
Open Source

   Development Centers

   -- Android Development Center
   -- Cloud Development Project Center
   -- HTML5 Development Center
   -- Windows Mobile Development Center