Sql Update语句中的语法错误 [英] Syntax Error in Sql Update Statement
问题描述
该SQL更新语句有什么问题?
使用c#和ADO.Net
数据库:访问权限
-------------------------------------------------- -------------------------------------------------- -
我正在使用一个名为HotelDataBase.MDB的访问数据库以及一个名为Register的表，该表中的14列分别为
名字[文字]，确定名称[文字]，年龄[Int]，ID [Int]，电话[Int]，性别[Text]，国家[Text]，房间号[Int]，房间类型[Text]，房间类[文本]，夜数[Int]，开始日期月份[Text]，开始日期日[Int]，开始日期年份[Int]
我有一个表单，其中有一个名为"updateIDTextBox"的文本框和搜索按钮，用于在该文本框中搜索指定的ID.
如果id存在，则所有数据将以其可编辑位置的形式显示为
名字:名字------------姓氏:MyName
年龄:22 ------------ ID:1320065等...
然后可以更改这些数据中的任何一个，然后用户单击保存"按钮以在搜索文本框中更新指定ID的数据
更新语句为
What''s wrong in that SQL update statement?
using c# and ADO.Net
database : Access
-----------------------------------------------------------------------------------------------------
I am using an access data base called HotelDataBase.MDB with a table called Register in which 14-columns that are respectively
First Name [Text], Sure Name [Text], Age [Int], ID [Int], Tel [Int], Gender [Text], Country [Text], Room Number [Int], Room Type [Text], Room Class [Text], Number of Nights [Int], Start Date Month [Text], Start Date Day [Int], Start Date Year [Int]
and I''ve a Form in which a text box called "updateIDTextBox" and Search Button to search for the specified id in that Text Box.
if the id exists, all data will be displayed in their editable places in the form as
First Name : Name------------Last Name : MyName
Age : 22 ------------ID : 1320065 and So On...
Then any of these data can be changed then the user clicks on the Save Button to Update data of the specified id in the search text box
the update statement is
string updateQuery = "Update Register Set First Name = ''" + firstNameTextBox.Text + "'',Sure Name = ''" + sureNameTextBox.Text + "'',Age = " + ageTextBox.Text + ",ID = " + idTextBox.Text + ",Tel = " + telTextBox.Text + ",Gender = ''" + genderComboBox.Text + "'',Country = ''" + countryComboBox.Text + "'',Room Number = " + roomNumberTextBox.Text + ",Room Type = ''" + roomTypeComboBox.Text + "'',Room Class = ''" + roomClassComboBox.Text + "'',Number of Nights = " + noOfNightsTextBox.Text + ",Sart Date Month = ''" + monthComboBox.Text) + "'',Start Date Day = " + dayComboBox.Text + ",Start Date Year = " + yearComboBox.Text + " Where ID = " + updateIDTextBox.Text + ";";
不幸的是，我在更新语句中收到语法错误
这是保存按钮的代码
Unfortunately i received a syntax error in the update statement
this is the code of Save Button
/*
* all recored are checked
* all inputs are valid
* now update data to the database
* open connection to database
* build commands, send it to database
* save updated data to database
* close connection of database
*/
//the connection
DataBaseOperations.CON = new OleDbConnection(DataBaseOperations.CONNECTION);
string updateQuery = "Update Register Set FirstName = ''" + firstNameTextBox.Text + "'',SureName = ''" + sureNameTextBox.Text + "'',Age = " + ageTextBox.Text + ",ID = " + idTextBox.Text + ",Tel = " + telTextBox.Text + ",Gender = ''" + genderComboBox.Text + "'',Country = ''" + countryComboBox.Text + "'',RoomNumber = " + roomNumberTextBox.Text + ",RoomType = ''" + roomTypeComboBox.Text + "'',RoomClass = ''" + roomClassComboBox.Text + "'',NumberofNights = " + noOfNightsTextBox.Text + ",SartDateMonth = ''" + monthComboBox.Text + "'',StartDateDay = " + dayComboBox.Text + ",StartDateYear = " + yearComboBox.Text + ", Where ID = " + updateIDTextBox.Text + ";";
// MessageBox.Show(updateQuery, "Update Query");
DataBaseOperations.COM = new OleDbCommand(updateQuery, DataBaseOperations.CON);
DataBaseOperations.CON.Open();
DataBaseOperations.COM.ExecuteNonQuery();
DataBaseOperations.CON.Close();
clearAllFieldsButton.Enabled = true;
MessageBox.Show("Update Process of Habitant : " + firstNameTextBox.Text.ToString() + " " + sureNameTextBox.Text.ToString() + "\nHas Been Done Successfully", "Successful Update");
推荐答案
这里有两个问题.第一个是其中包含空格的列名称，例如"First Name"列.您可能必须将此类名称括在方括号中.第二个问题是，如果任何数字文本框为空，则会出现此错误.例如，如果ageTextBox.Text返回一个空字符串，则您的查询将变为无效，如下所示:
There are two problems here. The first one is column names with a space in them, for example, ''First Name'' column. You probably have to enclose such names in a square brackets. The second problem is, if any of the numberic text box is empty, you can get this error. For example, if ageTextBox.Text return an empty string, your query becomes invalid, like this:
Update Register Set [First Name] = 'xxxx',[Sure Name] = 'xxxx',Age = ,ID=,....，这是错误的.
首先是空格.
在您的字段名称周围加上"["和]":
Spaces, first and foremost.
Put ''['' and '']'' around your field names:
string updateQuery = "Update Register Set [First Name] = ''" + firstNameTextBox.Text + "'',[Sure Name] = ''..."
然后，在有人意外或故意破坏您的数据库之前，阅读SQL注入攻击并将其更改为参数化查询:
Then read up on SQL Injection Attacks and change it to Parametrized queries before someone accidentally or deliberately destroys your database:
string updateQuery = "Update Register Set [First Name] = @FN, [Sure Name] = @SN...";
com = new OleDbCommand(updateQuery, con);
com.Parameters.AddWithValue("@FN", firstNameTextBox.Text);
com.Parameters.AddWithValue("@SN", sureNameTextBox.Text);
...
con.Open();
com.ExecuteNonQuery();
con.Close();
string updateQuery = "Update Register Set First Name = '" + firstNameTextBox.Text + "',Sure Name = '" + sureNameTextBox.Text + "',Age = " + ageTextBox.Text + ",ID = " + idTextBox.Text + ",Tel = " + telTextBox.Text + ",Gender = '" + genderComboBox.Text + "',Country = '" + countryComboBox.Text + "',Room Number = " + roomNumberTextBox.Text + ",Room Type = '" + roomTypeComboBox.Text + "',Room Class = '" + roomClassComboBox.Text + "',Number of Nights = " + noOfNightsTextBox.Text + ",\nSart Date Month = '" + monthComboBox.Text) + "',Start Date Day = " + dayComboBox.Text + ",Start Date Year = " + yearComboBox.Text + " Where ID = " + updateIDTextBox.Text + ";";
看起来不正确:",\nSart Date Month = ''" + monthComboBox.Text) + "，尤其是右括号
除此之外，我建议您使用OleDbCommand的Parameters集合.实际上，您的代码容易受到SQL注入的影响.
最好的问候
Espen Harlinn
This doesn''t look right: ",\nSart Date Month = ''" + monthComboBox.Text) + ", especially the right parenthesis
Apart from that I''d advise you to use the Parameters collection of the OleDbCommand. As it is, your code is vulnerable to SQL injection.
Best regards
Espen Harlinn
这篇关于Sql Update语句中的语法错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
