C# with Access database getting Insert Into Syntax Error

Question

I am getting a syntax error on my insert statement but I cannot find the error.

Here is my method

String sql = "insert into receivers (receiver_id, receiver_name, salary, team,     forty_yard_dash, position, offense) " + 
                                "values" + 
                                "(@id, @name, @salary, @team, @forty, @position, @offense)";
        com = new OleDbCommand(sql, con);
        com.Parameters.AddWithValue("@id", rec.id);
        com.Parameters.AddWithValue("@name", rec.name);
        com.Parameters.AddWithValue("@salary", rec.salary);
        com.Parameters.AddWithValue("@team", rec.team);
        com.Parameters.AddWithValue("@forty", rec.fortyYardDash);
        com.Parameters.AddWithValue("@offense", rec.offenseDefense);
        con.Open();
        com.ExecuteNonQuery();
        con.Close();

My database looks like this:
receiver_id number
name text
salary number
team text
forty_yard_dash number
position text
offense Yes/No

Any help would be appreciated!

For text type column i think you have to enclosed values with ' or " in the sql request — user2196728
– user2196728, Commented Nov 17, 2013 at 15:44
@user2196728 No, with a parameterized query you don't need to delimit any values in the SQL string. (In fact, it is a common mistake for people to put such delimiters in.) — Gord Thompson
– Gord Thompson, Commented Nov 17, 2013 at 15:50
Any time you ask a question about an error, you should always tell us what the error is. — Dour High Arch
– Dour High Arch, Commented Nov 17, 2013 at 16:08

Community · Accepted Answer · 2017-05-23 12:28:46Z

King King has already pointed it out in the comments:

You need to add the parameter for position

String sql = "insert into receivers (receiver_id, receiver_name, salary, team, forty_yard_dash, position, offense) " + 
             "values" + 
             "(@id, @name, @salary, @team, @forty, @position, @offense)";
    com = new OleDbCommand(sql, con);
    com.Parameters.AddWithValue("@id", rec.id);
    com.Parameters.AddWithValue("@name", rec.name);
    com.Parameters.AddWithValue("@salary", rec.salary);
    com.Parameters.AddWithValue("@team", rec.team);
    com.Parameters.AddWithValue("@forty", rec.fortyYardDash);
    //new --> I am assuming the value for position comes from rec.position; change to correct source
    com.Parameters.AddWithValue("@position", rec.position); 
    com.Parameters.AddWithValue("@offense", rec.offenseDefense);
    con.Open();
    com.ExecuteNonQuery();
    con.Close();

This should do the trick, if there is no other error you have not told us about ;)

Gord Thompson · Accepted Answer · 2013-11-17 16:28:07Z

In addition to the missing .Parameters.AddWithValue statement for position, the other issue is that position is a reserved word in Access SQL and must be enclosed in square brackets in the CommandText. I just tried the following and it worked for me:

using (var con = new OleDbConnection())
{
    con.ConnectionString = myConnectionString;
    con.Open();

    using (var com = new OleDbCommand())
    {
        int rec_id = 1;                         //
        string rec_name = "Bubba";              //
        int rec_salary = 2000000;               //
        string rec_team = "Springfield Atoms";  // test data
        int rec_fortyYardDash = 99;             //
        string rec_position = "linebacker";     //
        bool rec_offense = false;               //

        string sql =
                "INSERT INTO receivers (" +
                        "receiver_id, " +
                        "receiver_name, " +
                        "salary, " +
                        "team, " +
                        "forty_yard_dash, " +
                        "[position], " +
                        "offense " +
                    ") VALUES (?,?,?,?,?,?,?)";
        com.Connection = con;
        com.CommandText = sql;
        com.Parameters.AddWithValue("?", rec_id);
        com.Parameters.AddWithValue("?", rec_name);
        com.Parameters.AddWithValue("?", rec_salary);
        com.Parameters.AddWithValue("?", rec_team);
        com.Parameters.AddWithValue("?", rec_fortyYardDash);
        com.Parameters.AddWithValue("?", rec_position);
        com.Parameters.AddWithValue("?", rec_offense);
        com.ExecuteNonQuery();
    }
    con.Close();
}

That was it!!! Thank you both! When I added the field in the DB usually it will tell me if it is a reserved word or not...Oh well

Collectives™ on Stack Overflow

C# with Access database getting Insert Into Syntax Error

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related