"Syntax error in INSERT INTO statement" when adding record to Access database

Question

I've searched for hours for a solution to this problem but nothing I've read has helped. I'm getting this error when trying to add this record to an Access database. The file I'm trying to save into is named Cats.accdb, with a table named Cats.

Table column names:
CatId (type: text)
CatName (text)
Hair (text)
Size (text)
CareType (text)
Notes (text)
AdoptDate (date/time general date), Weight (double), Age (integer) (I've commented any reference to these columns out in the C# code to attempt to debug with just plain old text boxes. At first I thought it was because of something to do with using a DateTimePicker, but it still throws the error after commenting out.)

C# code:

Cat temp = new Cat(txtCatName.Text, txtHair.Text, txtSize.Text, txtCareType.Text, txtNotes.Text);

public string AddCat()
    {
        string strFeedback = "";

        string strSQL = "INSERT INTO Cats (CatName, Hair, Size, CareType, Notes) VALUES (@CatName, @Hair, @Size, @CareType, @Notes)"; 

        OleDbConnection conn = new OleDbConnection();

        string strConn = @"Provider=Microsoft.ACE.OLEDB.12.0; Data Source=Data\Cats.accdb; Persist Security Info=False;";
        conn.ConnectionString = strConn; 

        OleDbCommand comm = new OleDbCommand();
        comm.CommandText = strSQL;  
        comm.Connection = conn; 
        comm.Parameters.AddWithValue("@CatName", CatName); 
        comm.Parameters.AddWithValue("@Hair", Hair);
        comm.Parameters.AddWithValue("@Size", Size);
        comm.Parameters.AddWithValue("@CareType", CareType);
        comm.Parameters.AddWithValue("@Notes", Notes);
        //comm.Parameters.AddWithValue("@AdoptDate", AdoptDate);
        //comm.Parameters.AddWithValue("@Weight", Weight);
        //comm.Parameters.AddWithValue("@Age", Age);

        {
            conn.Open();
            strFeedback = comm.ExecuteNonQuery().ToString() + " record has been added successfully!";
            conn.Close();
        }
        catch (Exception err)
        {
            strFeedback = "ERROR: " + err.Message;
        }
        return strFeedback;

lblFeedback.Text = temp.AddCat();

Thanks for any help you can give!

Thank you ( @guffa specially )... I had the same problem, you guys just solved it... One of the fields that I have on the table was a reserved word (I don't know which one, so what I did was just, put brackets one every field name - one the "Insert statement"). Like this: string insertOnDB = "Insert Into [Users] ([Username],[Password],[CreatedOn],[FullName],[Gender],[AccountType],[Status])values(?,?,?,?,?,?,?)"; Thank you, once again — castigohd
– castigohd, Commented Dec 15, 2016 at 9:00

Guffa · Accepted Answer · 2015-05-03 22:00:10Z

10

Size is a reserved keyword. Add brackets around the name to specify that it's an identifier:

string strSQL = "INSERT INTO Cats (CatName, Hair, [Size], CareType, Notes) VALUES (@CatName, @Hair, @Size, @CareType, @Notes)";

Alternatively, change the field name to something that is not a keyword.

answered May 3, 2015 at 22:00

Guffa

703k111 gold badges760 silver badges1k bronze badges

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

1 Comment

leoraelkins Over a year ago

THANK YOU SO MUCH! I had Name used before changing it to CatName because it was flagged as a reserved word, so I didn't even think of that one (obviously). Your suggestion solved my problem!

garryp · Accepted Answer · 2015-05-03 22:00:17Z

0

In MS Access OLEDB I believe you use position markers rather than parameter names.

string strSQL = "INSERT INTO Cats (CatName, Hair, Size, CareType, Notes) VALUES (?, ?, ?, ?, ?)";

answered May 3, 2015 at 22:00

garryp

5,7861 gold badge31 silver badges43 bronze badges

2 Comments

Steve Over a year ago

No Access accepts the same parameters syntax used by its big cousin. (Or better, the OleDB provider doesn't care) However this doesn't mean that OleDb recognizes these names and allows you to change the order in which you supply the values

garryp Over a year ago

Sorry you're right. I looked at the connection string with the absence of the word "jet" without realising it has been superseded.

Collectives™ on Stack Overflow

"Syntax error in INSERT INTO statement" when adding record to Access database

2 Answers 2

1 Comment

2 Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

2 Comments

Linked

Related