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.CreateOb ject("ADODB.Con nection")

oConn.Open sConn

If Request.Form("N ext") <> "" Then
sSQL = "Exec S2_AddNewRFQ_Cu stDets '" & _
FilterSQL(Reque st.Form("Custom erID")) & "', '" & _
FilterSQL(Reque st.Form("Custom erName")) & "', '" & _
FilterSQL(Reque st.Form("Contac t")) & "', '" & _
FilterSQL(Reque st.Form("TelNo" )) & "', '" & _
FilterSQL(Reque st.Form("FaxNo" )) & "', '" & _
FilterSQL(Reque st.Form("Email" )) & "', '" & _
Session("UID") & "'"

Set rsRFQ = oConn.Execute(s SQL, iAffected)

'response.Write iAffected

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

Stored Proc -

SET QUOTED_IDENTIFI ER ON
GO
SET ANSI_NULLS ON
GO

ALTER Proc S2_AddNewRFQ_Cu stDets
@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(G etDate()), dbo.DateValue(D ateAdd(dd, 30, GetDate())),
@Username)

Select Scope_Identity( ) as QuoteID

GO

SET QUOTED_IDENTIFI ER OFF
GO
SET ANSI_NULLS ON
GO

[Bob Barrows [MVP]]

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.CreateOb ject("ADODB.Con nection")

oConn.Open sConn

If Request.Form("N ext") <> "" Then
sSQL = "Exec S2_AddNewRFQ_Cu stDets '" & _
FilterSQL(Reque st.Form("Custom erID")) & "', '" & _
FilterSQL(Reque st.Form("Custom erName")) & "', '" & _
FilterSQL(Reque st.Form("Contac t")) & "', '" & _
FilterSQL(Reque st.Form("TelNo" )) & "', '" & _
FilterSQL(Reque st.Form("FaxNo" )) & "', '" & _
FilterSQL(Reque st.Form("Email" )) & "', '" & _
Session("UID") & "'"

Set rsRFQ = oConn.Execute(s SQL, iAffected)

'response.Write iAffected

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

Stored Proc -

SET QUOTED_IDENTIFI ER ON
GO
SET ANSI_NULLS ON
GO

ALTER Proc S2_AddNewRFQ_Cu stDets
@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(G etDate()), dbo.DateValue(D ateAdd(dd, 30,
GetDate())), @Username)

Select Scope_Identity( ) as QuoteID

GO

SET QUOTED_IDENTIFI ER 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"

[CJM]

Ah... That's right...

Thanks Bob

Chris

"Bob Barrows [MVP]" <re******@NOyah oo.SPAMcom> wrote in message
news:%2******** ********@tk2msf tngp13.phx.gbl. ..

You need to SET NOCOUNT ON in your procedure.