January 4th, 2011, 07:20 PM
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:
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!
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.commandtext = "sp_update_lastnum"
Set param = comm.Parameters
comm("projectid") = 1
ReturnError = cmdVerify.Parameters("currnum")
Out_1 = comm("currnum")
January 4th, 2011, 11:43 PM
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.
The man who doesn't read good books has no advantage over the man who can't read them.
January 5th, 2011, 12:05 AM
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.
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.
February 7th, 2011, 06:14 PM
I had a look at the support article on microsoft support site and bellow is how the parameter is set.
Are you names of your parameters in your code the same as the parameters in your stored procedure?
Set myParameter = Command.CreateParameter (Name, [Type], [Direction], [Size], [Value])
also if this will help, the note at the bottom said:
Hope that helps