经典ASP越来越SCOPE_IDENTITY()从SQL2005值 [英] Classic ASP getting SCOPE_IDENTITY() Value from SQL2005
问题描述
我无法弄清楚如何从SQL2005存储过程得到SCOPE_IDENTITY()回到我的变量。
我sSQL字符串:
sSQL =EXEC [sp_NewClaim]&放大器;字符(34)及ClaimNumber&安培;字符(34)及与& Request.Cookies时(用户名)及与& Request.Cookies时(MasterID)及与&字符(34)及strRestaurante&安培;字符(34)及与&字符(34)及出生日期和放大器;字符(34)及与&字符(34)及霍拉&安培;字符(34)及与&字符(34)及请求(时代报)及字符(34)及与&字符(34)及请求(路斯)及字符(34)及与&字符(34)及请求(说明)及字符(34)及与&字符(34)及请求(incidente)及字符(34)及与&字符(34)及请求(codigos)及字符(34)及假,0,选择RecordNumber = SCOPE_IDENTITY()
我sSQL输出:
EXEC [sp_NewClaim]W200811,7,8,OTRO - 的工作,请,11/19/2008,下午1点19,Nublado 麻辣,asdasd,uyiuyui,C-军政府,假,0;选择RecordNumber = SCOPE_IDENTITY()
执行我的SQL命令:
设置rsData =的Server.CreateObject(ADODB.Recordset)
rsData.Open sSQL,conDB,adOpenKeyset,ADLOCKOPTIMISTIC
试图输出SCOPE_IDENTITY()生成空变量(无输出):
的Response.Write(< BR />备案号:&放大器; rsData(RecordNumber))
该存储过程运行正常。我的信息被存储到我的问题出数据库。 RecordNumber与标识列和存储过程定义@RecordNumber作为输出:
使用[db_clcinsurance_com]
走SET ANSI_NULLS ON
走
SET QUOTED_IDENTIFIER ON
走
CREATE PROCEDURE sp_NewClaim
(
@ClaimNumber为nvarchar(50),
@blah ............
.................
@RecordNumber INT输出
)
如开始 INSERT INTO Accidente(ClaimNumber,.........,RecordNumber) VALUES(@ClaimNumber,.....,@RecordNumber) SET @RecordNumber = SCOPE_IDENTITY();结束
有关您的存储过程,做到这一点:
CREATE PROCEDURE sp_NewClaim
(
@ClaimNumber为nvarchar(50),
@blah ............
.................
)
如
开始
SET NOCOUNT ON; INSERT INTO Accidente(ClaimNumber,.........,RecordNumber)
VALUES(@ClaimNumber,.....,@RecordNumber) SELECT SCOPE_IDENTITY()
结束
然后拿到身份证你会找回其他任何查询结果是一样的。
I can't figure out how to get the SCOPE_IDENTITY() back to my variables from an SQL2005 Store 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
Trying to Output the SCOPE_IDENTITY() Produces an Empty Variable (No Output):
Response.Write("<br />Record Number: " & rsData("RecordNumber"))
The Store Procedure runs correctly. My Information gets stored into my database with out problems. RecordNumber is the Column with the Identity, and the Store Procedure has defined @RecordNumber as an 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
For your stored procedure, do this:
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 the same way you'd retrieve any other query result.
这篇关于经典ASP越来越SCOPE_IDENTITY()从SQL2005值的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!