I'm writing an application which stores user information. Currently the user is supposed to update their Name, Height, Weight and Birthday.
我正在编写一个存储用户信息的应用程序。目前,用户应该更新他们的姓名,身高,体重和生日。
string height = TB_ClientHeight.Text;
string weight = TB_ClientWeight.Text;
string name = TB_ClientName.Text;
string bday = dateTimePicker1.Value.ToString("dd-MM-yyyy");
int heightint = Convert.ToInt32(height);
int weightint = Convert.ToInt32(weight);
It's updated by calling the public static string username variable from another form and using that as the WHERE UserName = @username.
它通过从另一个表单调用公共静态字符串用户名变量并使用它作为WHERE UserName = @username来更新。
usernamestringo = Login.usernameFromLogin;
I've followed other SO answers in this context and corrected some issues (like preventing SQL Injection). However I'm still getting a syntax error while updating these fields as claimed by OleDbException.
我在这种情况下跟踪了其他SO答案,并纠正了一些问题(比如防止SQL注入)。但是,当OleDbException声明更新这些字段时,我仍然会收到语法错误。
using (OleDbConnection myCon = new OleDbConnection(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=O:\Repos\Database\Database.accdb;Persist Security Info=False"))
using (OleDbCommand cmd = new OleDbCommand())
{
cmd.CommandType = CommandType.Text;
string query = "UPDATE TPersons SET Name=@Name, SET Height=@Height, SET Weight=@Weight, SET Bday=@Bday " + " WHERE FirstName= @username";
cmd.CommandText = query;
cmd.Parameters.AddWithValue("@Name", name.ToString());
cmd.Parameters.AddWithValue("@Height", heightint.ToString());
cmd.Parameters.AddWithValue("@Weight", weightint.ToString());
cmd.Parameters.AddWithValue("@Bday", bday.ToString());
cmd.Parameters.AddWithValue("@username", usernamestringo);
cmd.Connection = myCon;
myCon.Open();
cmd.ExecuteNonQuery();
MessageBox.Show("Updated!", "Success", MessageBoxButtons.OK, MessageBoxIcon.Information);
cmd.Parameters.Clear();
}
The OleDbException is:
OleDbException是:
Index #0 NativeError: -526847407 Source: Microsoft Access Database Engine SQLState: 3000 Description (message): Syntax error in UPDATE statement.
索引#0 NativeError:-526847407源:Microsoft Access数据库引擎SQLState:3000说明(消息):UPDATE语句中的语法错误。
Could anyone guide me where my syntax is wrong? Thank you!
任何人都可以指导我的语法错误吗?谢谢!
1 个解决方案
#1
The UPDATE syntax is
UPDATE语法是
UPDATE <tablename> SET field1=Value1, field2=Value2 WHERE primarykeyname=Value3
The SET keyword precedes only the first column to update, and you have another problem with the NAME column. In Access this is a reserved keyword. Use brackets around that column name (or better change it to something not so troublesome)
SET关键字仅在要更新的第一列之前,并且您对NAME列有另一个问题。在Access中,这是一个保留关键字。在该列名称周围使用括号(或者更好地将其更改为不那么麻烦的东西)
So:
string query = @"UPDATE TPersons SET [Name]=@Name,
Height=@Height, Weight=@Weight, Bday=@Bday
WHERE FirstName= @username";
Not strictly related to your current problem, but you should look also at this article Can we stop using AddWithValue already? The DbCommand.AddWithValue is a shortcut with numerous drawbacks. Better avoid it.
与您当前的问题并不严格相关,但您也应该看看本文我们是否已经停止使用AddWithValue了? DbCommand.AddWithValue是一个有许多缺点的快捷方式。更好地避免它。
#1
The UPDATE syntax is
UPDATE语法是
UPDATE <tablename> SET field1=Value1, field2=Value2 WHERE primarykeyname=Value3
The SET keyword precedes only the first column to update, and you have another problem with the NAME column. In Access this is a reserved keyword. Use brackets around that column name (or better change it to something not so troublesome)
SET关键字仅在要更新的第一列之前,并且您对NAME列有另一个问题。在Access中,这是一个保留关键字。在该列名称周围使用括号(或者更好地将其更改为不那么麻烦的东西)
So:
string query = @"UPDATE TPersons SET [Name]=@Name,
Height=@Height, Weight=@Weight, Bday=@Bday
WHERE FirstName= @username";
Not strictly related to your current problem, but you should look also at this article Can we stop using AddWithValue already? The DbCommand.AddWithValue is a shortcut with numerous drawbacks. Better avoid it.
与您当前的问题并不严格相关,但您也应该看看本文我们是否已经停止使用AddWithValue了? DbCommand.AddWithValue是一个有许多缺点的快捷方式。更好地避免它。