使用参数化插入sql server - 获取新的列值 [英] Inserting into sql server using parameterized - get new column value
问题描述
大家好，我是新手使用sql server和参数化sql。我希望能够返回已插入列的值。
这是我的陈述
strSqlInsetrtTrack = _
" INSERT INTO TRACK（CASE_NUM，CERT_NUM，CLMT_NUM，BROKER_NAME，" + _
" CONTRACT_SEQ，TRACK_COMMENTS，DATE_OVER_SPEC，LAST_TOUCHED）" + _
" VALUES（''" + strCase +"''，''" + strCert +"''，" + _
"''" + strClmt +"''，@ BrokerName，''" + strContractSeq +
"''，@ TrackComments，" + _
"''" + txtDateOverSpec.Text +"''，GETDATE（））" + _
" SET @TrackId = TRACK_ID"
然后我分配参数，我想我可以获得TRACK_ID
来自新插入的行，这是一个自动枚举的字段。
无论如何，这里是参数：
使用comInsertTrack
.Parameters.Add（" @ BrokerName"，SqlDbType.Char，50）
.Parameters.Add（" @ TrackComments"，SqlDbType.VarChar，500）
.Parameters（" @ BrokerName"）。Value = txtBrokerName.Text
.Parameters（" @ TrackComments"）。Value = txtTrackComments.Text
.Parameters .Add（" @ TrackId"，SqlDbType.Int）
.Parameters（" @ TrackId"）。Direction = ParameterDirection.ReturnValue
.ExecuteNonQuery（）
strTrackid = CType（.Parameters（" @ TrackId"）。Value，String）
结束
我甚至不确定这是否是返回值的意图
enum执行者。 。 。如果它可以工作真的很酷:)
错误是：
SqlException：必须声明变量''@TrackId' '
在.ExecuteNonQuery（）行。
感谢您的帮助。祝你有美好的一天！
瑞恩
Hi all, I am new to using sql server and parameterized sql. I am
hoping to be returned the value of a column that has been inserted.
Here is my statement
strSqlInsetrtTrack = _
"INSERT INTO TRACK (CASE_NUM, CERT_NUM, CLMT_NUM, BROKER_NAME, " + _
"CONTRACT_SEQ, TRACK_COMMENTS, DATE_OVER_SPEC, LAST_TOUCHED) " + _
"VALUES (''" + strCase + "'',''" + strCert + "''," + _
"''" + strClmt + "'',@BrokerName,''" + strContractSeq +
"'',@TrackComments," + _
"''" + txtDateOverSpec.Text + "'',GETDATE()) " + _
"SET @TrackId = TRACK_ID"
Then I assign the parameters and I thought I could obtain the TRACK_ID
from the newly inserted row, which is an auto-enumerated field.
Anyway, here are the parameters:
With comInsertTrack
.Parameters.Add("@BrokerName", SqlDbType.Char, 50)
.Parameters.Add("@TrackComments", SqlDbType.VarChar, 500)
.Parameters("@BrokerName").Value = txtBrokerName.Text
.Parameters("@TrackComments").Value = txtTrackComments.Text
.Parameters.Add("@TrackId", SqlDbType.Int)
.Parameters("@TrackId").Direction = ParameterDirection.ReturnValue
.ExecuteNonQuery()
strTrackid = CType(.Parameters("@TrackId").Value, String)
End With
I''m not even sure this is waht was intended by the returnvalue
enumerator . . . it would be really cool if it would work :)
The errror is:
SqlException: Must declare the variable ''@TrackId''
On the .ExecuteNonQuery() line.
Thank you for any assistance. Have a great day!
Ryan
推荐答案
< ry **** *****@gmail.com>在留言中写道
news：11 ********************** @ f14g2000cwb.googlegr oups.com ......
<ry*********@gmail.com> wrote in message
news:11**********************@f14g2000cwb.googlegr oups.com...
感谢您的帮助。祝你有美好的一天！
Thank you for any assistance. Have a great day!
http ：//www.akadia.com/services/dotne...increment.html
嗨Mark，感谢您的文章。我实际上看到了那个。
评论不是很具描述性，他们使用的是存储的
程序。我以为他们会受到不同的待遇。我也看了这篇文章：
http://authors.aspalliance.com/aspxt...eterClass.aspx
我真的没有了解什么是SET @Identity = @@ Identity意思是。
我看过msdn文档，但是它们不是很有用。
我希望我不是很密集:(无论如何，我会仔细看看
文章。同时，如果有人能提供更多信息，我会非常感激。
再次感谢！
Ryan
Hi Mark, thanks for the article. I actually saw that one. The
comments were not very descriptive and they were using stored
procedures. I thought they would be treated differently. I also
looked at this article:
http://authors.aspalliance.com/aspxt...eterClass.aspx
I didn''t really understand what "SET @Identity = @@Identity" meant.
I''ve looked at the msdn docs, but they are not very helpful.
I hope I''m not being dense :( Anyway, I''ll take a closer look at the
article. In the mean time, if anyone can give some more information, I
would greatly appreciate it.
Thanks again!
Ryan
< ry ***** ****@gmail.com>在留言中写道
news：11 ********************** @ f14g2000cwb.googlegr oups .com ...
<ry*********@gmail.com> wrote in message
news:11**********************@f14g2000cwb.googlegr oups.com...
我真的不明白什么是SET @Identity = @@ Identity的意思。
I didn''t really understand what "SET @Identity = @@Identity" meant.
这意味着将局部变量@Identity设置为系统变量@@ Identity，
等于当前登录下的最新自动增量值
到SQL Server 。
It meant set the local variable @Identity to the system variable @@Identity,
which equates to the most recent autoincrement value under the current login
to SQL Server.
这篇关于使用参数化插入sql server - 获取新的列值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
