Classic ASP getting SCOPE_IDENTITY () Value from SQL2005
I can't figure out how to get SCOPE_IDENTITY () back to my variables from the SQL2005 storage procedure.
My sSQL string:
sSQL = "EXEC [sp_NewClaim] " & Chr(34) & ClaimNumber & Chr(34) & ", " & Request.Cookies("UserID") & ", " & Request.Cookies("MasterID") & ", " & Chr(34) & strRestaurante & Chr(34) & ", " & Chr(34) & Fecha & Chr(34) & ", " & Chr(34) & Hora & Chr(34) & ", " & Chr(34) & Request("Tiempo") & Chr(34) & ", " & Chr(34) & Request("Luz") & Chr(34) & ", " & Chr(34) & Request("Desc") & Chr(34) & ", " & Chr(34) & Request("incidente") & Chr(34) & ", " & Chr(34) & Request("codigos") & Chr(34) & ", False, 0; SELECT RecordNumber = SCOPE_IDENTITY()"
My sSQL output:
EXEC [sp_NewClaim] "W200811", 7, 8, "Otro -- WORK PLEASE", "11/19/2008", "01:19 PM", "Nublado", "Mala", "asdasd", "uyiuyui", "C-Junta", False, 0; SELECT RecordNumber = SCOPE_IDENTITY()
Executing my SQL command:
Set rsData= Server.CreateObject("ADODB.Recordset")
rsData.Open sSQL, conDB, adOpenKeyset, adLockOptimistic
Attempting to output SCOPE_IDENTITY () Produces an empty variable (no output):
Response.Write("<br />Record Number: " & rsData("RecordNumber"))
The save procedure is correct. My information is stored in my database with no problem. RecordNumber is the ID column, and the Store procedure defined @RecordNumber as output:
USE [db_clcinsurance_com]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE sp_NewClaim
(
@ClaimNumber nvarchar(50),
@blah............
.................
@RecordNumber INT OUTPUT
)
AS
BEGIN
INSERT INTO Accidente (ClaimNumber,........., RecordNumber)
VALUES (@ClaimNumber,....., @RecordNumber)
SET @RecordNumber = SCOPE_IDENTITY();
END
source to share
For your stored procedure, do the following:
CREATE PROCEDURE sp_NewClaim
(
@ClaimNumber nvarchar(50),
@blah............
.................
)
AS
BEGIN
SET NOCOUNT ON;
INSERT INTO Accidente (ClaimNumber,........., RecordNumber)
VALUES (@ClaimNumber,....., @RecordNumber)
SELECT SCOPE_IDENTITY()
END
And then get the id just like you get any other query result.
source to share
I agree with Joel Coehoorn's answer, but I would like to point out that you are sending your SCOPE_IDENTITY () variable as an output parameter, but not receiving it that way in your ado call. You cannot get the output parameter using the method you must call the stored procedure.
If you're interested, there are some ado examples here for calling stored procedures.
source to share
You can try validating to make sure the function SCOPE_IDENTITY()
works as expected - add SELECT @RecordNumber
to the end of the sproc and manually run it in Management Studio to make sure this variable is set as you expect. If that doesn't work, try SELECT SCOPE_IDENTITY()
confirming that it works at all. Finally, hardcode the value of the variable as a test to make sure the OUTPUT parameter works as it should.
source to share
Asp classic is not my forte, but the ideas are the same.
The problem is that you are not returning the ID as a recordset but as OUT Parameters. This means that you are trying to read it incorrectly.
Try Joel's suggestion or get it via return code:
Return Scope_Identity()
Alternatively, you should create your request with parameters and specify the latter as your out parameter. Then run the query and check the value of the last parameter. In .NET that will (convert to VB as needed):
SqlCommand cmd = new SqlCommand("INSERT INTO Foo (Description) VALUES (@Description); SET @Result = SCOPE_IDENTITY()");
SqlParameter paramDesc = new SqlParameter("@Description", SqlDbType.Int);
cmd.Parameters.Add(paramDesc);
SqlParameter paramResult = new SqlParameter("@Result", SqlDbType.Int);
paramResult.Direction = ParameterDirection.Output;
cmd.Parameters.Add(paramResult);
I hope you do some cleanup on the input as the query method is very susceptible to SQL Injection attacks.
Rob.
source to share
I agree with Robert. If you are going to use an out parameter in your stored procedure and call it using dynamic SQL, in your build up, you will have to assign an SQL variable to the out parameter and then select that variable. You should also use the OUTPUT keyword when assigning an SQL variable, for example:
sSQL = "DECLARE @RecNo int; EXEC [sp_NewClaim] 'param1', 'param2', etc..... @RecNo OUTPUT; SELECT @RecNo;"
source to share