vb.net 插入行后从表中返回标识值
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/21100564/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Return Identity Value From Table After Inserting Row
提问by user3191666
I am trying to store the value of an identity value from table after inserting it into a row.
我试图在将其插入行后存储表中标识值的值。
I want to store the value in currentID so I can store the value in another table which could be added more than once.
我想将值存储在 currentID 中，以便我可以将值存储在另一个可以添加多次的表中。
Any help or advice would be great!
任何帮助或建议都会很棒！
Dim currentID As Integer
Dim cmd As New System.Data.SqlClient.SqlCommand
cmd.CommandType = System.Data.CommandType.Text
cmd.CommandText = "INSERT INTO table (Databasevalue) VALUES ('" + formvalue + "');"
cmd.Connection = sqlconninsertDB
sqlconninsertDB.Open()
cmd.ExecuteNonQuery()
**currentID = [get and store value]**
sqlconninsertDB.Close()
回答by Yuriy Galanter
You can try adding this SELECT to your command:
您可以尝试将此 SELECT 添加到您的命令中：
cmd.CommandText = "INSERT INTO table (Databasevalue) VALUES ('" + formvalue + "'); SELECT SCOPE_IDENTITY()"
And execute query like this:
并像这样执行查询：
Dim currentID as Integer = cmd.ExecuteScalar()
Instead of .ExecuteNonQuery()
代替 .ExecuteNonQuery()
Side note: Try not to use string concatenations when constructing inline-commands. Rather look into parametrized queries.
旁注：在构造内联命令时尽量不要使用字符串连接。而是研究参数化查询。
回答by Steve
With Sql Server you could pass two sql commands separating them with a semicolon.
The second command is a SELECT SCOPE_IDENTITY()and this returns the last ID inserted in this scope.
You could execute both commands and retrieve the result with ExecuteScalar
使用 Sql Server，您可以传递两个 sql 命令，用分号分隔它们。
第二个命令是SELECT SCOPE_IDENTITY()，它返回插入此范围的最后一个 ID。
您可以执行这两个命令并使用ExecuteScalar检索结果
Please, take note to use a parameterized query instead of a string concatenation to avoid Sql Injection attacks and parsing problems.
请注意使用参数化查询而不是字符串连接，以避免 Sql 注入攻击和解析问题。
Dim currentID As Integer
Dim cmd As New System.Data.SqlClient.SqlCommand
cmd.CommandType = System.Data.CommandType.Text
cmd.CommandText = "INSERT INTO table (Databasevalue) VALUES (@formval); " +
"SELECT SCOPE_IDENTITY()"
cmd.Connection = sqlconninsertDB
cmd.Parameters.AddWithValue("@formval", formValue)
sqlconninsertDB.Open()
currentid = cmd.ExecuteScalar()
sqlconninsertDB.Close()
