Mohsen Mohsen - 4 years ago 120
C# Question

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

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

Answer Source

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.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download