473,226 Members | 1,515 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,226 software developers and data experts.

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 8800
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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

0
by: Randi | last post by:
Hi all, I have this project to use the ADODB control to acces and manipulate the Access DB. I amd the mistake of first doing this project with just the data control. It worked fine with this...
0
by: elcc1958 | last post by:
I need to support a VB6 application that will be receiving disconnected ADODB.Recordset from out DotNet solution. Our dotnet solution deals with System.Data.DataTable. I need to populate a...
0
by: Dot Netizen | last post by:
I am having trouble passing an ADODB.Recordset from a VB6 application to a VB.Net class library using COM Interop. I am running this on XP SP2 with the .Net Framework 1.1 and MDAC 2.8 SP1. I've...
5
by: Simone | last post by:
Hello I hope you guys can help me. I am very new to ADO... I am creating a ADODB connection in a module and trying to access it from a command button in a form. Function fxEIDAssgn(plngEID As...
0
by: Andre Azevedo | last post by:
Hi all ! I've created a .net serviced component with only one method. This method receive an ADODB.Command object and execute it. The ADODB.Command object is created in the client process. (VB...
3
by: Yuk Tang | last post by:
I'm trying to grab the fieldnames and values from a recordset, but I'm getting errors. I have an idea what the error might come from, but I'm not sure how to correct it. I'm connecting to an...
7
by: boyleyc | last post by:
Hi all I have written a database in access and used ADODB recordsets all the way through. The only recordsets that are not ADODB are the listbox navigation code automatically generated by access...
3
by: dotnetdev1 | last post by:
Hi Friends, Wen I am rying to run my asp application....i am ending up with this following error... Error Type: ADODB.Recordset (0x800A0CC1) Item cannot be found in the...
1
by: Shiller | last post by:
Experts, I keep getting the following error message when accessing a web page: ADODB.Recordset (0x800A0CC1) Item cannot be found in the collection corresponding to the requested name or...
0
by: VivesProcSPL | last post by:
Obviously, one of the original purposes of SQL is to make data query processing easy. The language uses many English-like terms and syntax in an effort to make it easy to learn, particularly for...
3
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 3 Jan 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). For other local times, please check World Time Buddy In...
0
by: abbasky | last post by:
### Vandf component communication method one: data sharing ​ Vandf components can achieve data exchange through data sharing, state sharing, events, and other methods. Vandf's data exchange method...
0
by: stefan129 | last post by:
Hey forum members, I'm exploring options for SSL certificates for multiple domains. Has anyone had experience with multi-domain SSL certificates? Any recommendations on reliable providers or specific...
1
by: davi5007 | last post by:
Hi, Basically, I am trying to automate a field named TraceabilityNo into a web page from an access form. I've got the serial held in the variable strSearchString. How can I get this into the...
0
by: MeoLessi9 | last post by:
I have VirtualBox installed on Windows 11 and now I would like to install Kali on a virtual machine. However, on the official website, I see two options: "Installer images" and "Virtual machines"....
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: Aftab Ahmad | last post by:
Hello Experts! I have written a code in MS Access for a cmd called "WhatsApp Message" to open WhatsApp using that very code but the problem is that it gives a popup message everytime I clicked on...
0
by: Aftab Ahmad | last post by:
So, I have written a code for a cmd called "Send WhatsApp Message" to open and send WhatsApp messaage. The code is given below. Dim IE As Object Set IE =...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.