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

Recommended Answers

All 2 Replies

INSERT INTO Statement No Where ???

I've never used an "insert into" statement. I've always just done something like:

dsTrainingInfo.Tables("TrainingInfo").Rows.Add(dsNewRowTrainingInfo)
                daTrainingInfo.Update(dsTrainingInfo, "TrainingInfo")
Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.