#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2011
    Posts
    2
    Rep Power
    0

    Run oracle stored procedure


    I am trying to run an oracle stored proc from ASP. It passes 1 integer parameter and returns 1 integer parameter. The code is copied below.

    I get an error popping up - "Item cannot be found in the collection corresponding to the requested name or ordinal."

    The error handling code in ASP says:
    3704
    ADODB.Connection
    Operation is not allowed when the object is closed.

    The stored proc works just fine when I execute if from SQL Plus or Toad.

    I get this error when I call ANY stored proc, even ones that don't exist. So I am a bit confused.

    What am I missing? I've googled this and tried everything I can think of. Any pointers for further troubleshooting will be appreciated!


    Code:
    Set conn = Server.CreateObject("ADODB.Connection")
    conn.Open "Provider=MSDAORA.1;Data Source=lldev;User ID=theuser;Password=thepassword"
    
    Set comm = Server.CreateObject("ADODB.Command")
    Set comm.ActiveConnection = conn
    comm.commandtype=adCmdStoredProc
    comm.commandtext = "sp_update_lastnum"
    
    Set param = comm.Parameters
    param.append comm.createparameter("projectid",adInteger,adParamInput)
    param.append comm.createparameter("currnum",adInteger,adParamOutput)
    
    comm("projectid") = 1
    comm.execute
    ReturnError = cmdVerify.Parameters("currnum")
    Out_1 = comm("currnum")
    Thanks,
    Srini
  2. #2
  3. No Profile Picture
    Grumpier old Moderator
    Devshed Supreme Being (6500+ posts)

    Join Date
    Jun 2003
    Posts
    14,445
    Rep Power
    4539
    ADODB.Connection
    Operation is not allowed when the object is closed.
    Look for some on error resume next code that might be hiding an error with your db connections. The message above indicates your db connection did not open successfully.
    ======
    Doug G
    ======
    Bartender to Rene Descartes "have another beer?" Descartes: "I think not" and he vanished.
    --Alfred Bester
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2011
    Posts
    2
    Rep Power
    0
    Thanks for the reply! You are right, I was able to change the connection string and get the connection working but now I get a different error.

    438
    Microsoft VBScript runtime error
    Object doesn't support this property or method

    And the ASP error handler messages are:
    invalid ROWID ORA-06512: at LIVELINKUSR.SP_UPDATE_LASTNUM", line 20 ORA-06512: at line 1

    When I do a response.write(comm.Parameters) it doesn't output anything so that suggests to me that somehow the parameter definition is not working right?

    I don't get any errors when I execute this stored proc directly from sql plus or TOAD.
  6. #4
  7. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Apr 2005
    Location
    Dundee, Scotland
    Posts
    5
    Rep Power
    0
    Hi,

    I had a look at the support article on microsoft support site and bellow is how the parameter is set.

    Code:
    Set myParameter = Command.CreateParameter (Name, [Type], [Direction], [Size], [Value])
    Are you names of your parameters in your code the same as the parameters in your stored procedure?

    also if this will help, the note at the bottom said:

    NOTE: It can be difficult to determine how to properly call a stored procedure if you are unaware of the stored procedure's parameter information. Without the correct information, you cannot properly create the ADO parameters. You can use the Refresh method of the Parameter object to populate the Parameters collection automatically, based on the stored procedure's definition on the server.
    For example:
    Command.Parameters.Refresh

    Hope that helps

    regards


    Ribs

IMN logo majestic logo threadwatch logo seochat tools logo