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

 

[Bob Barrows [MVP]]

You need to SET NOCOUNT ON in your procedure.

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

 

[CJM]

Ah... That's right...

Thanks Bob

Chris