Frustrating Stored Procedure Prob

Try putting:
set nocount on
at the beginning of the stored procedure.

"Andrew Paton" <andrew.paton@nospamplease.nufsaid.net> a écrit dans le message de news: [email]eYthI2MQDHA.3768@tk2msftngp13.phx.gbl[/email]...

> I have the Simple Stored Procedure:
>
> CREATE PROCEDURE dbo.spCreateCart
> AS
> BEGIN
> INSERT tblCart
> VALUES (getdate())
> SELECT @@IDENTITY as cartID
> END
> GO
>
> When i run this in stored procedure it works fine, i get my record created
> and it returns the field i want under the alias 'cartID'
>
> However when i try to grab this value using the following ASP i get an 'Item
> cannot be found in the collection corresponding to the requested name or
> ordinal.' Error.
>
> SQL = "EXEC dbo.spCreateCart"
> rsCreateCart = conn.execute(SQL)
>
> cartID = rsCreateCart("cartID")
> response.write cartID
>
> I know that my connection is fine - since it works with other sections of
> the script. And i have checked field names etc. This one really has me
> stumped. Does anyone have any ideas?
>
> Thankyou
>
>
>

Try putting:
set nocount on
at the beginning of the stored procedure.

"Andrew Paton" <andrew.paton@nospamplease.nufsaid.net> a écrit dans le message de news: [email]eYthI2MQDHA.3768@tk2msftngp13.phx.gbl[/email]...

> I have the Simple Stored Procedure:
>
> CREATE PROCEDURE dbo.spCreateCart
> AS
> BEGIN
> INSERT tblCart
> VALUES (getdate())
> SELECT @@IDENTITY as cartID
> END
> GO
>
> When i run this in stored procedure it works fine, i get my record created
> and it returns the field i want under the alias 'cartID'
>
> However when i try to grab this value using the following ASP i get an 'Item
> cannot be found in the collection corresponding to the requested name or
> ordinal.' Error.
>
> SQL = "EXEC dbo.spCreateCart"
> rsCreateCart = conn.execute(SQL)
>
> cartID = rsCreateCart("cartID")
> response.write cartID
>
> I know that my connection is fine - since it works with other sections of
> the script. And i have checked field names etc. This one really has me
> stumped. Does anyone have any ideas?
>
> Thankyou
>
>
>

[Robb Meade]

"Andrew Paton" wrote ...

> However when i try to grab this value using the following ASP i get an

'Item

> cannot be found in the collection corresponding to the requested name or
> ordinal.' Error.

Try collecting the value in your ASP like this...

strCartID = RS(0)

See if that works :)

Robb

[Robb Meade]

"Andrew Paton" wrote ...

> However when i try to grab this value using the following ASP i get an

'Item

> cannot be found in the collection corresponding to the requested name or
> ordinal.' Error.

Try collecting the value in your ASP like this...

strCartID = RS(0)

See if that works :)

Robb

From BOL:
Each INSERT, UPDATE, and DELETE statement returns a result set containing only the number of rows affected by the modification...
When the NOCOUNT option is set on, SQL Server does not return the counts of the rows affected by a statement.

It is good practice to always include 'set nocount on' in all INSERT, UPDATE, and DELETE procedures, unless you really want to return Rowcount to the client. This also boosts performance.

If you do not include it, you have to use:
"set rsCreateCart = rsCreateCart.nextrecordset"
in your ASP page to access "cartID".

"Andrew Paton" <andrew.paton@nospamplease.nufsaid.net> a écrit dans le message de news: e2p5U$MQDHA.3016@TK2MSFTNGP10.phx.gbl...

> >Try putting:
> >set nocount on

>
> Thankyou this worked a treat - can i ask what difference setting no count on
> makes?
>
>
>

From BOL:
Each INSERT, UPDATE, and DELETE statement returns a result set containing only the number of rows affected by the modification...
When the NOCOUNT option is set on, SQL Server does not return the counts of the rows affected by a statement.

It is good practice to always include 'set nocount on' in all INSERT, UPDATE, and DELETE procedures, unless you really want to return Rowcount to the client. This also boosts performance.

If you do not include it, you have to use:
"set rsCreateCart = rsCreateCart.nextrecordset"
in your ASP page to access "cartID".

"Andrew Paton" <andrew.paton@nospamplease.nufsaid.net> a écrit dans le message de news: e2p5U$MQDHA.3016@TK2MSFTNGP10.phx.gbl...

> >Try putting:
> >set nocount on

>
> Thankyou this worked a treat - can i ask what difference setting no count on
> makes?
>
>
>