473,231 Members | 1,699 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,231 software developers and data experts.

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

CJM
I'm running a stored procedure that inserts a record into a table then
returns the Identity field value. I've done this kind of thing plenty of
times in the past, but I'm obviously doing something wrong this time..

I've tested the Stored Proc in QA and it works fine. The SQL code runs OK in
ASP (iAffected = 1 which shows the record is inserted), but when I try to
query the resulting recordset, I get an error:

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

I'm sure this is something simple, but having just come back off 3 weeks
leave, my brain isn't yet up to speed...

Thanks

Chris

Code snippets:

ASP -

sConn = Application("DB")
Set oConn = Server.CreateObject("ADODB.Connection")

oConn.Open sConn

If Request.Form("Next") <> "" Then
sSQL = "Exec S2_AddNewRFQ_CustDets '" & _
FilterSQL(Request.Form("CustomerID")) & "', '" & _
FilterSQL(Request.Form("CustomerName")) & "', '" & _
FilterSQL(Request.Form("Contact")) & "', '" & _
FilterSQL(Request.Form("TelNo")) & "', '" & _
FilterSQL(Request.Form("FaxNo")) & "', '" & _
FilterSQL(Request.Form("Email")) & "', '" & _
Session("UID") & "'"

Set rsRFQ = oConn.Execute(sSQL, iAffected)

'response.Write iAffected

iRFQ = rsRFQ("QuoteID")
If iRFQ > 0 Then
Response.Redirect "newrfq2.asp?id=" & iRFQ
Else
'error!
End if
End If

Stored Proc -

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

ALTER Proc S2_AddNewRFQ_CustDets
@CustomerID varchar(15),
@CustomerName varchar(50),
@Contact varchar(30),
@TelNo varchar(20),
@FaxNo varchar(20),
@Email varchar(50),
@Username varchar(20)
As

Insert into QuoteHeader
(CustomerID, CustomerName, ContactName, TelNo, FaxNo, Email, Originator,
LoggedDate, ExpirationDate, AssignedTo)
Values
(@CustomerID, @CustomerName, @Contact, @TelNo, @FaxNo, @Email, @Username,
dbo.DateValue(GetDate()), dbo.DateValue(DateAdd(dd, 30, GetDate())),
@Username)

Select Scope_Identity() as QuoteID

GO

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
Jul 21 '05 #1
2 22755
You need to SET NOCOUNT ON in your procedure.

CJM wrote:
I'm running a stored procedure that inserts a record into a table then
returns the Identity field value. I've done this kind of thing plenty
of times in the past, but I'm obviously doing something wrong this
time..
I've tested the Stored Proc in QA and it works fine. The SQL code
runs OK in ASP (iAffected = 1 which shows the record is inserted),
but when I try to query the resulting recordset, I get an error:

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

I'm sure this is something simple, but having just come back off 3
weeks leave, my brain isn't yet up to speed...

Thanks

Chris

Code snippets:

ASP -

sConn = Application("DB")
Set oConn = Server.CreateObject("ADODB.Connection")

oConn.Open sConn

If Request.Form("Next") <> "" Then
sSQL = "Exec S2_AddNewRFQ_CustDets '" & _
FilterSQL(Request.Form("CustomerID")) & "', '" & _
FilterSQL(Request.Form("CustomerName")) & "', '" & _
FilterSQL(Request.Form("Contact")) & "', '" & _
FilterSQL(Request.Form("TelNo")) & "', '" & _
FilterSQL(Request.Form("FaxNo")) & "', '" & _
FilterSQL(Request.Form("Email")) & "', '" & _
Session("UID") & "'"

Set rsRFQ = oConn.Execute(sSQL, iAffected)

'response.Write iAffected

iRFQ = rsRFQ("QuoteID")
If iRFQ > 0 Then
Response.Redirect "newrfq2.asp?id=" & iRFQ
Else
'error!
End if
End If

Stored Proc -

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

ALTER Proc S2_AddNewRFQ_CustDets
@CustomerID varchar(15),
@CustomerName varchar(50),
@Contact varchar(30),
@TelNo varchar(20),
@FaxNo varchar(20),
@Email varchar(50),
@Username varchar(20)
As

Insert into QuoteHeader
(CustomerID, CustomerName, ContactName, TelNo, FaxNo, Email,
Originator, LoggedDate, ExpirationDate, AssignedTo)
Values
(@CustomerID, @CustomerName, @Contact, @TelNo, @FaxNo, @Email,
@Username, dbo.DateValue(GetDate()), dbo.DateValue(DateAdd(dd, 30,
GetDate())), @Username)

Select Scope_Identity() as QuoteID

GO

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO


--
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"
Jul 21 '05 #2
CJM
Ah... That's right...

Thanks Bob

Chris

"Bob Barrows [MVP]" <re******@NOyahoo.SPAMcom> wrote in message
news:%2****************@tk2msftngp13.phx.gbl...
You need to SET NOCOUNT ON in your procedure.

Jul 21 '05 #3

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

5
by: J. Muenchbourg | last post by:
The field name 'articleid', which is an identity/primary key , is not being recognized in my recordset as I get an " Item cannot be found in the collection corresponding to the requested name or...
4
by: Dthmtl | last post by:
I know what this error is, all fieldnames have been verified and reverified. Not sure why it is happening. Here is the SQL statement strSQL = "SELECT Problems.TicketNumber, Activity, ActDate...
8
by: baustin75 | last post by:
Posted: Mon Oct 03, 2005 1:41 pm Post subject: cannot mail() in ie only when debugging in php designer 2005 -------------------------------------------------------------------------------- ...
9
by: Gerald Lightsey | last post by:
I am doing some work that involves automating MS MapPoint 2002. The object model is documented in the context of Visual Basic 6.0. A typical example follows. 'Output first result of find...
18
by: JohnR | last post by:
From reading the documentation, this should be a relatively easy thing. I have an arraylist of custom class instances which I want to search with an"indexof" where I'm passing an instance if the...
2
by: indhu | last post by:
Hi its not working, i want only distinct record. here its repeated records coming. accdb = "SELECT DISTINCT sequence FROM scene WHERE sceneid = '" & myquery & "' " and when i select the...
2
by: anson | last post by:
this code is an example code of TPOP, i type them and can be compiled... but when give some input , it getting segment fault .... i observed that when the build() initial the word table ... it...
2
by: Sticksboy | last post by:
I keep getting this error message - Item cant be found in the collection corresponding to the requested name or ordinal I dont understand what it means to be honest. I am trying to get the code...
8
by: charli | last post by:
Error 3265, "Item cannot be found in the collection corresponding to the requested name or ordinal" code programatically opens a query using ADOX and changed the sql Dim cat As New...
0
by: jianzs | last post by:
Introduction Cloud-native applications are conventionally identified as those designed and nurtured on cloud infrastructure. Such applications, rooted in cloud technologies, skillfully benefit from...
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...
0
Git
by: egorbl4 | last post by:
Скачал я git, хотел начать настройку, а там вылезло вот это Что это? Что мне с этим делать? ...
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: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
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.