Syntax Error in INSERT INTO statement vb.net and an access database

Question

The program I'm writing (in vb.net) is supposed to be loading values from text boxes into a database. However, when I click "Save", at first nothing at all happened. No error, no notification, nothing. So I traced it using breakpoints, and it got to:

daTraining.Update(dsTraining, "Training")

and just stopped. So I put in a try/catch, and now when I hit save I get

System.Data.OleDB.OledgException (0x80040E14): Syntax error in INSERT INTO statement.

I'm confused on how to troubleshoot this or what the issue might be.

The Code

Private Sub btnSave_Click(sender As Object, e As EventArgs) Handles btnSave.Click

        Try
            Dim cb As New OleDb.OleDbCommandBuilder(daTraining)
            Dim dsNewRow As DataRow
            dsNewRow = dsTraining.Tables("Training").NewRow
            dsNewRow.Item("ID") = txtTrainingID.Text
            dsNewRow.Item("Ranch") = cbRanches.Text
            dsNewRow.Item("Location") = txtLocation.Text
            dsNewRow.Item("Date") = mtbDate.Text
            dsNewRow.Item("StartTime") = mtbStartTime.Text
            dsNewRow.Item("FinishTime") = mtbFinishTime.Text
            dsNewRow.Item("Crew") = txtCrews.Text
            dsNewRow.Item("Supervisor") = txtSupervisor.Text
            dsNewRow.Item("Foreperson") = txtForeperson.Text
            dsNewRow.Item("Activity") = txtActivity.Text
            dsNewRow.Item("Trainer") = txtTrainer.Text
            dsNewRow.Item("Topics") = txtTopics.Text

            dsTraining.Tables("Training").Rows.Add(dsNewRow)
            daTraining.Update(dsTraining, "Training")

            MsgBox("Training Recorded")

            cb.Dispose()


        Catch ex As Exception
            MsgBox(ex.ToString)
        End Try



    End Sub

Take a look at the query that is in daTraining.InsertCommand.CommandText. You should find that it has some kind of an error — FloatingKiwi
– FloatingKiwi, Commented Aug 10, 2016 at 2:24
List of Microsoft Jet 4.0 reserved words. Your dataadapter can/will hold onto all the commands without recreating the commandbuilder over and over. — Ňɏssa Pøngjǣrdenlarp
– Ňɏssa Pøngjǣrdenlarp, Commented Aug 10, 2016 at 2:36
As @Plutonix suggests, you should not create a new command builder every time you save. Create a single command builder immediately after you create the data adapter it's building commands for. If you're using a member variable for the data adapter, use a member variable for the command builder too. — jmcilhinney
– jmcilhinney, Commented Aug 10, 2016 at 3:45
@FloatingKiwi How would I do this? I added: {MsgBox(daTraining.InsertCommand.CommandText.ToString)} and it throws an error. I googled it and it said I needed to write a parser. — 404notfound
– 404notfound, Commented Aug 10, 2016 at 20:07

jmcilhinney · Accepted Answer · 2016-08-10 03:43:54Z

2

This is a common issue when using a data adapter with a wildcard in the query and a command builder. If one or more of your columns names is a reserved word or contains spaces or other special characters then the auto-generated INSERT and UPDATE statements will generate syntax errors.

The preferred solution is to change the offending column name(s) but, if that's not an option, you can set the QuotePrefix and QuoteSuffix properties of your command builder so that it escapes all column names in the generated action commands. The appropriate values will vary depending on your database but for Microsoft databases, use "[" and "]" respectively.

answered Aug 10, 2016 at 3:43

jmcilhinney

54.9k5 gold badges30 silver badges51 bronze badges

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

Comments

MethodMan · Accepted Answer · 2016-08-10 21:29:08Z

0

As indicated in comments, the issue was in

dsNewRow.Item("Date") = mtbDate.Text

with Date being a reserved word.

edited Aug 10, 2016 at 21:29

MethodMan

18.9k6 gold badges41 silver badges53 bronze badges

answered Aug 10, 2016 at 20:48

404notfound

11 bronze badge

1 Comment

jmcilhinney Over a year ago

No, the issue is not there. There's nothing wrong with that code. The issue is that the command builder generated invalid SQL because it wasn't escaping that column name. There are two ways to fix that without changing that column name and thus not changing the code you've displayed here. While changing the column name is the best option, you can fix the issue without doing so where you've indicated the issue was is actually fine as it is.

Collectives™ on Stack Overflow

Syntax Error in INSERT INTO statement vb.net and an access database

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related