Syntax Error in Sql Update Statement

Question

0.00/5 (No votes)

See more:

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

Posted 17-Jul-11 8:25am

Ibraheim Gaber

Updated 21-Jul-11 19:35pm

v4

Add a Solution

Comments

Wonde Tadesse 21-Jul-11 22:38pm

Use parameterized query to avoid SQL Injection.!

4 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)

Shameel · Accepted Answer · 2011-07-17T08:48:00

Solution 1

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:

SQL

Update Register Set [First Name] = 'xxxx',[Sure Name] = 'xxxx',Age = ,ID=,....

which is wrong.

Posted 17-Jul-11 8:48am

Shameel

Comments

Espen Harlinn 17-Jul-11 19:52pm

Ah, missed all of those, my 5 ...
I just looked for reasons why it wouldn't compile at all ...

thatraja 17-Jul-11 20:38pm

5!

walterhevedeich 17-Jul-11 22:20pm

Good point. 5.

Wonde Tadesse 21-Jul-11 22:36pm

5+

OriginalGriff · Accepted Answer · 2011-07-17T08:51:00

Solution 3

Spaces, first and foremost.
Put '[' and ']' around your field names:

string updateQuery = "Update Register Set [First Name] = '" + firstNameTextBox.Text + "',[Sure Name] = '..."

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

Posted 17-Jul-11 8:51am

OriginalGriff

Comments

Espen Harlinn 17-Jul-11 19:52pm

Right :)

thatraja 17-Jul-11 20:38pm

5!

walterhevedeich 17-Jul-11 22:21pm

5ed too.

Ibraheim Gaber 21-Jul-11 20:52pm

there is still error in the update statement

OriginalGriff 22-Jul-11 3:36am

And what does your code look like now? It is four days later, so I assume it is very different! :laugh:

Wonde Tadesse 21-Jul-11 22:35pm

5+

Espen Harlinn · Accepted Answer · 2011-07-17T08:49:00

SQL

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

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

Ibraheim Gaber · Accepted Answer · 2011-07-21T14:38:00

please review this code lines and reply as soon as possible

string updateQuery = "Update Register \nSet \n [First Name] = @FN ,[Sure Name] = @SN, [Age] = @AGE, [ID] = @ID , [Tel] = @TEL,[Gender] = @GEN, [Country] = @COUN,[Room Number] = @RN,[Room Type] = @RT,[Room Class] = @RC,[Number of Nights] = @NON,[Sart Date Month] = @SDM,[Start Date Day] = @SDD,[Start Date Year] = @SDY, Where [ID] = @UITB";

DataBaseOperations.COM = new OleDbCommand(updateQuery, DataBaseOperations.CON);
                                                                            
                                                                            DataBaseOperations.COM.Parameters.AddWithValue("@FN", firstNameTextBox.Text);
                                                                            DataBaseOperations.COM.Parameters.AddWithValue("@SN", sureNameTextBox.Text);
                                                                            DataBaseOperations.COM.Parameters.AddWithValue("@AGE", ageTextBox.Text);
                                                                            DataBaseOperations.COM.Parameters.AddWithValue("@ID", idTextBox.Text);
                                                                            DataBaseOperations.COM.Parameters.AddWithValue("@TEL", telTextBox.Text);
                                                                            DataBaseOperations.COM.Parameters.AddWithValue("@GEN", genderComboBox.Text);
                                                                            DataBaseOperations.COM.Parameters.AddWithValue("@COUN", countryComboBox.Text);
                                                                            DataBaseOperations.COM.Parameters.AddWithValue("@RN", roomNumberTextBox.Text);
                                                                            DataBaseOperations.COM.Parameters.AddWithValue("@RT", roomTypeComboBox.Text);
                                                                            DataBaseOperations.COM.Parameters.AddWithValue("@RC", roomClassComboBox.Text);
                                                                            DataBaseOperations.COM.Parameters.AddWithValue("@NON", noOfNightsTextBox.Text);
                                                                            DataBaseOperations.COM.Parameters.AddWithValue("@SDM", monthComboBox.Text);
                                                                            DataBaseOperations.COM.Parameters.AddWithValue("@SDD", dayComboBox.Text);
                                                                            DataBaseOperations.COM.Parameters.AddWithValue("@SDY", yearComboBox.Text);
                                                                            DataBaseOperations.COM.Parameters.AddWithValue("@UITB", updateIDTextBox.Text);

DataBaseOperations.CON.Open();
                                                                            DataBaseOperations.COM.ExecuteNonQuery();
                                                                            DataBaseOperations.CON.Close();

Syntax Error in Sql Update Statement

4 solutions

Solution 1

Solution 3

Solution 2

Solution 4

Add your solution here

Preview 0