Re: Item cannot be found in the collection corresponding to the requested name or ordinal.
- From: "tony B" <anthonybrough@xxxxxxxxxxxxxx>
- Date: 27 Sep 2006 15:37:40 -0700
Bob Barrows [MVP] wrote:
anthonybrough@xxxxxxxxxxxxxx wrote:
I am trying to get a value from a RETURNING statement on an OracleI'm a little hesitant to reply, never having used Oracle, but in SQL Server,
database and write the result on the same ASP page, but I am getting
the error:
Error Type:
ADODB.Recordset (0x800A0CC1)
Item cannot be found in the collection corresponding to the requested
name or ordinal.
<<<>
This the code that I am using,
conn.beginTrans
sql = "INSERT INTO tbltest(REGID) Values (tbltest_SEQ.nextval )"
conn.execute SQL
sql = "Insert into tblreg_Account (REGID, Account1) Values
(tbltest_SEQ.currval, '" &strAccount &"')RETURNING REGID into
return_REGID "set r = conn.execute (sql)
response.write r.fields("return_REGID").value
conn.commitTrans
If I leave out :
set r = conn.execute (sql)
response.write r.fields("return_REGID").value
the code executes no problem.
I would be grateful of any pointers in how to resolve.
RETURN values are returned via parameters, not via resultsets. I suspect
it's the same way with Oracle. In order to get the return parameter value,
you have to use a Command object. Try:
const adInteger=3
const adParamReturnValue=4
set cmd=createobject("adodb.command")
cmd.commandtext=sql
cmd.commandtype=1
set cmd.activeconnection=conn
cmd.parameters.append cmd.createparameter("Return", _
adInteger,adParamReturnValue)
cmd.execute
response.write cmd.parameters(0).value
The ADO documentation can be found here.
http://msdn.microsoft.com/library/en-us/ado270/htm/mdmscadoapireference.asp
You should look up the methods I used above to become familiar with them.
--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"
Hi Bob
Thanks for the reply.
I am new to oracle but have used a SQL Server more recently, bur
unfortunately i didnt have a choice in the selection of the database
:-(
I was hoping that I could accomplish this without a Stored Proc, but i
will take your advice and revert to that method.
Thanks again for the reply.
Regards
Tony
.
- Follow-Ups:
- Re: Item cannot be found in the collection corresponding to the requested name or ordinal.
- From: Bob Barrows [MVP]
- Re: Item cannot be found in the collection corresponding to the requested name or ordinal.
- References:
- Item cannot be found in the collection corresponding to the requested name or ordinal.
- From: anthonybrough
- Re: Item cannot be found in the collection corresponding to the requested name or ordinal.
- From: Bob Barrows [MVP]
- Item cannot be found in the collection corresponding to the requested name or ordinal.
- Prev by Date: Re: Item cannot be found in the collection corresponding to the requested name or ordinal.
- Next by Date: Re: Item cannot be found in the collection corresponding to the requested name or ordinal.
- Previous by thread: Re: Item cannot be found in the collection corresponding to the requested name or ordinal.
- Next by thread: Re: Item cannot be found in the collection corresponding to the requested name or ordinal.
- Index(es):
Relevant Pages
|
|
