SQL Update Statement and ADO.Net?

Question

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


Collapse

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
Collapse

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

Answer 1

You should really find a better way to format your SQL so it's more readable. This is a good example of why doing SQL by string splicing is a *bad* idea. Tough to read and maintain.



But this bit did jump out at me:

", Where ID ="



There shouldn't be a comma before Where. That is certainly an error. There may be others, it's very hard to read.



One thing that might help you, this code:

// MessageBox.Show(updateQuery, "Update Query");



If you hit CTRL-C while that message box is up, it will copy your query (and a few other things) to the clipboard.



You should then be able to paste it into a query window (access has an ad-hoc query window, right?) and tweak it until it runs.

Answer 2

Is the state of affairs something like this? a million) Order created (row inserted so as table) -> replace onOrder fee in inventory table 2) Order gained (row as much as date so as table) -> replace (shrink) onOrder and (advance) onHand values in inventory table if so, you'll be able to desire to try this by way of a pair of triggers against the orders table, one an insert set off and the different an replace set off (plus perhaps a 0.33 delete set off to handle cancelled orders.) yet another threat is thru a transaction as pronounced above.

Answer 3

Are you sure about the field name: SartDateMonth? Shouldn't it be StartDateMonth?