469,275 Members | 1,649 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,275 developers. It's quick & easy.

ADODB.Recordset (0x800A0CC1) Error

Joe
I want to add a new record to database, and then retrives the @@IDENTITY value for this ne
added record, and the fields of this record. However, it has the following error on
line varNewID = objRS("NewMsgID"

ADODB.Recordset (0x800A0CC1
Item cannot be found in the collection corresponding to the requested name or ordinal

sqlStmt= "INSERT INTO AddressValidation "
& "(RequestXML, ResponseXML) "
& "VALUES ("
& "'" & strRequestXML & "'" & ","
& "'" & strResponseXML & "'" & ");

sqlStmt = sqlStmt & "SELECT @@IDENTITY AS NewMsgID;

Set objConn=Server.CreateObject("ADODB.Connection"
objConn.Open strConnec
Set objRS = objConn.Execute(sqlStmt
varNewID = objRS("NewMsgID") '<============= ERROR HERE!!
Response.Write "varNewID = " & varNewID & "<BR>
sqlStmt = sqlStmt & "SELECT * FROM AddressValidation WHERE AddressValidationID = " & "'" & varNewID & "'" & ";
Set objRS = objConn.Execute(sqlStmt
varXMLRequest = objRS("RequestXML"
varXMLRequest = objRS("ResponseXML"
Response.Write "varXMLRequest = " & varXMLRequest & "<BR>
Response.Write "varXMLRequest = " & varXMLRequest & "<BR>
objConn.Close(
Set objConn = Nothin
Set objRS = Nothing
Jul 19 '05 #1
6 8671
Joe wrote:
I want to add a new record to database,


What database?

Bob Barrows
--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.
Jul 19 '05 #2
joe
MS-SQL 2K
Jul 19 '05 #3
Add a "SET NOCOUNT ON;" just before your INSERT statement. The INSERT row
count is being returned, before the @@IDENTITY is, and hence the error.

--
Manohar Kamath
Editor, .netWire
www.dotnetwire.com
"Joe" <an*******@discussions.microsoft.com> wrote in message
news:BD**********************************@microsof t.com...
I want to add a new record to database, and then retrives the @@IDENTITY value for this new added record, and the fields of this record. However, it has the following error on line varNewID = objRS("NewMsgID")

ADODB.Recordset (0x800A0CC1)
Item cannot be found in the collection corresponding to the requested name or ordinal.

sqlStmt= "INSERT INTO AddressValidation " _
& "(RequestXML, ResponseXML) " _
& "VALUES (" _
& "'" & strRequestXML & "'" & "," _
& "'" & strResponseXML & "'" & ");"

sqlStmt = sqlStmt & "SELECT @@IDENTITY AS NewMsgID;"

Set objConn=Server.CreateObject("ADODB.Connection")
objConn.Open strConnect
Set objRS = objConn.Execute(sqlStmt)
varNewID = objRS("NewMsgID") '<============= ERROR HERE!!!
Response.Write "varNewID = " & varNewID & "<BR>"
sqlStmt = sqlStmt & "SELECT * FROM AddressValidation WHERE AddressValidationID = " & "'" & varNewID & "'" & ";" Set objRS = objConn.Execute(sqlStmt)
varXMLRequest = objRS("RequestXML")
varXMLRequest = objRS("ResponseXML")
Response.Write "varXMLRequest = " & varXMLRequest & "<BR>"
Response.Write "varXMLRequest = " & varXMLRequest & "<BR>"
objConn.Close()
Set objConn = Nothing
Set objRS = Nothing

Jul 19 '05 #4
joe wrote:
MS-SQL 2K


OK, that means you are getting an extra recordset back containing the "x
records affected" informational message. To suppress that, add this:
sqlStmt= "SET NOCOUNT ON;" _
& "INSERT INTO AddressValidation " _
etc.

Bob Barrows

--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.
Jul 19 '05 #5
Usually implies that the field you requested is not in the table or
recordset or that the recordset has no records.

So .. I suspect that your *first* SQL statement is invalid - try it without
the SELECT @@IDENTITY or in the MSSQL Query Analyzer.

Chris.

"Joe" <an*******@discussions.microsoft.com> wrote in message
news:BD**********************************@microsof t.com...
I want to add a new record to database, and then retrives the @@IDENTITY
value for this new
added record, and the fields of this record. However, it has the following
error on
line varNewID = objRS("NewMsgID")

ADODB.Recordset (0x800A0CC1)
Item cannot be found in the collection corresponding to the requested name
or ordinal.
sqlStmt= "INSERT INTO AddressValidation " _
& "(RequestXML, ResponseXML) " _
& "VALUES (" _
& "'" & strRequestXML & "'" & "," _
& "'" & strResponseXML & "'" & ");"

sqlStmt = sqlStmt & "SELECT @@IDENTITY AS NewMsgID;"

Set objConn=Server.CreateObject("ADODB.Connection")
objConn.Open strConnect
Set objRS = objConn.Execute(sqlStmt)
varNewID = objRS("NewMsgID") '<============= ERROR HERE!!!
Response.Write "varNewID = " & varNewID & "<BR>"
sqlStmt = sqlStmt & "SELECT * FROM AddressValidation WHERE
AddressValidationID = " & "'" & varNewID & "'" & ";"
Set objRS = objConn.Execute(sqlStmt)
varXMLRequest = objRS("RequestXML")
varXMLRequest = objRS("ResponseXML")
Response.Write "varXMLRequest = " & varXMLRequest & "<BR>"
Response.Write "varXMLRequest = " & varXMLRequest & "<BR>"
objConn.Close()
Set objConn = Nothing
Set objRS = Nothing
Jul 19 '05 #6
Forget that - I always forget about SET NOCOUNT ON!
BTW: With a *single connection instance* you can always do the SELECT
@@IDENTITY as a second SQL statement recordset request it's own right since
the IDENTITY value is specific to that connection and thus retained after
each INSERT until the connection is closed.

Chris.

"Chris Barber" <ch***@blue-canoe.co.uk.NOSPAM> wrote in message
news:ua*************@TK2MSFTNGP10.phx.gbl...
Usually implies that the field you requested is not in the table or
recordset or that the recordset has no records.

So .. I suspect that your *first* SQL statement is invalid - try it without
the SELECT @@IDENTITY or in the MSSQL Query Analyzer.

Chris.

"Joe" <an*******@discussions.microsoft.com> wrote in message
news:BD**********************************@microsof t.com...
I want to add a new record to database, and then retrives the @@IDENTITY
value for this new
added record, and the fields of this record. However, it has the following
error on
line varNewID = objRS("NewMsgID")

ADODB.Recordset (0x800A0CC1)
Item cannot be found in the collection corresponding to the requested name
or ordinal.
sqlStmt= "INSERT INTO AddressValidation " _
& "(RequestXML, ResponseXML) " _
& "VALUES (" _
& "'" & strRequestXML & "'" & "," _
& "'" & strResponseXML & "'" & ");"

sqlStmt = sqlStmt & "SELECT @@IDENTITY AS NewMsgID;"

Set objConn=Server.CreateObject("ADODB.Connection")
objConn.Open strConnect
Set objRS = objConn.Execute(sqlStmt)
varNewID = objRS("NewMsgID") '<============= ERROR HERE!!!
Response.Write "varNewID = " & varNewID & "<BR>"
sqlStmt = sqlStmt & "SELECT * FROM AddressValidation WHERE
AddressValidationID = " & "'" & varNewID & "'" & ";"
Set objRS = objConn.Execute(sqlStmt)
varXMLRequest = objRS("RequestXML")
varXMLRequest = objRS("ResponseXML")
Response.Write "varXMLRequest = " & varXMLRequest & "<BR>"
Response.Write "varXMLRequest = " & varXMLRequest & "<BR>"
objConn.Close()
Set objConn = Nothing
Set objRS = Nothing

Jul 19 '05 #7

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

reply views Thread by Dot Netizen | last post: by
reply views Thread by Andre Azevedo | last post: by
3 posts views Thread by Yuk Tang | last post: by
1 post views Thread by Shiller | last post: by
reply views Thread by zhoujie | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.