Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Welcome To Ask or Share your Answers For Others

Categories

0 votes
350 views
in Technique[技术] by (71.8m points)

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

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=DataCats.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!

See Question&Answers more detail:os

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome To Ask or Share your Answers For Others

1 Reply

0 votes
by (71.8m points)

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.


与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
OGeek|极客中国-欢迎来到极客的世界,一个免费开放的程序员编程交流平台!开放,进步,分享!让技术改变生活,让极客改变未来! Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Click Here to Ask a Question

...