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
– Mad Myche, Commented May 27, 2017 at 12:18

Steve · Accepted Answer · 2017-05-27 12:23:05Z

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, 2017 at 12:23

answered May 27, 2017 at 12:11

Steve

217k22 gold badges242 silver badges296 bronze badges

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

12 Comments

MiOnIs Over a year ago

First suggestion don't work. I will try with parameterized queries.

MiOnIs Over a year ago

Yeah I have the same error when I tried to put fields with space in square brackets

Steve Over a year ago

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

Szörényi Ádám Over a year ago

Isn't your ID generated by the db? Also you're passing it between single quotes, which indicates a string for sql server

Steve Over a year ago

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.

|

Collectives™ on Stack Overflow

OleDbException: Syntax error in INSERT INTO statement

1 Answer 1

12 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

12 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related