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

Discussion in 'ASP General' started by CJM, Nov 9, 2004.

  1. CJM

    CJM Guest

    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, Nov 9, 2004
    #1
    1. Advertising

  2. 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"
     
    Bob Barrows [MVP], Nov 9, 2004
    #2
    1. Advertising

  3. CJM

    CJM Guest

    Ah... That's right...

    Thanks Bob

    Chris

    "Bob Barrows [MVP]" <> wrote in message
    news:%...
    > You need to SET NOCOUNT ON in your procedure.
     
    CJM, Nov 9, 2004
    #3
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Replies:
    4
    Views:
    2,216
  2. Øyvind Isaksen
    Replies:
    1
    Views:
    1,026
    Øyvind Isaksen
    May 18, 2007
  3. J. Muenchbourg
    Replies:
    5
    Views:
    144
    Aaron Bertrand [MVP]
    Sep 27, 2003
  4. Augustus
    Replies:
    3
    Views:
    205
    Ken Fine
    Feb 5, 2004
  5. Vinod
    Replies:
    4
    Views:
    1,024
    Patrice
    Feb 24, 2005
Loading...

Share This Page