嗨,下面有一些代码,我希望在执行SQL语句时返回生成的身份号。
set objCnn = Server.CreateObject("ADODB.connection")
CurrMachine = "my-test-box"
ObjCnn.Open("Provider=SQLOLEDB;Persist Security Info=False;User ID=Test;password=Test123;Language=British;Initial Catalog=TestDB;Data Source=" & CurrMachine)
strSQL = "INSERT INTO dbo.xyz" & _ " (field) " & _ " VALUES (" & date & ")" & _ " SELECT @@Identity "
objCnn.Execute strSQL
我添加了SELECT @@Identity,当我在SQL中这样做时,它将返回唯一的ID号。如果我回应。我得到SQL字符串,而不是结果文本。
干杯!
编辑
给你Richard
set objCnn = Server.CreateObject("ADODB.connection")
CurrMachine = "my-test-srv"
ObjCnn.Open("Provider=SQLOLEDB;Persist Security Info=False;User ID=Test;password=Test123;Language=British;Initial Catalog=Test;Data Source=" & CurrMachine)
strSQL = "INSERT INTO xyz" & _
" ([date])" & _
" VALUES ( " & date & " )" & _
" SET NOCOUNT OFF; SELECT SCOPE_IDENTITY() ID; "
Set rs = objCnn.Execute(strSQL)
response.write rs("ID")
ADODB.Connection。Execute运行一个SQL语句,但是没有修改字符串,所以当你检查(或response.write
) strSQL时,它是不变的-仍然包含SQL命令文本。
您可以将SELECT语句中创建的记录集捕获到adodb .记录集中。
strSQL = "SET NOCOUNT ON; INSERT INTO dbo.xyz" & _
" (field) " & _
" VALUES (" & date & ");" & _
" SET NOCOUNT OFF; SELECT SCOPE_IDENTITY() ID; "
Dim rs
Set rs = objCnn.Execute(strSQL)
response.write rs("ID")
请注意,您应该使用SCOPE_IDENTITY()
而不是@@IDENTITY
,只有极少数例外(如果存在的话)。