Hi Guys
I am coding as very simple database in VB.net. I have decided to manually code my buttons but for some reason my save button returns with an error. Below is my code for my entire program (I know its not perfect).

This is the error i Get:

An unhandled exception of type 'System.Data.OleDb.OleDbException' occurred in System.Data.dll

Additional information: Syntax error in INSERT INTO statement.

Here is my code:

Imports System.Data.OleDb

Public Class Form1
    Dim con As New OleDb.OleDbConnection
    Dim dbProvider As String
    Dim dbsource As String
    Dim ds As New DataSet
    Dim da As OleDb.OleDbDataAdapter
    Dim sql As String
    Dim inc As Integer
    Dim MaxRows As Integer

    Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
        'TODO: This line of code loads data into the 'FilmsDataSet.Movie' table. You can move, or remove it, as needed.
        Me.MovieTableAdapter.Fill(Me.FilmsDataSet.Movie)
        'setup the connection of our database and initalise the variables we have created
        dbProvider = "Provider=Microsoft.ACE.OLEDB.12.0;"
        dbsource = "Data Source= E:\Resources to sort\Level 2 (To Sort)\U12\VB Programs\Film Database\Films.accdb"
        con.ConnectionString = dbProvider & dbsource
        con.Open()
        sql = "SELECT * FROM Movie"
        da = New OleDb.OleDbDataAdapter(sql, con)
        da.Fill(ds, "Films")
        MaxRows = ds.Tables("Films").Rows.Count
        inc = 0
        NavigateRecords()

        'disable text boxes to prevent modification of data
        txtTitle.Enabled = False
        txtDirector.Enabled = False
        dpDate.Enabled = False
        txtYotube.Enabled = False
        txtIMDB.Enabled = False
        cmbCertificate.Enabled = False
        cmbGenre.Enabled = False

    End Sub

    Private Sub btnFirstRecord_Click(sender As Object, e As EventArgs) Handles btnFirstRecord.Click
        If inc <> 0 Then
            inc = 0
            NavigateRecords()
            btnPrevious.Enabled = False
            btnFirstRecord.Enabled = False
        End If
    End Sub

    Private Sub NavigateRecords()
        txtTitle.Text = ds.Tables("Films").Rows(inc).Item(1)
        txtDirector.Text = ds.Tables("Films").Rows(inc).Item(2)
        cmbCertificate.Text = ds.Tables("Films").Rows(inc).Item(3)
        cmbGenre.Text = ds.Tables("Films").Rows(inc).Item(4)
        dpDate.Text = ds.Tables("Films").Rows(inc).Item(5)
        txtYotube.Text = ds.Tables("Films").Rows(inc).Item(6)
        txtIMDB.Text = ds.Tables("Films").Rows(inc).Item(7)
    End Sub

    Private Sub btnNext_Click(sender As Object, e As EventArgs) Handles btnNext.Click
        If inc <> MaxRows - 1 Then
            inc = inc + 1
            NavigateRecords()
            btnFirstRecord.Enabled = True
            btnPrevious.Enabled = True
        Else
            MessageBox.Show("No More Records;")
        End If
    End Sub

    Private Sub btnPrevious_Click(sender As Object, e As EventArgs) Handles btnPrevious.Click
        If inc > 0 Then
            inc = inc - 1
            NavigateRecords()

        End If
    End Sub

    Private Sub btnLast_Click(sender As Object, e As EventArgs) Handles btnLast.Click
        If inc <> MaxRows - 1 Then
            inc = MaxRows - 1
            NavigateRecords()

        End If
    End Sub

    Private Sub tnAdd_Click(sender As Object, e As EventArgs) Handles tnAdd.Click
        'clear text bxoes of data
        txtTitle.Clear()
        txtDirector.Clear()
        txtYotube.Clear()
        txtIMDB.Clear()
        cmbCertificate.SelectedIndex = 0 'set combo box to top option
        cmbGenre.SelectedIndex = 0 'set combo box to top option

        'enable the the text fields
        txtTitle.Enabled = True
        txtDirector.Enabled = True
        dpDate.Enabled = True
        txtYotube.Enabled = True
        txtIMDB.Enabled = True
        cmbCertificate.Enabled = True
        cmbGenre.Enabled = True

    End Sub

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

        If inc <> -1 Then
            Dim cb As New OleDb.OleDbCommandBuilder(da)
            Dim dsnewrow As DataRow
            dsnewrow = ds.Tables("Films").NewRow()
            dsnewrow.Item("Title") = txtTitle.Text
            dsnewrow.Item("Director") = txtDirector.Text
            dsnewrow.Item("Certificate") = cmbCertificate.Text
            dsnewrow.Item("Genre") = cmbGenre.Text
            dsnewrow.Item("Date") = dpDate.Text
            dsnewrow.Item("IMDBLink") = txtIMDB.Text
            dsnewrow.Item("YoutubeLink") = txtYotube.Text
            ds.Tables("Films").Rows.Add(dsnewrow)
            da.Update(ds, "Films") 'this is where the error is
            NavigateRecords()
            MessageBox.Show("Record Added to Database")
        End If

    End Sub

    Private Sub btnDelete_Click(sender As Object, e As EventArgs) Handles btnDelete.Click
        Dim cb As New OleDb.OleDbCommandBuilder(da)
        ds.Tables("Films").Rows(inc).Delete()
        MaxRows = MaxRows - 1
        inc = 0
        da.Update(ds, "Films")
        NavigateRecords()
    End Sub

End Class

Thanks for any help

This article has been dead for over six months. Start a new discussion instead.