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

[Bob Barrows [MVP]]

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.

[joe]

MS-SQL 2K

[Manohar Kamath [MVP]]

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

[Bob Barrows [MVP]]

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.

[Chris Barber]

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

[Chris Barber]

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