I've been sitting here staring at this code for a day now, and am quite ready to light my computer on fire and throw it into oncoming traffic.
The error I recieve is:
System.Data.OleDbException Syntax error in INSERT INTO statement.
I think everything is spelled correctly. I'm expanding on a program I wrote in the past that I had no issue with.
If inc <> -1 Then
Try
Dim cbTrainingInfo As New OleDb.OleDbCommandBuilder(daTrainingInfo)
Dim dsNewRowTrainingInfo As DataRow
dsNewRowTrainingInfo = dsTrainingInfo.Tables("TrainingInfo").NewRow
dsNewRowTrainingInfo.Item("TrainingID") = txtTrainingID.Text
dsNewRowTrainingInfo.Item("Location") = txtLocation.Text
dsNewRowTrainingInfo.Item("Ranch") = txtRanch.Text
dsNewRowTrainingInfo.Item("Date") = mtbDate.Text
dsNewRowTrainingInfo.Item("Time") = mtbTime.Text
dsNewRowTrainingInfo.Item("Crew") = txtCrews.Text
dsNewRowTrainingInfo.Item("Supervisor") = txtSupervisor.Text
dsNewRowTrainingInfo.Item("Foreperson") = txtForeperson.Text
dsNewRowTrainingInfo.Item("Activity") = txtActivity.Text
dsNewRowTrainingInfo.Item("Trainer") = txtTrainer.Text
dsNewRowTrainingInfo.Item("Notes") = txtMemo.Text
dsTrainingInfo.Tables("TrainingInfo").Rows.Add(dsNewRowTrainingInfo)
daTrainingInfo.Update(dsTrainingInfo, "TrainingInfo") 'Point of error
Any help would be greatly appriciated. Also, if anyone has a moment to peruse my complete code and offer some criticism it would also be appriciated...
Public Class frmTrainingEntry
Dim con As New OleDb.OleDbConnection
Dim dbProvider As String
Dim dbSource As String
Dim sqlTrainingInfo As String
Dim dsTrainingInfo As New DataSet
Dim daTrainingInfo As New OleDb.OleDbDataAdapter
Dim sqlTrainees As String
Dim dsTraininees As New DataSet
Dim daTrainees As New OleDb.OleDbDataAdapter
Dim RowCount As Integer
Dim inc As Integer
Private Sub frmTrainingEntry_Load(sender As Object, e As EventArgs) Handles MyBase.Load
dbProvider = "provider = microsoft.ace.oledb.12.0;"
dbSource = "Data Source = C:\DatabaseFiles\safety1.accdb"
con.ConnectionString = dbProvider & dbSource
Try
con.Open()
MsgBox(con.State.ToString)
sqlTrainingInfo = "SELECT * FROM tblTrainingInfo"
sqlTrainees = "SELECT * FROM tblTrainees"
'sqlTrainingItems = "SELECT * FROM tblTrainingItems"
'daTrainingItems = New OleDb.OleDbDataAdapter(sqlTrainingItems, con)
'daTrainingItems.Fill(dsTrainingItems, "TrainingItems")
daTrainingInfo = New OleDb.OleDbDataAdapter(sqlTrainingInfo, con)
daTrainees = New OleDb.OleDbDataAdapter(sqlTrainees, con)
daTrainingInfo.Fill(dsTrainingInfo, "TrainingInfo")
daTrainees.Fill(dsTraininees, "Trainees")
con.Close()
RowCount = dsTrainingInfo.Tables("TrainingInfo").Rows.Count
inc = -1
Catch ex As Exception
MsgBox(ex.ToString)
End Try
End Sub
Private Sub NavigateTrainingInfo()
Dim cb As New OleDb.OleDbCommandBuilder(daTrainingInfo)
txtTrainingID.Text = dsTrainingInfo.Tables("TrainingInfo").Rows(inc).Item(1)
txtLocation.Text = dsTrainingInfo.Tables("TrainingInfo").Rows(inc).Item(2).ToString
txtRanch.Text = dsTrainingInfo.Tables("TrainingInfo").Rows(inc).Item(3).ToString
mtbDate.Text = dsTrainingInfo.Tables("TrainingInfo").Rows(inc).Item(4).ToString
mtbTime.Text = dsTrainingInfo.Tables("TrainingInfo").Rows(inc).Item(5).ToString
txtCrews.Text = dsTrainingInfo.Tables("TrainingInfo").Rows(inc).Item(6).ToString
txtSupervisor.Text = dsTrainingInfo.Tables("TrainingInfo").Rows(inc).Item(7).ToString
txtForeperson.Text = dsTrainingInfo.Tables("TrainingInfo").Rows(inc).Item(8).ToString
txtActivity.Text = dsTrainingInfo.Tables("TrainingInfo").Rows(inc).Item(9).ToString
txtTrainer.Text = dsTrainingInfo.Tables("TrainingInfo").Rows(inc).Item(10).ToString
txtMemo.Text = dsTrainingInfo.Tables("TrainingInfo").Rows(inc).Item(11).ToString
End Sub
Private Sub NavigateTrainees()
Dim dsTrainees As New DataSet
Dim dtTrainees As New DataTable
dsTrainees.Tables.Add(dtTrainees)
Dim daTrainees As New OleDb.OleDbDataAdapter
con.Open()
daTrainees = New OleDb.OleDbDataAdapter("SELECT TrainingID, Trainee FROM tblTrainees WHERE TrainingID = " & txtTrainingID.Text, con)
daTrainees.Fill(dtTrainees)
con.Close()
dgvAttendees.DataSource = dtTrainees.DefaultView
End Sub
Private Sub NavigateTrainingItems()
Dim sqlTrainingItems As String
Dim dsTrainingItems As New DataSet
Dim daTrainingItems As New OleDb.OleDbDataAdapter
Dim dtTrainingItems As New DataTable
dsTrainingItems.Tables.Add(dtTrainingItems)
con.Open()
sqlTrainingItems = "SELECT * FROM tblTrainingItems WHERE TrainingID = " & txtTrainingID.Text
daTrainingItems = New OleDb.OleDbDataAdapter(sqlTrainingItems, con)
daTrainingItems.Fill(dsTrainingItems, "TrainingItems")
con.Close()
If dsTrainingItems.Tables("TrainingItems").Rows(inc).Item(2) = 1 Then
chkSanitation.Checked = True
End If
If dsTrainingItems.Tables("TrainingItems").Rows(inc).Item(3) = 1 Then
chkPesticide.Checked = True
End If
If dsTrainingItems.Tables("TrainingItems").Rows(inc).Item(4) = 1 Then
chkUseTools.Checked = True
End If
If dsTrainingItems.Tables("TrainingItems").Rows(inc).Item(5) = 1 Then
chkLiftingItems.Checked = True
End If
If dsTrainingItems.Tables("TrainingItems").Rows(inc).Item(6) = 1 Then
chkElectricalSafety.Checked = True
End If
If dsTrainingItems.Tables("TrainingItems").Rows(inc).Item(7) = 1 Then
chkSafetyPolicyProcedure.Checked = True
End If
If dsTrainingItems.Tables("TrainingItems").Rows(inc).Item(8) = 1 Then
chkReportInjuryIllnessDeath.Checked = True
End If
If dsTrainingItems.Tables("TrainingItems").Rows(inc).Item(9) = 1 Then
chkTreeWorkPruningOperations.Checked = True
End If
If dsTrainingItems.Tables("TrainingItems").Rows(inc).Item(10) = 1 Then
chkHeatStress.Checked = True
End If
If dsTrainingItems.Tables("TrainingItems").Rows(inc).Item(11) = 1 Then
chkOperatingEquipInspection.Checked = True
End If
If dsTrainingItems.Tables("TrainingItems").Rows(inc).Item(12) = 1 Then
chkFirstAid.Checked = True
End If
If dsTrainingItems.Tables("TrainingItems").Rows(inc).Item(13) = 1 Then
chkTransportation.Checked = True
End If
If dsTrainingItems.Tables("TrainingItems").Rows(inc).Item(14) = 1 Then
chkSexualHarassment.Checked = True
End If
If dsTrainingItems.Tables("TrainingItems").Rows(inc).Item(15) = 1 Then
chkGlobalGap.Checked = True
End If
If dsTrainingItems.Tables("TrainingItems").Rows(inc).Item(16) = 1 Then
chkForkliftTraining.Checked = True
End If
If dsTrainingItems.Tables("TrainingItems").Rows(inc).Item(17) = 1 Then
chkEmergencyResponse.Checked = True
End If
If dsTrainingItems.Tables("TrainingItems").Rows(inc).Item(18) = 1 Then
chkSlipsTripsFalls.Checked = True
End If
If dsTrainingItems.Tables("TrainingItems").Rows(inc).Item(19) = 1 Then
chkLadderSafety.Checked = True
End If
If dsTrainingItems.Tables("TrainingItems").Rows(inc).Item(20) = 1 Then
chkPPE.Checked = True
End If
If dsTrainingItems.Tables("TrainingItems").Rows(inc).Item(21) = 1 Then
chkDrugAlcoholPolicy.Checked = True
End If
End Sub
Private Sub btnNext_Click(sender As Object, e As EventArgs) Handles btnNext.Click
If inc <> RowCount - 1 Then
inc = inc + 1
NavigateTrainingInfo()
NavigateTrainees()
NavigateTrainingItems()
Else
MsgBox("No further Records")
End If
End Sub
Private Sub btnAddNew_Click(sender As Object, e As EventArgs) Handles btnAddNew.Click
btnPrevious.Enabled = False
btnNext.Enabled = False
btnFirst.Enabled = False
btnLast.Enabled = False
btnAddNew.Enabled = False
btnAddNew.Enabled = False
btnPrint.Enabled = False
btnDelete.Enabled = False
btnUpdate.Enabled = False
btnSave.Enabled = True
btnCancel.Enabled = True
txtRanch.Clear()
txtLocation.Clear()
mtbDate.Clear()
mtbTime.Clear()
txtSupervisor.Clear()
txtForeperson.Clear()
txtActivity.Clear()
txtTrainer.Clear()
txtMemo.Clear()
txtCrews.Clear()
chkSanitation.Checked = False
chkElectricalSafety.Checked = False
chkHeatStress.Checked = False
chkSexualHarassment.Checked = False
chkSlipsTripsFalls.Checked = False
chkPesticide.Checked = False
chkGlobalGap.Checked = False
chkLadderSafety.Checked = False
chkFirstAid.Checked = False
chkPPE.Checked = False
chkReportInjuryIllnessDeath.Checked = False
chkSafetyPolicyProcedure.Checked = False
chkDrugAlcoholPolicy.Checked = False
chkOperatingEquipInspection.Checked = False
chkTreeWorkPruningOperations.Checked = False
chkForkliftTraining.Checked = False
chkTransportation.Checked = False
chkUseTools.Checked = False
chkLiftingItems.Checked = False
chkEmergencyResponse.Checked = False
dgvAttendees.DataSource = Nothing
txtRanch.Focus()
inc = RowCount - 1
txtTrainingID.Text = dsTrainingInfo.Tables("TrainingInfo").Rows(inc).Item(0) + 1
End Sub
Private Sub btnSave_Click(sender As Object, e As EventArgs) Handles btnSave.Click
btnPrevious.Enabled = True
btnNext.Enabled = True
btnFirst.Enabled = True
btnLast.Enabled = True
btnAddNew.Enabled = True
btnAddNew.Enabled = True
btnPrint.Enabled = True
btnDelete.Enabled = True
btnUpdate.Enabled = True
btnSave.Enabled = False
btnCancel.Enabled = False
If inc <> -1 Then
Try
Dim cbTrainingInfo As New OleDb.OleDbCommandBuilder(daTrainingInfo)
Dim dsNewRowTrainingInfo As DataRow
dsNewRowTrainingInfo = dsTrainingInfo.Tables("TrainingInfo").NewRow
dsNewRowTrainingInfo.Item("TrainingID") = txtTrainingID.Text
dsNewRowTrainingInfo.Item("Location") = txtLocation.Text
dsNewRowTrainingInfo.Item("Ranch") = txtRanch.Text
dsNewRowTrainingInfo.Item("Date") = mtbDate.Text
dsNewRowTrainingInfo.Item("Time") = mtbTime.Text
dsNewRowTrainingInfo.Item("Crew") = txtCrews.Text
dsNewRowTrainingInfo.Item("Supervisor") = txtSupervisor.Text
dsNewRowTrainingInfo.Item("Foreperson") = txtForeperson.Text
dsNewRowTrainingInfo.Item("Activity") = txtActivity.Text
dsNewRowTrainingInfo.Item("Trainer") = txtTrainer.Text
dsNewRowTrainingInfo.Item("Notes") = txtMemo.Text
dsTrainingInfo.Tables("TrainingInfo").Rows.Add(dsNewRowTrainingInfo)
daTrainingInfo.Update(dsTrainingInfo, "TrainingInfo")
cbTrainingInfo.Dispose()
Dim dsTrainingItems As New DataSet
Dim daTrainingItems As New OleDb.OleDbDataAdapter
Dim cbTrainingItems As New OleDb.OleDbCommandBuilder(daTrainingItems)
Dim dsNewRowTrainingItems As DataRow
dsNewRowTrainingItems = dsTrainingItems.Tables("TrainingItems").NewRow
dsNewRowTrainingItems.Item("TrainingID") = txtTrainingID.Text
If chkSanitation.Checked = True Then
dsNewRowTrainingItems.Item("Sanitation") = 1
Else
dsNewRowTrainingItems.Item("Sanitation") = 0
End If
If chkPesticide.Checked = True Then
dsNewRowTrainingItems.Item("Pesticides") = 1
Else
dsNewRowTrainingItems.Item("Pesticides") = 0
End If
If chkUseTools.Checked = True Then
dsNewRowTrainingItems.Item("UseOfTools") = 1
Else
dsNewRowTrainingItems.Item("UseOfTools") = 0
End If
If chkLiftingItems.Checked = True Then
dsNewRowTrainingItems.Item("LiftingItems") = 1
Else
dsNewRowTrainingItems.Item("LiftingItems") = 0
End If
If chkElectricalSafety.Checked = True Then
dsNewRowTrainingItems.Item("ElectricalSafety") = 1
Else
dsNewRowTrainingItems.Item("ElectricalSafety") = 0
End If
If chkSafetyPolicyProcedure.Checked = True Then
dsNewRowTrainingItems.Item("SafetyPolicyProcedures") = 1
Else
dsNewRowTrainingItems.Item("SafetyPolicyProcedures") = 0
End If
If chkReportInjuryIllnessDeath.Checked = 1 Then
dsNewRowTrainingItems.Item("ReportingInjuriesIllnessDeath") = 1
Else
dsNewRowTrainingItems.Item("ReportingInjuriesIllnessDeath") = 0
End If
If chkTreeWorkPruningOperations.Checked = True Then
dsNewRowTrainingItems.Item("TreeWorkPruningOperations") = 1
Else
dsNewRowTrainingItems.Item("TreeWorkPruningOperations") = 0
End If
If chkHeatStress.Checked = True Then
dsNewRowTrainingItems.Item("HeatStress") = 1
Else
dsNewRowTrainingItems.Item("HeatStress") = 0
End If
If chkOperatingEquipInspection.Checked = True Then
dsNewRowTrainingItems.Item("OperatingEquipInspection") = 1
Else
dsNewRowTrainingItems.Item("OperatingEquipInspection") = 0
End If
If chkFirstAid.Checked = True Then
dsNewRowTrainingItems.Item("FirstAid") = 1
Else
dsNewRowTrainingItems.Item("FirstAid") = 0
End If
If chkTransportation.Checked = True Then
dsNewRowTrainingItems.Item("Transportation") = 1
Else
dsNewRowTrainingItems.Item("Transportation") = 0
End If
If chkSexualHarassment.Checked = True Then
dsNewRowTrainingItems.Item("SexualHarassment") = 1
Else
dsNewRowTrainingItems.Item("SexualHarassment") = 0
End If
If chkGlobalGap.Checked = True Then
dsNewRowTrainingItems.Item("GlobalGap") = 1
Else
dsNewRowTrainingItems.Item("GlobalGap") = 0
End If
If chkForkliftTraining.Checked = True Then
dsNewRowTrainingItems.Item("ForkliftTraining") = 1
Else
dsNewRowTrainingItems.Item("ForkliftTraining") = 0
End If
If chkEmergencyResponse.Checked = True Then
dsNewRowTrainingItems.Item("EmergencyResponse") = 1
Else
dsNewRowTrainingItems.Item("EmergencyResponse") = 0
End If
If chkSlipsTripsFalls.Checked = True Then
dsNewRowTrainingItems.Item("SlipsTripsFalls") = 1
Else
dsNewRowTrainingItems.Item("SlipsTripsFalls") = 0
End If
If chkLadderSafety.Checked = True Then
dsNewRowTrainingItems.Item("LadderSafety") = 1
Else
dsNewRowTrainingItems.Item("LadderSafety") = 0
End If
If chkPPE.Checked = True Then
dsNewRowTrainingItems.Item("PPE") = 1
Else
dsNewRowTrainingItems.Item("PPE") = 0
End If
If chkDrugAlcoholPolicy.Checked = True Then
dsNewRowTrainingItems.Item("DrugAlcoholPolicy") = 1
Else
dsNewRowTrainingItems.Item("DrugAlcoholPolicy") = 0
End If
dsTrainingItems.Tables("TrainingItems").Rows.Add(dsNewRowTrainingItems)
daTrainingItems.Update(dsTrainingItems, "TrainingItems")
Catch ex As Exception
MsgBox(ex.ToString)
End Try
End If
End Sub
Private Sub btnCancel_Click(sender As Object, e As EventArgs) Handles btnCancel.Click
btnPrevious.Enabled = True
btnNext.Enabled = True
btnFirst.Enabled = True
btnLast.Enabled = True
btnAddNew.Enabled = True
btnAddNew.Enabled = True
btnPrint.Enabled = True
btnDelete.Enabled = True
btnUpdate.Enabled = True
btnSave.Enabled = False
btnCancel.Enabled = False
inc = RowCount - 1
NavigateTrainingInfo()
NavigateTrainees()
NavigateTrainingItems()
End Sub
End Class
