CODEWITHSUNDEEP
c#database
Kevin
Kevin

Reputation: 4848

"Syntax error in INSERT INTO statement". Why?

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();
        }
    }

Upvotes: 4

Views: 821

Answers (3)

Juha Syrjälä
Juha Syrjälä

Reputation: 34261

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.

Upvotes: 0

user254875486
user254875486

Reputation: 11240

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)

Upvotes: 7

Klaus Byskov Pedersen
Klaus Byskov Pedersen

Reputation: 120917

Try changing 

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

To:

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

Upvotes: 2

Related Questions