Syntax Error!

Question

Hey, I'm having a small prob trying to add a new entry to my access databse,

the program code is:

Imports System.Data

Public Class Form1
    Dim inc As Integer
    Dim MaxRows As Integer

    Dim con As New OleDb.OleDbConnection
    Dim ds As New DataSet
    Dim da As OleDb.OleDbDataAdapter
    Dim sql As String
    Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        Me.tblRecordsTableAdapter.Fill(Me.BikelogDataSet.tblRecords)
        con.ConnectionString = "PROVIDER=Microsoft.Jet.OLEDB.4.0;Data Source = C:\VBWORK\WorkshopLog\WorkshopLog\bikelog.mdb"
        con.Open()

        sql = "SELECT * FROM tblRecords"
        da = New OleDb.OleDbDataAdapter(sql, con)
        da.Fill(ds, "bikelog")

        con.Close()
        MaxRows = ds.Tables("bikelog").Rows.Count
        inc = -1
    End Sub

    Private Sub btnAddnew_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnAddnew.Click
        Dim cb As New OleDb.OleDbCommandBuilder(da)
        Dim dsNewRow As DataRow

        dsNewRow = ds.Tables("bikelog").NewRow()

        dsNewRow.Item("Customer") = txtcName.Text
        dsNewRow.Item("Bike") = txtBike.Text
        dsNewRow.Item("Job") = txtJob.Text
        dsNewRow.Item("Parts") = txtParts.Text
        dsNewRow.Item("Labour") = txtLabour.Text
        dsNewRow.Item("Frame Number") = txtFrame.Text
        dsNewRow.Item("Mechanic") = txtMech.Text
        dsNewRow.Item("Date") = System.DateTime.Now

        ds.Tables("bikelog").Rows.Add(dsNewRow)
        'Me.tblRecordsTableAdapter.Update(Me.BikelogDataSet.tblRecords)

        da.Update(ds, "bikelog")

        MsgBox("New Record added to the Database")
    End Sub
End Class

Now when I run it, I get:

System.Data.OleDb.OleDbException was unhandled
  ErrorCode=-2147217900
  Message="Syntax error in INSERT INTO statement."
  Source="Microsoft JET Database Engine"
  StackTrace:
       at System.Data.Common.DbDataAdapter.UpdatedRowStatusErrors(RowUpdatedEventArgs rowUpdatedEvent, BatchCommandInfo[] batchCommands, Int32 commandCount)    at System.Data.Common.DbDataAdapter.UpdatedRowStatus(RowUpdatedEventArgs rowUpdatedEvent, BatchCommandInfo[] batchCommands, Int32 commandCount)    at System.Data.Common.DbDataAdapter.Update(DataRow[] dataRows, DataTableMapping tableMapping)    at System.Data.Common.DbDataAdapter.UpdateFromDataTable(DataTable dataTable, DataTableMapping tableMapping)    at System.Data.Common.DbDataAdapter.Update(DataSet dataSet, String srcTable)    at WorkshopLog.Form1.btnAddnew_Click(Object sender, EventArgs e) in C:\VBWORK\WorkshopLog\WorkshopLog\Form1.vb:line 43    at System.Windows.Forms.Control.OnClick(EventArgs e)    at System.Windows.Forms.Button.OnClick(EventArgs e)    at System.Windows.Forms.Button.OnMouseUp(MouseEventArgs mevent)    at System.Windows.Forms.Control.WmMouseUp(Message& m, MouseButtons button, Int32 clicks)    at System.Windows.Forms.Control.WndProc(Message& m)    at System.Windows.Forms.ButtonBase.WndProc(Message& m)    at System.Windows.Forms.Button.WndProc(Message& m)    at System.Windows.Forms.Control.ControlNativeWindow.OnMessage(Message& m)    at System.Windows.Forms.Control.ControlNativeWindow.WndProc(Message& m)    at System.Windows.Forms.NativeWindow.DebuggableCallback(IntPtr hWnd, Int32 msg, IntPtr wparam, IntPtr lparam)    at System.Windows.Forms.UnsafeNativeMethods.DispatchMessageW(MSG& msg)    at System.Windows.Forms.Application.ComponentManager.System.Windows.Forms.UnsafeNativeMethods.IMsoComponentManager.FPushMessageLoop(Int32 dwComponentID, Int32 reason, Int32 pvLoopData)    at System.Windows.Forms.Application.ThreadContext.RunMessageLoopInner(Int32 reason, ApplicationContext context)    at System.Windows.Forms.Application.ThreadContext.RunMessageLoop(Int32 reason, ApplicationContext context)    at System.Windows.Forms.Application.Run(ApplicationContext context)    at Microsoft.VisualBasic.ApplicationServices.WindowsFormsApplicationBase.OnRun()    at Microsoft.VisualBasic.ApplicationServices.WindowsFormsApplicationBase.DoApplicationModel()    at Microsoft.VisualBasic.ApplicationServices.WindowsFormsApplicationBase.Run(String[] commandLine)    at WorkshopLog.My.MyApplication.Main(String[] Args) in 17d14f5c-a337-4978-8281-53493378c1071.vb:line 81
  InnerException: 

Syntax error in INSERT TO

and I can't figure out why at all! 

Can anyone help a newbie out?

Answer 1

Hello,

To send the changes to the database, we may need to build a CommandBuilder first, for example:
        Dim objCommandBuilder As New OleDbCommandBuilder(da)
        daAuthors.Update(ds, "Test")
        MsgBox("updated successfully")

Will you please have a try and tell me the result?
For the walkthrough on updating database using DataSet, I recommend you refer to:
http://support.microsoft.com/kb/301248/en-us
The sample there is using SQL Server, however, the process is almost the same with a Access database, I believe it could help you.

By the way, you may post threads in a VB.NET forum, basically, it is off-topic here.
Thanks a lot!

---

Please remember to mark the replies as answers if they help and unmark them if they provide no help.
If you have any feedback, please tell us.
Welcome to the All-In-One Code Framework!

Answer 2

Sorry but I have already included that code?

Dim cb As New OleDb.OleDbCommandBuilder(da)

Answer 3

Hello,

Could I ask if we checked the type? are they matched? Could we generate a small project of the issue? If you will, please zipped the project to my email account: [e-mail]

Thanks a lot!

---

Please remember to mark the replies as answers if they help and unmark them if they provide no help.
If you have any feedback, please tell us.
Welcome to the All-In-One Code Framework!