CODEWITHSUNDEEP
c#databasems-access
Ruturaj
Ruturaj

Reputation: 630

Syntax error in INSERT INTO statement MS access (not keyword issue)

I am facing this error when I try to insert a data row in to table in ms access file. dataTable is table I got using select * from TableName,

I got it, displayed it, made changes, now I want to replace previous one with new one. So I am going to delete all previous rows and add each row one by one from new table. But I am not able to insert any row.

I am getting this error

"Syntax error in INSERT INTO statement."

String query = "INSERT INTO [" + TableName + "] (TaskID, HTMLTopic, [Group], nKey,"
    + " nText, nImage, nSelImage, nFontName, nFontInfo, Keywords) VALUES (@TaskID,"
    + " @HTMLTopic, @Group, @nKey, @nText, @nImage, @nSelImage, @nFontName, "
    + " @nFontInfo, @Keywords)";
OleDbCommand command = new OleDbCommand(query, mdbConnection);
command.Parameters.AddWithValue("@TaskID", dataTable.Rows[0]["TaskID"]);
command.Parameters.AddWithValue("@HTMLTopic", dataTable.Rows[0]["HTMLTopic"]);
command.Parameters.AddWithValue("@Group", dataTable.Rows[0]["Group"]);
command.Parameters.AddWithValue("@nKey", dataTable.Rows[0]["nKey"]);
command.Parameters.AddWithValue("@nText", dataTable.Rows[0]["nText"]);
command.Parameters.AddWithValue("@nImage", dataTable.Rows[0]["nImage"]);
command.Parameters.AddWithValue("@nSelImage", dataTable.Rows[0]["nSelImage"]);
command.Parameters.AddWithValue("@nFontName", dataTable.Rows[0]["nFontName"]);
command.Parameters.AddWithValue("@nFontInfo", dataTable.Rows[0]["nFontInfo"]);
command.Parameters.AddWithValue("@Keywords", dataTable.Rows[0]["Keywords"]);
mdbConnection.Open();
command.ExecuteNonQuery();
mdbConnection.Close();

Edit: Changed it just for debugging to

        String query = "INSERT INTO [" + TableName + "] (TaskID, HTMLTopic, nRelative, [Group], nKey,"
           + " nText, nImage, nSelImage, nFontName, nFontInfo, Keywords) VALUES ('" + dataTable.Rows[0]["TaskID"]
           + "', '" + dataTable.Rows[0]["HTMLTopic"] + "', '" + dataTable.Rows[0]["nRelative"] + "', '" + dataTable.Rows[0]["Group"]
           + "', " + dataTable.Rows[0]["nKey"] + ", '" + dataTable.Rows[0]["nText"] + "', '" + dataTable.Rows[0]["nImage"]
           + "', '" + dataTable.Rows[0]["nSelImage"] + "', '" + dataTable.Rows[0]["nFontName"] + "', '" + dataTable.Rows[0]["nFontInfo"]
           + "', '" + dataTable.Rows[0]["Keywords"] + "')";

        OleDbCommand command = new OleDbCommand(query, mdbConnection);
        Debug.Print(command.CommandText);
        mdbConnection.Open();
        command.ExecuteNonQuery();
        mdbConnection.Close();

I added some single quotes so database can understand them as string.

Upvotes: 2

Views: 650

Answers (1)

squillman
squillman

Reputation: 13641

There looks to be a bug somewhere between the provider and the engine. It looks like the issue is with your column named nText.

I duplicated your schema in an Access 2013 db and received the same error that you did. I then started making various changes to the column names and the query. When I changed column names (appending a X to the end of each column) the INSERT worked. I then went back and started adding square brackets to other columns names. As soon as I did that for nText it worked. This query works for me in a C# console app using the Microsoft.ACE.OLEDB.12.0 oldeb provider:

    String query =
        "INSERT INTO [" + TableName + "] (TaskID,HTMLTopic,[Group],nKey,[nText],nImage,nSelImage,nFontName,nFontInfo,Keywords)" +
        "VALUES" +
        "(@TaskID,@HTMLTopic, @Group, @nKey, @nText, @nImage, @nSelImage, @nFontName,@nFontInfo, @Keywords)"

I agree with you that it shouldn't be a keyword / reserved word issue, but it sure acts like it is. NTEXT is a keyword in TSQL (SQL Server), but not Access according to https://support.microsoft.com/en-us/kb/286335.

Upvotes: 4

Related Questions