"Syntax error in INSERT INTO statement". Why?

Question

My code is below. I have a method where I pass in three parameters and they get written out to an MS Access database table. However, I keep getting a syntax error message. Can anyone tell me why? I got this example from the internet.

        private static void insertRecord(string day, int hour, int loadKW)
    {
        string connString = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\LoadForecastDB.accdb";
        OleDbConnection conn = new OleDbConnection(connString);

        string ins = @"INSERT INTO Forecasts (Day, Hour, Load) VALUES (?,?,?)";

        OleDbCommand cmd = new OleDbCommand(ins, conn);

        cmd.Parameters.Add("@day", OleDbType.VarChar).Value = day;
        cmd.Parameters.Add("@hour", OleDbType.Integer).Value = hour;
        cmd.Parameters.Add("@load", OleDbType.Integer).Value = loadKW;

        conn.Open();

        try
        {
            int count = cmd.ExecuteNonQuery();
        }
        catch (OleDbException ex)
        {
            Console.WriteLine(ex.Message);
        }
        finally
        {
            conn.Close();
        }
    }

Do you get the syntax error at compile time or execution time? Can you provide any more information about the error? — Marcelo Cantos
– Marcelo Cantos, Commented Apr 7, 2010 at 10:31

user254875486 · Accepted Answer · 2010-04-07 10:31:37Z

7

I think this could be because your column names (Day and Hour) are also keywords. Maybe you can put ` (inverted single quotes) around them (that works in MySQL, don't know about MS Access)

answered Apr 7, 2010 at 10:31

user254875486

11.3k7 gold badges39 silver badges65 bronze badges

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

3 Comments

Kevin Over a year ago

Hmm. Day and Hour are keywords, eh? That may be the root of my problem. I'll change the field names in the file, and change my 'insert into' and see what happens. Thanks!

user254875486 Over a year ago

I'm having second thoughts about it, could be that they aren't keywords, but it was the first thing I could think of. It's worth trying to find out, let me know what happens.

Kevin Over a year ago

You were right! I changed Day and Hour to ForecastDay and ForecastHour in the file and the program and re-ran the program. It worked perfectly! Good eye! Thanks so much!

Klaus Byskov Pedersen · Accepted Answer · 2010-04-07 10:30:32Z

2

Try changing

string ins = @"INSERT INTO Forecasts (Day, Hour, Load) VALUES (?,?,?)";

To:

string ins = @"INSERT INTO Forecasts ([Day], [Hour], [Load]) VALUES (@day, @hour, @load)";

answered Apr 7, 2010 at 10:30

Klaus Byskov Pedersen

122k31 gold badges192 silver badges222 bronze badges

4 Comments

wRAR Over a year ago

I think OLE DB parameters need ?, not @-prefixed names.

Kevin Over a year ago

Good idea! I'll change it now and see what happens. Thank you!

Kevin Over a year ago

Yes, I did read somewhere that OLE DB parms required ? instead of 'named' parms. But, I'll try anything at this point.

Klaus Byskov Pedersen Over a year ago

@wRAR you might be right, however I read somewhere that you can use named params. Another point of my answer is to escape the names, Day, Hour and Load (using []).

Juha Syrjälä · Accepted Answer · 2010-04-07 10:32:30Z

0

Another option might be to refer bind variables with numbers:

    cmd.Parameters.Add(1, OleDbType.VarChar).Value = day;
    cmd.Parameters.Add(2, OleDbType.Integer).Value = hour;
    cmd.Parameters.Add(3, OleDbType.Integer).Value = loadKW;

Note I do not know C#, but similar approach works for Java and JDBC.

answered Apr 7, 2010 at 10:32

Juha Syrjälä

34.4k33 gold badges137 silver badges191 bronze badges

1 Comment

Kevin Over a year ago

Thanks, Juha, I'll see if that makes a difference.

Collectives™ on Stack Overflow

"Syntax error in INSERT INTO statement". Why?

3 Answers 3

3 Comments

4 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

3 Comments

4 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related