OleDbException: Syntax error in INSERT INTO statement

Question

I got OleDbException: Syntax error in INSERT INTO statement. I think that my INSERT INTO statement is good. Parameters have good data type so it's not problem. Does someone maybe know what's the problem?

        OleDbCommand command = new OleDbCommand();
        command.Connection = conn;
        command.CommandType = CommandType.Text;
        command.CommandText = String.Format("INSERT INTO Employees" +
            " (ID, Company, Last Name, First Name, E-mail Address, Job Title, Business Phone, Home Phone" +
            ", Mobile Phone, Fax NUmber, Address, City, State/Province, ZIP/Postal Code, Country/Region, Web Page, Notes)" +
            " Values ('{0}', '{1}','{2}','{3}','{4}','{5}'," +
            "'{6}','{7}','{8}','{9}','{10}','{11}','{12}','{13}','{14}','{15}','{16}')", iD,kompanija,prezime,ime,email,
           zvanje,busTelefon,telefon,mobTelefon,fax,adresa,grad,okrug,postanskiBroj,zemlja,web,beleska);               zvanje,busTelefon,telefon,mobTelefon,fax,adresa,grad,okrug,postanskiBroj,zemlja,web,beleska);

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

Error message:

UDATE SQL:

        OleDbCommand command = new OleDbCommand();
        command.Connection = conn;
        command.CommandType = CommandType.Text;
        string cmdText = String.Format(@"UPDATE TABLE Employees " +
                        "SET" +
                        " Company='" + kompanija + "'," +
                        " [Last Name]='" + prezime + "'," +
                        " [First Name]='" + ime + "'," +
                        " [E-mail Address]='" + email + "' ," +
                        " [Job Title]='" + zvanje +"'," +
                        " [Business Phone]='" + busTelefon + "'," +
                        " [Home Phone]='" + telefon + "'," +
                        " [Mobile Phone]='" + mobTelefon + "'," +
                        " [Fax Number]='" + fax + "'," +
                        " Address='" + adresa + "'," +
                        " City='" + grad + "'," +
                        " [State/Province]='" + okrug + "'," +
                        " [ZIP/Postal Code]='" + postanskiBroj + "'," +
                        " [Country/Region]='" + zemlja + "'," +
                        " [Web Page]='" + web + "'," +
                        " Notes='" + beleska + "' WHERE ID="+iD);
        command.CommandText = cmdText;

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

And this SQL don't work. The same error like previous.

What type of DB is this? What is displayed when you click on the "view Details" of that image? — Mad Myche, May 27 '17 at 12:18

Steve · Accepted Answer · 2017-05-27T12:23:05.540

2

When your fields names contain a space or other misleadings characters like the / (division operator) you need them to be enclosed in square brackets

 string cmdText = @"INSERT INTO Employees
                  (ID, Company, [Last Name], [First Name], [E-mail Address],
                   .., [State/Province], ....) VALUES (....)";

Also you are not using parameters in your query. String.Format is just another type of string concatenation that cannot protect you by invalid inputs (for example, try to use a single quote in your lastname value) and cannot save your code from Sql Injection vulnerability.

You should always use parameterized queries

string cmdText = @"INSERT INTO Employees ( your_field_list_comma_sep)
                  VALUES (@id, @company, @lastname, @firstname, 
                  ......)";

OleDbCommand cmd = new OleDbCommand(cmdText, conn);
cmd.Parameters.Add("@id", OleDbType.Integer).Value = iD;
cmd.Parameters.Add("@company", OleDbType.VarWChar).Value = kompanija;
cmd.Parameters.Add("@lastname", OleDbType.VarWChar).Value = prezime;
cmd.Parameters.Add("@firstname", OleDbType.VarWChar).Value = ime;
....
// add all the other parameters with their name and type
....
cmd.ExecuteNonQuery();

edited May 27 '17 at 12:23

answered May 27 '17 at 12:11

Steve

213,761
22
232
286

First suggestion don't work. I will try with parameterized queries. – MiOnIs May 27 '17 at 12:17
Yeah I have the same error when I tried to put fields with space in square brackets – MiOnIs May 27 '17 at 12:21
You should put the square brackets also around the [State/Province] otherwise the / is considered the division operator, Not quite right in an INSERT statement – Steve May 27 '17 at 12:21
Isn't your ID generated by the db? Also you're passing it between single quotes, which indicates a string for sql server – Szörényi Ádám May 27 '17 at 12:22
The first suggestion should work at least as a suggestion; knowing the DB type would be helpful – Mad Myche May 27 '17 at 12:22
well ID is int, which I add it's not generated in DB, I count all records in DB Table and increase it with 1 and that number add to next record. – MiOnIs May 27 '17 at 12:25
@MiOnIs please consider that you should declare correctly the parameters both in the name part and in the Type part. The parameter type should match the database field type otherwise you can get other errors or bad input saved in the database (dates and decimal for example are easy to mangle) – Steve May 27 '17 at 12:26
Yeah Steve, your suggestion work. I didn't put State/Province in square brackets. So I resolve that and it work now. And thank you for suggestion with parameterized queries I will use it in futur. Thank you – MiOnIs May 27 '17 at 12:27
Well, it doesn't take much time to fix. And you are still on thin ice without parameters. As suggested. Try to insert a lastname containing a single quote like _O'Hare_ without using parameters – Steve May 27 '17 at 12:31
All string fields require single quotes around the value. – Steve May 27 '17 at 14:18
That's no problem. :/ – MiOnIs May 27 '17 at 14:23
1

I can't see any syntax error in your update code. But remember, if any of your inputs data contains a single quote you are doomed with another syntax error. – Steve May 27 '17 at 15:17

OleDbException: Syntax error in INSERT INTO statement

1 Answers1