Item cannot be found in the collection corresponding to the requested name or ordinal

[Jodda]

Hi there,

Has any 1 had problems with the error message below while inserting an SQL record?
-----------------------------------------
ADODB.Recordset (0x800A0CC1)
Item cannot be found in the collection corresponding to the requested name or ordinal.
-----------------------------------------------
The error points to the following line:
-----------------------------------------------
Set RequestRegisterDB = Server.CreateObject("ADODB.Recordset")
sSourceString = "EXEC RegisterRequest " & txtLevel & ",'" & txtFullName & "','" & txtEmail & "','" & txtPhone & "','" & txtFax & "','" & txtComp & "','" & txtPos & "','" & txtCompAddr & "','" & txtSoftLevel & "'"
RequestRegisterDB.Open sSourceString, Application("ConnectionStringAdmin")
lRequestID = CLng(RequestRegisterDB.Fields("RequestID").Value)
RequestRegisterDB.Close
Set RequestRegisterDB = Nothing
--------------------------------------------
When inserting a record, if a submitted email matches an existing email address, RequestID = -1, else the identity number is returned.

The record is correctly inserted evan thow the error message is shown, and if the page is refreshed, the return value of -1 is correctly pickep up and processed, seems like the first attempt returns nothing.

I Response.Write'd sSourceString, copied the wrote text and executed in the 'Query Analyser' and it worked fine - returning a correct 'RequestID'.

Happened to any 1 else before? If so, please say how to resolve..

[whammy]

That error in my experience means you either misspelled a field name, or are looking for a field that isn't in the database.

:)

[Jodda]

I've triple checked spelling on var names, the line was originally:

lRequestID = CLng(RequestRegisterDB.Fields(0).Value)

but tried the var name as a case of elimination, the field def exsists for the field returned is the 'Identity', which is the first field in the database. Nevermind, suppose i'll resort to brute force and search for me hammer :o

[Jodda]

Found the problem,

/* Fixes "Closed ADO RecordSet" problem when using temp table */
SET NOCOUNT ON