SQL sql更新命令的vb.net问题
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/25498037/
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
vb.net problem with sql update command
提问by 8675309
I'm looking for some help trying to figure out why I'm seeing a SQL error on an update command within vb.net, noting that my SQL experience is very limited.
我正在寻找一些帮助，试图找出为什么我在 vb.net 中的更新命令上看到 SQL 错误，并指出我的 SQL 经验非常有限。
I'm in the midst of building a profile system that will be used in several other tools within our company, as part of the profile system, the user's information is queried against our LDAP directory upon page load and the data stored in variables displayed in labels on the page.
我正在构建一个配置文件系统，该系统将在我们公司内的其他几个工具中使用，作为配置文件系统的一部分，在页面加载时根据我们的 LDAP 目录查询用户信息，并将存储在变量中的数据显示在页面上的标签。
The user then has the option to create a new profile if one does not exist, or update an existing if there is already one in place. The code determines the proper action based on a query to the table using the users employee id.
如果配置文件不存在，则用户可以选择创建新配置文件，如果已有配置文件，则可以更新现有配置文件。该代码根据使用用户员工 ID 对表的查询来确定正确的操作。
My insert command works ok but the update does not, below is my current code and the error.
我的插入命令工作正常但更新没有，下面是我当前的代码和错误。
This is the insert statement to create a new entry, this works properly.
这是用于创建新条目的插入语句，它可以正常工作。
Protected Sub btnInsert_Click(sender As Object, e As EventArgs) Handles btnInsert.Click
Try
Dim con As New SqlConnection
Dim cmd As New SqlCommand
con.ConnectionString = "Data Source="server" Catalog=usertable;Integrated Security=True"
con.Open()
cmd.Connection = con
cmd.CommandText = "INSERT INTO Profile (FirstName, LastName, Email, Telephone, EmployeeID, NTLogin) VALUES ('" & lblFirstName.Text & "','" & lblLastName.Text & "','" & lblEmail.Text & "','" & lblPhone.Text & "','" & lblEID.Text & "','" & lblNT.Text & "')"
cmd.ExecuteNonQuery()
btnInsert.Enabled = False
lblValid.Text = "Record Inserted Successfully"
con.Close()
Catch ex As System.Exception
lblValid.Text = (ex.Message)
End Try
The following is the update code that gives an error:
以下是出现错误的更新代码：
Protected Sub btnUpdate_Click(sender As Object, e As EventArgs) Handles btnUpdate.Click
Try
Dim con As New SqlConnection
Dim cmd As New SqlCommand
con.ConnectionString = "server;Initial Catalog=usertable;Integrated Security=True"
con.Open()
cmd.Connection = con
cmd.CommandText = "UPDATE Profile (FirstName, LastName, Email, Telephone, EmployeeID, NTLogin) VALUES ('" & lblFirstName.Text & "','" & lblLastName.Text & "','" & lblEmail.Text & "','" & lblPhone.Text & "','" & lblEID.Text & "','" & lblNT.Text & "') Where [EmployeeID] = '" & lblEID.text & "'"
cmd.ExecuteNonQuery()
btnUpdate.Enabled = False
lblValid.Text = "Record Updated Successfully"
con.Close()
Catch ex As System.Exception
lblValid.Text = (ex.Message)
End Try
End Sub
The exception error returned is
返回的异常错误是
[SqlException (0x80131904): Incorrect syntax near '('.]
System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction) +392
System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose) +815
System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady) +4515
System.Data.SqlClient.SqlCommand.RunExecuteNonQueryTds(String methodName, Boolean async, Int32 timeout, Boolean asyncWrite) +1390
System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(TaskCompletionSource`1 completion, String methodName, Boolean sendToPipe, Int32 timeout, Boolean asyncWrite) +538
System.Data.SqlClient.SqlCommand.ExecuteNonQuery() +290
newprofile.btnUpdate_Click(Object sender, EventArgs e) +759
System.Web.UI.WebControls.Button.RaisePostBackEvent(String eventArgument) +155
System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +3804
I'm at a loss and appreciate any help that can be given.
我不知所措，并感谢可以提供的任何帮助。
回答by Vladimir Kocjancic
Update syntax is completely invalid as stated by @JBKing.
如@JBKing 所述，更新语法完全无效。
Update statements are of form:
更新语句的格式为：
UPDATE myTableName SET field=new_value WHERE condition=some_value
Also, to avoid possible injections, do not pass field values directly into SQL command. Use parameters How to use parameters "@" in an SQL command in VB
此外，为了避免可能的注入，不要将字段值直接传递给 SQL 命令。使用参数在VB中如何在SQL命令中使用参数“@”
回答by JB King
cmd.CommandText = "UPDATE Profile (FirstName, LastName, Email, Telephone, EmployeeID, NTLogin) VALUES ('" & lblFirstName.Text & "','" & lblLastName.Text & "','" & lblEmail.Text & "','" & lblPhone.Text & "','" & lblEID.Text & "','" & lblNT.Text & "') Where [EmployeeID] = '" & lblEID.text & "'"
Should be like this:
应该是这样的：
cmd.CommandText = "UPDATE Profile SET FirstName='"&lblFirstName.Text&"', LastName='"&lblLastName.Text&"', Email='"&lblEmail.Text&"', Telephone='"&lblPhone.Text&"', EmployeeID='"&lblEID.Text&"', NTLogin='"&lblNT.Text&"' Where [EmployeeID] = '" & lblEID.text & "'"
I would be careful about last names like "O'Connor" that may break with your SQL as there are reasons stored procedures or parameters are preferred in most cases to prevent SQL injection attacks.
我会小心像“O'Connor”这样的姓氏，它们可能会破坏您的 SQL，因为在大多数情况下，为了防止 SQL 注入攻击，存储过程或参数是首选的原因。
回答by 8675309
Thanks to everyone who provided some assistance. Between the examples and links shared, I was able to rebuild my command so that it update properly and is now setup using parametrized queries rather than a direct insert statement, or at least what appears correct to me, and is working.
感谢所有提供帮助的人。在共享的示例和链接之间，我能够重建我的命令，以便它正确更新，现在使用参数化查询而不是直接插入语句进行设置，或者至少在我看来是正确的，并且正在工作。
Below is my final code.
下面是我的最终代码。
Try
Dim con As New SqlConnection
Dim cmd As New SqlCommand
con.ConnectionString = "Data Source=server;Initial Catalog=database;Integrated Security=True"
con.Open()
cmd.Connection = con
cmd.CommandText = ("UPDATE Profile SET FirstName = @FirstName, LastName = @LastName, Email = @Email, Telephone = @Telephone, EmployeeID = @EmployeeID, NTLogin = @NTLogin, Organization = @Organization, Permission = @Permission Where [EmployeeID] = '" & eID & "'")
cmd.Parameters.Add("@FirstName", SqlDbType.VarChar).Value = lblFirstName.Text
cmd.Parameters.Add("@LastName", SqlDbType.VarChar).Value = lblLastName.Text
cmd.Parameters.Add("@Email", SqlDbType.VarChar).Value = lblEmail.Text
cmd.Parameters.Add("@Telephone", SqlDbType.VarChar).Value = lblPhone.Text
cmd.Parameters.Add("@EmployeeID", SqlDbType.VarChar).Value = lblEID.Text
cmd.Parameters.Add("@NTLogin", SqlDbType.VarChar).Value = lblNT.Text
cmd.Parameters.Add("@Organization", SqlDbType.VarChar).Value = lblOrg.Text
cmd.Parameters.Add("@Permission", SqlDbType.VarChar).Value = "requestor"
cmd.ExecuteNonQuery()
con.close
lblValid.Text = "Record Updated Successfully"
Catch ex As System.Exception
lblValid.Text = (ex.Message)
End Try
Thanks!
谢谢！
