ADO.Net中的SQL更新语句 [英] SQL Update Statement in ADO.Net

问题描述

该SQL更新语句有什么问题?

我收到异常消息更新语句中的语法错误"，尽管所有代码行都可以，但是SQL Update语句也可以.

使用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?

I Received an Exception Message "Syntax Error in Update Statement", Although all the code lines are OK, also the SQL Update statement is OK

using c# and ADO.Net
database : Access

Problem Description
-----------------------------------------------------------------------------------------------------
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");

推荐答案

updateQuery语句中存在三个问题，

1. SartDateMonth必须为[开始日期月份]
2. monthComboBox.Text)必须为monthComboBox.Text
3.并且所有的多字列名称都需要像这样写，例如First Name => [名字]等等，请参阅下面的

There three problems in the updateQuery statement,

1. SartDateMonth needs to be [Start Date Month]
2. monthComboBox.Text) needs to be monthComboBox.Text
3. and all the multi words column name needs to be write like, for example First Name => [First Name] and so on, please see below,

UPDATE [Db2Growth].[dbo].[Register]
   SET [First Name] = ''
      ,[Sure Name] = ''
      ,[Age] = 0
      ,[ID] = 0
      ,[Tel] = 0
      ,[Gender] = ''
      ,[Country] = ''
      ,[Room Number] = 0
      ,[Room Type] = ''
      ,[Room Class] = ''
      ,[Number of Nights] = 0
      ,[Start Date Month] = ''
      ,[Start Date Day] = 0
      ,[Start Date Year] = 0
 WHERE ID=0
GO

希望对您有所帮助:)

hope it helps, :)

如果您想使代码FAR更具可读性和可支持性，建议您重写此字符串连接垃圾作为参数化查询，并使用Parameter对象填充所有空白.

有了一点知识，任何人都可以输入特制的名字"并完全破坏您的数据库.


阅读此 [

If you want to make your code FAR more readable and supportable, I suggest rewriting this string concatentation garbage as a parameterized query and use Parameter objects to fill in all the blanks.

With a little bit of knowledge, anyone could type in a specially crafted "first name" and utterly destroy your database.


Read this[^] to find out why and how.

这篇关于ADO.Net中的SQL更新语句的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！