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

0

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
vb.net
ms-access
asked on Stack Overflow Aug 10, 2016 by 404notfound • edited Dec 11, 2017 by Vadim Kotov

2 Answers

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 on Stack Overflow Aug 10, 2016 by jmcilhinney
0

As indicated in comments, the issue was in

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

with Date being a reserved word.

answered on Stack Overflow Aug 10, 2016 by 404notfound • edited Aug 10, 2016 by MethodMan

User contributions licensed under CC BY-SA 3.0