oledbexception was unhandled syntax error in insert into statement

Question

I can't seem to get were I am making the mistake.

        OleDbCommand command = new OleDbCommand();

        command.Connection = connection;
        command.CommandText = "INSERT INTO statement workers values(?,?,?,?,?,?,?,?,?)";
        command.Parameters.AddWithValue("@Tab", tabtextBox1.Text);
        command.Parameters.AddWithValue("@User", usertextBox2.Text);
        command.Parameters.AddWithValue("@Pass", passtextBox4.Text);
        command.Parameters.AddWithValue("@Names", namestextBox3.Text);
        command.Parameters.AddWithValue("@Tele", teletextBox6.Text);
        command.Parameters.AddWithValue("@Job", jobtextBox9.Text);
        command.Parameters.AddWithValue("@Pay", paytextBox7.Text);
        command.Parameters.AddWithValue("@Date", dateofdateTimePicker2.Value.ToShortDateString());
        command.Parameters.AddWithValue("@DOB", dobdateTimePicker1.Value.ToShortDateString());
        command.ExecuteNonQuery();


        MessageBox.Show("Data Saved");

after I try to insert details it giving me the response:

oledbexception was unhandled syntax error in insert into statement

What is the actual name of your table? Is it "statement workers" or is it just 'workers"? — Gord Thompson, Mar 30 '16 at 14:39

score 4 · Accepted Answer · edited May 23 '17 at 12:15

When a table name or column name contains a space or it is a reserved word you need to enclose that name in square brackets to avoid confusing the sql parser called to interpret your command.
In your context you should write

command.CommandText = "INSERT INTO [statement workers] values(?,?,?,?,?,?,?,?,?)";

EDIT
After looking at your field's name (and assuming that the order of fields is exactly as you have typed them) then you should change the order in which you add your parameters to match exactly the order of the fields in your table.
OleDb cannot use named placeholders to setup the parameter collection and thus, it is important to strictly follow the field order.
If you don't do it then a value could end up in a different field and create problems like when you try to store a decimal value in a date field. (Of course if you add the column names after the table name the order could be changed)

command.Connection = connection;
command.CommandText = "INSERT INTO statement workers values(?,?,?,?,?,?,?,?,?)";
command.Parameters.Add("@Tab", OleDbType.Integer).Value = Convert.ToInt32( tabtextBox1.Text);
command.Parameters.Add("@User", OleDbType.VarWChar).Value = usertextBox2.Text;
command.Parameters.Add("@Pass", OleDbType.VarWChar).Value = passtextBox4.Text;
command.Parameters.Add("@Names", OleDbType.VarWChar).Value = namestextBox3.Text;
command.Parameters.Add("@Tele", OleDbType.Integer).Value = Convert.ToInt32(teletextBox6.Text);
command.Parameters.Add("@Job", OleDbType.VarWChar).Value = jobtextBox9.Text;
command.Parameters.Add("@Date", OleDbType.Date).Value = dateofdateTimePicker2.Value;
command.Parameters.Add("@DOB", OleDbType.Date).Value = dateTimePicker1.Value);
command.Parameters.Add("@Pay", OleDbType.Decimal).Value = Convert.ToDecimal(paytextBox7.Text);
command.ExecuteNonQuery();

In this way you set the values in the exact order expected by the table fields.
Notice that I have removed the dangerous AddWithValue with a more precise Add followed by the datatype expected by the field.
AddWithValue cannot know what is the type expected by the database and thus looks at the type of the parameter. Since you pass all strings then the database engine is forced to convert them back to the expected field type.
Sometime (in particular with dates and decimal fields) this doesn't work correctly.

Side notes:

Telephone numbers should not be stored as numbers, they are not.
From a security point of view, a password should never be stored in clear text.There are many articles that explain how this is dangerous and how to hash a password. You could start your search from here: Best way to store password in database

Is it the same Syntax error or is different now? Are you sure you have a table with that exact name "statement workers"? Keep in mind that if you don't list the columns after the table name you need to supply every field in the parameter list and this list should be in the exact order in which the fields appear in your table — Steve, Mar 30 '16 at 13:11
the table name is [statement workers], its still giving the same error — kgreat, Mar 30 '16 at 16:20
It is strange, could you show all the field names in the table and their type? — Steve, Mar 30 '16 at 17:15
Tab No - Number, username - short text, password - short text, Full Names - short text, Telephone - Number, Job Des - Short text, Date of hire - Date/time, DOB - Date/time, pay - currency. — kgreat, Mar 30 '16 at 17:39
Put the parameters in the exact order in which they should set the field names. And change the AddWithValue to a more precise Add. Will update the answer — Steve, Mar 30 '16 at 17:47

Gustav · Answer 2 · 2016-03-30T13:55:02.610

1

Also, you must use the values of the DateTimePickers, not strings:

command.Parameters.AddWithValue("@Date", dateofdateTimePicker2.Value);
command.Parameters.AddWithValue("@DOB", dobdateTimePicker1.Value);

and to state the field names:

command.CommandText = "INSERT INTO [statement workers] (fielname1, fieldname2, .., fieldname9) values(?,?,?,?,?,?,?,?,?)";

edited Mar 30 '16 at 13:55

answered Mar 30 '16 at 12:49

Gustav

53,498
7
29
55

i have done it but i am still getting the same error – kgreat Mar 30 '16 at 12:59
You may also have to spell out the field names. See edit. – Gustav Mar 30 '16 at 13:55
its giving the same error i have counter checked on the spelllings – kgreat Mar 30 '16 at 18:02

oledbexception was unhandled syntax error in insert into statement

2 Answers2