Question Syntax error in INSERT INTO statement

[SCassidy2010]

Hi,

I'm currently writing a vb.net program in visual studio 2005 to save job details to a access 2007 database. I have one form so far to add customer details, it works fine saving, editing etc. I have a new form to add specific job details but anytime I try to update to the database I get this

System.Data.OleDb.OleDbException: Syntax error in INSERT INTO statement.
   at System.Data.Common.DbDataAdapter.UpdatedRowStatusErrors(RowUpdatedEventArgs rowUpdatedEvent, BatchCommandInfo[] batchCommands, Int32 commandCount)
   at System.Data.Common.DbDataAdapter.UpdatedRowStatus(RowUpdatedEventArgs rowUpdatedEvent, BatchCommandInfo[] batchCommands, Int32 commandCount)
   at System.Data.Common.DbDataAdapter.Update(DataRow[] dataRows, DataTableMapping tableMapping)
   at System.Data.Common.DbDataAdapter.UpdateFromDataTable(DataTable dataTable, DataTableMapping tableMapping)
   at System.Data.Common.DbDataAdapter.Update(DataSet dataSet, String srcTable)
   at projCDiTNi.frmAddDataRecoveryJob.btnConfirm_Click(Object sender, EventArgs e) in D:\HND Computing\Year 2\Assignments\SDP1\CDiTNi\solnCDiTNi\projCDiTNi\frmAddDataRecoveryJob.vb:line 132

I haven't a clue why this is coming up, or what it means!! at the min I have 3 data adapters adding different info to the dataset (one for adding a table with job prices, another for adding customer records for a combo box and finally a table to store the job details) - could this be causing a problem?

 

[jmcilhinney]

The most likely explanation for this is that you are using an OleDbCommandBuilder to generate your DELETE, INSERT and/or UPDATE statements based on your SELECT statement and the SQL code generated is invalid. The most common reason for that is that one or more of your table or column names are reserved words. The best way to avoid this is to not use reserved words as identifiers. If that change is not possible, the next thing to look at is, instead of using a wildcard in your query, write out the full column list. In that case you'll have to escape the resereved words and the command builder should follow suit. For example, instead of this:

SELECT * FROM User

you do this:

SELECT UserID, UserName, [Password] FROM User

You escape the reserved word "Password" to force it to be interpreted as an identifier.

 

[SCassidy2010]

Hi, you have no idea how much trouble you've saved me!!

Funnily enough, I searched for a list of access 2007 reserved words before coming here and couldn't find any that matched my fields so I thought it was the data adapter itself that wasn't working - out of curiosity there I put EVERY field in square brackets and it works fine now

thanks again!

 

[poesboes]

FYI I had the situation that SELECT * FROM worked fine, but the INSERT INTO failed exactly as described here by SCassidy2010. In my case this was not due to the use of reserved words, but because there were spaces in the field identifiers of the database. The use of spaces in field id's seems to be allowed by MS Access 2003 where the database originated.

 

[jmcilhinney]

FYI I had the situation that SELECT * FROM worked fine, but the INSERT INTO failed exactly as described here by SCassidy2010. In my case this was not due to the use of reserved words, but because there were spaces in the field identifiers of the database. The use of spaces in field id's seems to be allowed by MS Access 2003 where the database originated.

Spaces and other special characters are allowed in identifiers in pretty much all databases, but it makes perfect sense they create syntax errors in SQL code because the parser doesn't know to treat the multiple words as a single identifier. The best option is to NEVER use spaces in database identifiers. If you must for some reason then the solution is the same as for reserved words, i.e. escape the identifier. You can do it in the SelectCommand as I suggested previously or, when using a command builder, you can also set the QuotePrefix and QuoteSuffix properties, so all identifiers in the generated SQL code will be wrapped in those two strings, e.g. "[" and "]" or, in some cases, "`" for both.