Syntax error in INSERT INTO statement using C# with ms-access

Question

I am having trouble figuring out what is wrong with my code. I am trying to add a record in my Access Db through C# but was confronted with an exception that says I have a wrong syntax in my SQL statement.

 private void buttonSUB_Click(object sender, EventArgs e)
    {
        string Adrs = textADRS.Text;
        string Fname = textFN.Text;
        string Mname = textMN.Text;
        string Lname = textLN.Text;
        string Pos = textPOS.Text;

        try
        {

            OleDbConnection con = new OleDbConnection();
            con.ConnectionString = @"Provider = Microsoft.ACE.OLEDB.12.0; Data Source = C:\\Users\\genesis\\Documents\\Database1.accdb";
            OleDbCommand cmd = new OleDbCommand("Insert into Employees (FN,MN,LN,Address,Position) Values (@FirstName,@MidName,@LastName,@Address,@Position)",con);
            cmd.Parameters.Add(new OleDbParameter("@FirstName", Fname));
            cmd.Parameters.Add(new OleDbParameter("@MidName", Mname));
            cmd.Parameters.Add(new OleDbParameter("@LastName", Lname));
            cmd.Parameters.Add(new OleDbParameter("@Address", Adrs));
            cmd.Parameters.Add(new OleDbParameter("@Position", Pos));
            con.Open();
            cmd.ExecuteNonQuery();
            MessageBox.Show("Record Submitted", "Nice!");
            con.Close();
        }

        catch (Exception ex) {
            MessageBox.Show(ex.Message);
        }



    }

Typically the syntax exceptions state the character location or exact piece of syntax that it considers to be incorrect. This is often a great clue toward finding the problem. Can you paste the full exception message? — Bill Tarbell
– Bill Tarbell, Commented Jun 21, 2017 at 18:57

Tetsuya Yamamoto · Accepted Answer · 2017-06-22 03:11:26Z

From Access SQL Reserved Words List, POSITION already defined as reserved keyword.

If you use a reserved word or symbol to name a field in a table, Access warns you that the word is reserved and that you might encounter errors when referring to the field. You might also encounter errors if you use a reserved word to name a control, an object, or a variable.

If a reserved word is already in use, you can avoid error messages by surrounding each occurrence of the word with brackets ([ ]).

Hence, you need to add square brackets to use it as table name:

OleDbCommand cmd = new OleDbCommand(
                 @"INSERT INTO Employees (FN, MN, LN, Address, [Position]) 
                   VALUES (@FirstName, @MidName, @LastName, @Address, @Position)", con);

NB: Reserved words are case-insensitive in Access usage, thus every occurrences which use reserved words must placed inside square brackets.

Marcelo · Accepted Answer · 2017-06-21 18:45:30Z

-1

try some like this:

cmd.Parameters.Add(new OleDbParameter("@FirstName","'" + Fname "'"));

Single cote with string parameters.

answered Jun 21, 2017 at 18:45

Marcelo

11 bronze badge

Collectives™ on Stack Overflow

Syntax error in INSERT INTO statement using C# with ms-access

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related