Getting Syntax Error When using 'Insert into' command in c#

Question

I get syntax error when using 'Insert into' SQL command using c#. I'm using Access db to store some data. Surprisingly, when I copy the exact command into MS Access to try if it's not correct, it works like charm. I'm a bit confused! I appreciate any idea or help in this regard. Here is my code:

using (OleDbConnection connection = new OleDbConnection(Global.ConString))
            {
                using (OleDbCommand command = new OleDbCommand())
                {
                    command.Connection = connection;            
                    command.CommandType = CommandType.Text;
                    command.CommandText = "INSERT INTO Users(Name,UserName,Password,Customers,Jobs,Invoice,Statement,Reports,Users) values (@name,@UserName,@Password,@Customers,@Jobs,@Invoice,@Statement,@Reports,@Users)";

                    command.Parameters.AddWithValue("@name",fullName.Text );
                    command.Parameters.AddWithValue("@UserName", userName.Text);
                    command.Parameters.AddWithValue("@Password", passWord.Text);
                    command.Parameters.AddWithValue("@Customers", Customers.Checked);
                    command.Parameters.AddWithValue("@Jobs", Jobs.Checked);
                    command.Parameters.AddWithValue("@Invoice", Invoice.Checked);
                    command.Parameters.AddWithValue("@Statement", Statement.Checked);
                    command.Parameters.AddWithValue("@Reports", Report.Checked);
                    command.Parameters.AddWithValue("@Users", userDef.Checked);

                    try
                    {
                        connection.Open();
                        int recordsAffected = command.ExecuteNonQuery();
                        if (recordsAffected > 0)
                        {
                            foreach (Control item in newRecord.Controls)
                            {
                                if (item is TextBox) item.ResetText();
                            }
                            timer1.Enabled = true;

                        }
                        else
                            MessageBox.Show("Insert Fail");

                    }
                    catch (OleDbException err)
                    {

                        MessageBox.Show("Somthing wrong. Error no. is: " + err.ErrorCode + "..." + err.Message);
                    }
                }
            }

jmcilhinney · Accepted Answer · 2017-02-06 06:21:13Z

1

Most likely your issue is the use of a reserved word as an identifier, specifically 'Password'. Wrap that column name in brackets, i.e. [Password] and you should be good to go.

It's best to avoid reserved words if possible. Generally speaking, you should not be storing unhashed passwords in your database so a column name like 'PasswordHash` is appropriate and avoids this issue.

edited Feb 6, 2017 at 6:21

answered Feb 6, 2017 at 5:27

jmcilhinney

54.9k5 gold badges30 silver badges52 bronze badges

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

Collectives™ on Stack Overflow

Getting Syntax Error When using 'Insert into' command in c#

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related