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
– Gord Thompson, Commented Mar 30, 2016 at 14:39

Community · Accepted Answer · 2017-05-23 12:15:33Z

4

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

edited May 23, 2017 at 12:15

CommunityBot

11 silver badge

answered Mar 30, 2016 at 12:07

Steve

217k22 gold badges242 silver badges296 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

kgreat Over a year ago

i have tried it but i am still getting the same error

Steve Over a year ago

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

kgreat Over a year ago

the table name is [statement workers], its still giving the same error

Steve Over a year ago

It is strange, could you show all the field names in the table and their type?

kgreat Over a year ago

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.

|

Gustav · Accepted Answer · 2016-03-30 13:55:02Z

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, 2016 at 13:55

answered Mar 30, 2016 at 12:49

Gustav

56.3k7 gold badges33 silver badges65 bronze badges

3 Comments

kgreat Over a year ago

i have done it but i am still getting the same error

Gustav Over a year ago

You may also have to spell out the field names. See edit.

kgreat Over a year ago

its giving the same error i have counter checked on the spelllings

Collectives™ on Stack Overflow

oledbexception was unhandled syntax error in insert into statement

2 Answers 2

7 Comments

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

7 Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related