CODEWITHSUNDEEP
c#databasems-access
RHammonds
RHammonds

Reputation: 107

C# with Access database getting Insert Into Syntax Error

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!

Upvotes: 2

Views: 202

Answers (2)

Gord Thompson
Gord Thompson

Reputation: 123849

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

Upvotes: 1

Marco
Marco

Reputation: 23945

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

Upvotes: 1

Related Questions