Click here to Skip to main content
15,562,816 members
Please Sign up or sign in to vote.
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
Updated 21-Jul-11 20:35pm
v4
Comments
Wonde Tadesse 21-Jul-11 22:38pm    
Use parameterized query to avoid SQL Injection.!

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.
 
Share this answer
 
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+
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();
 
Share this answer
 
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:
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
 
Share this answer
 
Comments
thatraja 17-Jul-11 20:38pm    
5!
Espen Harlinn 18-Jul-11 5:38am    
Thank you, thatraja!
walterhevedeich 17-Jul-11 22:21pm    
5ed too!
Espen Harlinn 18-Jul-11 5:38am    
Thank you, Walter!
Wonde Tadesse 21-Jul-11 22:37pm    
5+
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();
 
Share this answer
 
Comments
Christian Graus 21-Jul-11 20:50pm    
Please don't use 'answer' to ask questions, or tell us how quickly to give you free help because you have no idea how to do your job.

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



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900