No announcement yet.

Stored Procedure with Parameters Error

  • Filter
  • Time
  • Show
Clear All
new posts

  • Stored Procedure with Parameters Error

    I have been googling for 2 days and everything I tried has failed. I'm not new to vb, but am new to vba in excel. I'm trying to get a record set back from a system stored procedure on a SQL database.
    I am able to connect fine if running a SQL query string. The code is as follows....
    I'm getting an Run-time error '3001': Application-defined or object-defined error at the bolded line.
    If I comment this line out I also get one when trying to set the parameter in the next line. Weird thing is that setting the ActiveConnection and CommandText works just fine. Any ideas?

            Set myConn = CreateObject("ADODB.Connection")
            Set myTablesRS = CreateObject("ADODB.Recordset")
            Set myDataRS = CreateObject("ADODB.Recordset")
            Set myCommand = CreateObject("ADODB.Command")
            Set myParameter = CreateObject("ADODB.Parameter")
            myConnectionString = "Provider=SQLOLEDB.1;Data Source=XXXXX;Initial Catalog=XXXXX;User ID=XXXXX;password=XXXXX;"
            myConn.Open myConnectionString
            myCommand.ActiveConnection = myConn
            myCommand.CommandText = "sp_helpindex"
            myCommand.CommandType = adCmdStoredProc
            myCommand.Parameters.Append myCommand.CreateParameter("@objname", adNVarChar, adParamInput, 776, "F01")
            myDataRS = myCommand.Execute()
            myDataRS.Close         'gives error halfway through, so don't close
            Set myDataRS = Nothing
            Set myCommand.ActiveConnection = Nothing
            Set myCommand = Nothing

  • #2
    Re: Stored Procedure with Parameters Error

    It appears you are doing what is referred to as "late binding" meaning you don't have the necessary library included in the reference list (look under tools:references in the macro editor).

    If that's the case, Excel doesn't know what value adCmdStoredProc has. Try putting in a 4 which is what is defined in the command type enumeration for adCmdStoredProc.
    Old Programmers Never Die ... They just lose their bits


    • #3
      Re: Stored Procedure with Parameters Error

      Haha! You are correct. I put in actual numeric values like you said and it worked!
      Thanks for the help!!

      For anybody else who finds this it means...

      myCommand.CommandType = 4

      instead of

      myCommand.CommandType = adCmdStoredProc


      • #4

        Re: Stored Procedure with Parameters Error

        IMO, it's preferable to declare the constant yourself:
        Const adCmdStoredProc as Long = 4
        which would make the code clearer.
        Theory is when you know something, but it doesnít work. Practice is when something works, but you donít know why. Programmers combine theory and practice: nothing works and they donít know why