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
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.
As indicated in comments, the issue was in
dsNewRow.Item("Date") = mtbDate.Text
with Date
being a reserved word.
User contributions licensed under CC BY-SA 3.0