Problems on Insert and Update Statement

[ebabes]

Please help me solve these errors that I usually receive. I’ve created an application that reads, updates, and inserts a record into an Access database. But every time I press the Update button to update a record, I usually receive this error message “Syntax error in UPDATE statement”.

On the other hand, when I also intend to press the Save New button to insert a new record in the database, I usually receive this error message “Syntax error in INSERT INTO statement.”

I also found that when I update or insert only one field of a database, the given code works. But when I update multiple fields that's the time I experience such errors.

My Access database compose of the following field names: IDNo, Course, Age, Year (all declared as TEXT). The following are the selected codes of my application that experiences the error:

[code]
private void btnUpdate_Click(object sender, EventArgs e)
{
string ConnectionString = @"Provider=Microsoft.Jet.OLEDB.4.0; Data Source=c:\EBabes\DataTesting.mdb";
OleDbConnection conn = new OleDbConnection(ConnectionString);
OleDbCommand cmd = new OleDbCommand("UPDATE StudTable SET Course=?, Year=?, Age=? WHERE IDNo = ?", conn);
cmd.Parameters.Add("@Course", OleDbType.VarChar).Value = txtCourse.Text;
cmd.Parameters.Add("@Year", OleDbType.VarChar).Value = txtYear.Text;
cmd.Parameters.Add("@Age", OleDbType.VarChar).Value = txtAge.Text;
cmd.Parameters.Add("@IDNo", OleDbType.VarChar).Value = txtID.Text;

conn.Open();
cmd.ExecuteNonQuery();
conn.Close();

clearForm();
MessageBox.Show("Record updated!");
}

