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

Question

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.

it's the name of table which I used in first place in select. — Ruturaj
– Ruturaj, Commented Mar 30, 2016 at 16:41
does the table name contain spaces? On which statement does the error occur? — ron tornambe
– ron tornambe, Commented Mar 30, 2016 at 17:29
Investigate the statement in an Access session. Write the c# code's completed query string to a text file. Then copy the statement text and paste into SQL View of a new query in the Access query designer. When you attempt to run the query, does Access complain about syntax error or anything else? If not, can you then supply appropriate values for each of the parameters and wind up with a new row in the destination table? — HansUp
– HansUp, Commented Mar 30, 2016 at 17:37
@rontornambe Yes, the table name contains spaces, error occurred at command.ExecuteNonQuery(); — Ruturaj
– Ruturaj, Commented Mar 30, 2016 at 20:57
@HansUp So I made some changes (you can see them in edit), then I ran command.commandText as you said (directly on database, in SQL view), it worked there and inserted new row. Still showing same error in here. — Ruturaj
– Ruturaj, Commented Mar 30, 2016 at 21:13

squillman · Accepted Answer · 2016-03-30 21:56:13Z

4

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.

answered Mar 30, 2016 at 21:56

squillman

13.7k3 gold badges45 silver badges65 bronze badges

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

1 Comment

Ruturaj Over a year ago

Thank you so much, I guess I had wrong title all along.

Collectives™ on Stack Overflow

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

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related