Syntax Error INSERT INTO Statement (Command Builder)

Question

Okay this problem of mine is a little bit tricky for the past months now. SUMMARY: When I only have 5 columns in my Access Database my code successfully inserts the new row. But when I add another column in my access database (that will be 6 columns now) I encounter

Syntax Error INSERT INTO Statement

Code:

  Dim cb As New OleDb.OleDbCommandBuilder(SYSTEM_MainClient.dbda)
    Dim dsNewRow As DataRow
    dsNewRow = SYSTEM_MainClient.DataSet.Tables("Database").NewRow()
    dsNewRow.Item("First_Name") = TXT_FirstName.Text
    dsNewRow.Item("Last_Name") = TXT_Surname.Text
    dsNewRow.Item("StudentID") = CInt(TXT_StudentID.Text)
    dsNewRow.Item("Middle_Name") = TXT_MiddleName.Text
    dsNewRow.Item("College") = TXT_College.Text
    SYSTEM_MainClient.DataSet.Tables("Database").Rows.Add(dsNewRow)
    SYSTEM_MainClient.dbda.Update(SYSTEM_MainClient.DataSet, "Database")
   MsgBox("New Record added to the Database")

My database cColumns:

"StudentID" (primary key), 
"First_Name", "Last_Name", "Middle_Name"
"College"

RESULT: This will work okay, and new record is added into the database.

Because there is only 5 columns.

BUT IF I EXCEED:

Code:

  Dim cb As New OleDb.OleDbCommandBuilder(SYSTEM_MainClient.dbda)
    Dim dsNewRow As DataRow
    dsNewRow = SYSTEM_MainClient.DataSet.Tables("Database").NewRow()
    dsNewRow.Item("First_Name") = TXT_FirstName.Text
    dsNewRow.Item("Last_Name") = TXT_Surname.Text
    dsNewRow.Item("StudentID") = CInt(TXT_StudentID.Text)
    dsNewRow.Item("Middle_Name") = TXT_MiddleName.Text
    dsNewRow.Item("College") = TXT_College.Text

    dsNewRow.Item("Section") = TXT_Section.Text 'I added a section insert

    SYSTEM_MainClient.DataSet.Tables("Database").Rows.Add(dsNewRow)
    SYSTEM_MainClient.dbda.Update(SYSTEM_MainClient.DataSet, "Database")
   MsgBox("New Record added to the Database")

My database columns:

"StudentID" (primary key)
"First_Name", "Last_Name", "Middle_Name"
"College", "Section".

It will return an error

Syntax Error INSERT INTO Statement

It's like it can only insert not more than 5 column values, I think the problem is access not VB itself.

I hope you understand my problem, please help me It's my problem for months now, I searched the forums and Googled it, still can't find the answer.

Thank you so much!

Perhaps, the problem is with update command of the dbda can you show it? if problem be with it we will fix it — void
– void, Commented Jan 17, 2015 at 12:52
section is an access reserved word.support2.microsoft.com/default.aspx?scid=kb;en-us;286335. Try changing it to something else — Flakes
– Flakes, Commented Jan 17, 2015 at 12:59
This the 'dbda' line . 'Public dbda As OleDb.OleDbDataAdapter' from the top of the class. Thats all it is, there's no update commands, I think the problem is Access itself because when I delete the "Section" column, everything works fine, but If I added a column again, it will return an error. :( — billywafuu
– billywafuu, Commented Jan 17, 2015 at 13:02
Oh my god guys! Thank you so much! It works! Thank you very much wholeheartedly I never know that "Section" is a reserved word, my head almost explodes when I can't find the solution, thank you very much! a lesson for me about reserved words. Thank you so much, God Bless! — billywafuu
– billywafuu, Commented Jan 17, 2015 at 13:11

Gord Thompson · Accepted Answer · 2015-02-14 12:29:50Z

4

This happens almost universally because one or more column names are reserved words or contain spaces or other special characters. In your case I suspect that "section" is a reserved word.

The solution is to set the .QuotePrefix and .QuoteSuffix properties of the OleDbCommandBuilder object to escape all column names. For Access, set them to "[" and "]" respectively.

edited Feb 14, 2015 at 12:29

Gord Thompson

125k39 gold badges252 silver badges458 bronze badges

answered Jan 17, 2015 at 12:59

jmcilhinney

54.9k5 gold badges30 silver badges52 bronze badges

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

2 Comments

billywafuu Over a year ago

Thanks for the heads up :) Really appreciate it :)

jmcilhinney Over a year ago

I should note that the best solution is to avoid column names that will cause an issue in the first place. I would recommend never including spaces or other special characters in a column name and avoiding reserved words. If you can't or won't do that though, QuotePrefix and QuoteSuffix are there to support you.

Collectives™ on Stack Overflow

Syntax Error INSERT INTO Statement (Command Builder)

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related