SQL Update Statement in ADO.Net

Question

0.00/5 (No votes)

See more:

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

Posted 22-Jul-11 12:53pm

Ibraheim Gaber

Updated 22-Jul-11 13:03pm

v2

Add a Solution

Comments

Mycroft Holmes 22-Jul-11 20:48pm

Besides taking Daves advice and using parameterised queries I would change the field names to eliminate the spaces First Name should be changed to FirstName etc. This is the root cause of one of your problem and should be fixed in the table design.

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Mohammad A Rahman · Answer 1 · 2011-07-22T13:13:00

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,

SQL

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, :)

Dave Kreskowiak · Answer 2 · 2011-07-22T13:42:00

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.

SQL Update Statement in ADO.Net

2 solutions

Solution 1

Solution 2

Add your solution here

Preview 0