SCOPE_IDENTITY() problem, please assist 1

[stfarm]

I hope that you guys can help me.
I get an error msg when I run my asp page. It does not seem to recognize my field that I return.

Here is my stored proc

CREATE PROCEDURE [dbo].[FACT_ins_Initiative]

@initiative_description varchar(250)
AS
insert into FACT_Initiative
(initiative_description)

values

(@initiative_description)

SELECT newid = SCOPE_IDENTITY()

GO
________________________________________________

and here is the ASP page......

set cmd = Server.CreateObject("ADODB.Command")

cmd.ActiveConnection = MM_FACT_STRING

cmd.CommandText = "dbo.FACT_ins_Initiative"

cmd.CommandType = 4

cmd.CommandTimeout = 0

cmd.Prepared = true

cmd.Parameters.Append cmd.CreateParameter("@RETURN_VALUE", 3, 4)

cmd.Parameters.Append cmd.CreateParameter("@initiative_description", 200, 1,250,"Yipee")

set RS = cmd.Execute

RS_numRows = 0

response.write "RV= " & RS("newid") <---- Does error out

Any suggestions are appreciated.


Steve

Steve

http://www.ShopHereNow.net

http://www.XdataSolutions.com

http://www.ItsMoreThanSoftware.com

 

[gmmastros]

This is sort-of a shot in the dark (but a reasonably good guess).

Try changing the stored procedure to...

CREATE PROCEDURE [dbo].[FACT_ins_Initiative]

@initiative_description varchar(250)
AS
[red] SET NOCOUNT ON[/red]
insert into FACT_Initiative
(initiative_description)

values

(@initiative_description)

SELECT newid = SCOPE_IDENTITY()

GO

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[stfarm]

THAT did it man!! Thank you a million!!

Steve

Steve

http://www.ShopHereNow.net

http://www.XdataSolutions.com

http://www.ItsMoreThanSoftware.com

 

[gmmastros]

I don't have much experience with ASP, but in VB, the same problem exists. It's become habit for me to include SET NOCOUNT ON in all of my stored procedures.

I'm glad this was helpful, and thanks for the star.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[stfarm]

George, I know this sounds stupid, but what does that do?

Steve

Steve

http://www.ShopHereNow.net

http://www.XdataSolutions.com

http://www.ItsMoreThanSoftware.com

 

[gmmastros]

I'm not entirely sure. Butm here's what I notice....

When I execute batch command in th query analyzer (with several statements), I notice the QA shows how many records are affected by each command. If i run a simple update command there is only 1 line for (1 record affected). If I run to commands, like this...

Insert into table(field1) Values(1)
Insert Into table(Field1) Values(29)

then I see...

(1 row(s) affected)
(1 row(s) affected)

I think this information is passed back in the recordset (as a recordset), so you effectively have 2 recordsets in your recordset object.

Since your SP has 2 commands, the insert, and the Select NewId = Scope_Identity(), I think 2 recordsets are getting returned from the database. The first says... (1 row affected, 1 row affected) and the second actually has your data.

By setting nocount on, you are effectively suppressing the first part.

http://support.microsoft.com/kb/q197528/

Hope this helps.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[johnwm]

You could also look at

http://msdn2.microsoft.com/en-us/library/ms189837.aspx

for a fuller explanation

________________________________________________________________
If you want to get the best response to a question, please check out FAQ222-2244 first.
'If we're supposed to work in Hex, why have we only got A fingers?'
Drive a Steam Roller

 

[DotNetGnat]

stfarm,

as George pointed out...when you do NOT set the NOCOUNT, what happens is the messages like "1 rows affected" are sent by the database causing the connection to close...and when you try to retrieve the value (thinking that the connection is still active) you get the error on the asp page...

-DNG

 

[stfarm]

Thank you everybody for the explanations.
This place rules!

Steve

Steve

http://www.ShopHereNow.net

http://www.XdataSolutions.com

http://www.ItsMoreThanSoftware.com