private void btnSaveNew_Click(object sender, EventArgs e)
{
string ConnectionString = @"Provider=Microsoft.Jet.OLEDB.4.0; Data Source=C:\EBabes\DataTesting.mdb";
OleDbConnection conn = new OleDbConnection(ConnectionString);
OleDbCommand cmd = new OleDbCommand("INSERT INTO StudTable (IDNo, Course, Year, Age) VALUES (?, ?, ?, ?),conn");
cmd.Parameters.Add("@IDNo", OleDbType.VarChar).Value = txtID.Text;
cmd.Parameters.Add("@Course", OleDbType.VarChar).Value = txtCourse.Text;
cmd.Parameters.Add("@Year", OleDbType.VarChar).Value = txtYear.Text;
cmd.Parameters.Add("@Age", OleDbType.VarChar).Value = txtAge.Text;

conn.Open();
cmd.ExecuteNonQuery();
conn.Close();

clearForm();
MessageBox.Show("Record updated!");

[hollystyles]

ebabes please use code tags to format your code in posts. (See the watermark in the quick reply box at the botom of all threads)

OleDbCommand cmd = new OleDbCommand("INSERT INTO StudTable (IDNo, Course, Year, Age) VALUES (?, ?, ?, ?),conn");

You have misplaced your closing " it should be here:

OleDbCommand cmd = new OleDbCommand("INSERT INTO StudTable (IDNo, Course, Year, Age) VALUES (?, ?, ?, ?)",conn);

[ebabes]

Thank you HollyStyles! What about my problem on Update button. Using the same code I receive the message “Syntax error in UPDATE statement”.

Thanks a lot!

[hollystyles]

Year is a reserved word surround it in square brackets:

OleDbCommand cmd = new OleDbCommand("UPDATE StudTable SET Course=?, [Year]=?, Age=? WHERE IDNo = ?", conn);

You probably need to do that in the INSERT statement too.

OleDbCommand cmd = new OleDbCommand("INSERT INTO StudTable (IDNo, Course, [Year], Age) VALUES (?, ?, ?, ?)",conn);

[ebabes]

Thank you very much. My code is already working. It's a big help!Thanks guys....

[Evox]

Hello,i got a problem.

When i update,the database ,i got this error code:Additional information: Data type mismatch in criteria expression. But i can to the database insert evry data. What's wrong?

Thanks For help!!

//read button to datagrid
private: System::Void button1_Click(System::Object^  sender, System::EventArgs^  e) {
				 //adatbázis megnyitás
				 db = gcnew OleDbConnection(" Provider=Microsoft.Jet.OLEDB.4.0;Data Source=database.hdb");
				 try
					{
						db->Open();
						
						
		OleDbCommand ^ dbc = gcnew OleDbCommand();
						
		
						OleDbDataAdapter ^ dba = gcnew OleDbDataAdapter(L"SELECT * FROM games", db);
					
				
						dbt = gcnew Data::DataTable();
						dba->Fill(dbt);
							
						
						this->dataGridView1->DataSource = dbt;
					
					}
					catch (OleDbException ^ pe)
					{
					  MessageBox::Show(pe->Message);
					}


			 }
//Save button
	private: System::Void button2_Click(System::Object^  sender, System::EventArgs^  e) {
		
	    OleDbDataAdapter ^ dba = gcnew OleDbDataAdapter("SELECT * FROM games", db);

		dba->UpdateCommand  = gcnew OleDbCommand("UPDATE games SET name = ?,size = ?,type = ?,[date] = ?,language = ?,disc = ?,misc = ? WHERE id = ?",db);
		dba->UpdateCommand->Parameters->Add("@id", OleDbType::Integer,2,"id");
		dba->UpdateCommand->Parameters->Add("@name", OleDbType::VarChar, 250, "name");
		dba->UpdateCommand->Parameters->Add("@size", OleDbType::VarChar, 40, "size");
		dba->UpdateCommand->Parameters->Add("@type", OleDbType::VarChar, 40, "type");
		dba->UpdateCommand->Parameters->Add("@date", OleDbType::Date ,4, "date");
		dba->UpdateCommand->Parameters->Add("@language", OleDbType::VarChar, 40, "language");
		dba->UpdateCommand->Parameters->Add("@disc", OleDbType::VarChar, 250, "disc");
		dba->UpdateCommand->Parameters->Add("@misc", OleDbType::Integer, 2, "misc");


		dba->InsertCommand = gcnew OleDbCommand("INSERT INTO games (id,name,size,type,date,language,disc,misc) VALUES ( @id,@name,@size,@type,@date,@language,@disc,@misc)",db);
		dba->InsertCommand->Parameters->Add("@id", OleDbType::Integer,2, "id");
		dba->InsertCommand->Parameters->Add("@name", OleDbType::VarChar, 255, "name");
		dba->InsertCommand->Parameters->Add("@size", OleDbType::VarChar, 40, "size");
		dba->InsertCommand->Parameters->Add("@type", OleDbType::VarChar, 40, "type");
		dba->InsertCommand->Parameters->Add("@date", OleDbType::Date,7, "date");
		dba->InsertCommand->Parameters->Add("@language", OleDbType::VarChar, 40, "language");
		dba->InsertCommand->Parameters->Add("@disc", OleDbType::VarChar, 255, "disc");
		dba->InsertCommand->Parameters->Add("@misc", OleDbType::Integer, 2, "misc");

		dba->DeleteCommand  = gcnew OleDbCommand("DELETE FROM games WHERE id = @id",db);
		dba->DeleteCommand->Parameters->Add("@id", OleDbType::Integer,2, "id");
		dba->DeleteCommand->Parameters->Add("@name", OleDbType::VarChar,255, "name");
		dba->DeleteCommand->Parameters->Add("@size", OleDbType::VarChar,40, "size");
		dba->DeleteCommand->Parameters->Add("@type", OleDbType::VarChar,40, "type");
		dba->DeleteCommand->Parameters->Add("@date", OleDbType::Date,7, "date");
		dba->DeleteCommand->Parameters->Add("@language", OleDbType::VarChar, 40, "language");
		dba->DeleteCommand->Parameters->Add("@disc", OleDbType::VarChar, 255, "disc");
		dba->DeleteCommand->Parameters->Add("@misc", OleDbType::Integer, 2, "misc");

		
		dba->Update(dbt); 
			 }