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 2 22774
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"
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. This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics
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...
|
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...
|
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
--------------------------------------------------------------------------------
...
|
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...
|
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...
|
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...
|
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...
|
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...
|
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...
|
by: Charles Arthur |
last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
|
by: BarryA |
last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
|
by: nemocccc |
last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
|
by: Sonnysonu |
last post by:
This is the data of csv file
1 2 3
1 2 3
1 2 3
1 2 3
2 3
2 3
3
the lengths should be different i have to store the data by column-wise with in the specific length.
suppose the i have to...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers,...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven...
|
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing,...
| |