Hi All,

I am tearing my hair out trying here!

I am developing a VB.net application with an Access database. I have the database connected fine to allow me edit existing customers and update the dataset and the database. However when I try to add a new customer to the database - i.e a new row it causes and error when I try to update the database. The error is "Syntax error in INSERT INTO statement."

This is my code:
Code:
Imports System.Data
Imports System.Data.OleDb

Module Module1

    Friend objConnection As New OleDb.OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source = ordersystem.accdb")
    Friend objCustomerDA As New OleDb.OleDbDataAdapter("Select *from Customer_Table", objConnection)
    Friend objCustomerCB As New OleDb.OleDbCommandBuilder(objCustomerDA)
    Friend objDataSet As New DataSet()

    Friend objOrderDA As New OleDb.OleDbDataAdapter("Select *from Order_Table", objConnection)
    Friend objOrderCB As New OleDb.OleDbCommandBuilder(objOrderDA)

End Module

 Private Sub btnSaveNew_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSaveNew.Click

        Dim objRowCustomer As DataRow
        Dim objRowOrder As DataRow

        Dim CustID As String
        Dim Name As String = txtName.Text
        Dim Address As String = txtAddress.Text
        Dim Telephone As String = txtTelephone.Text
        Dim pass As String = txtPassword.Text

        objCustomerDA.FillSchema(objDataSet, SchemaType.Source, "Customer_Table")
        objCustomerDA.Fill(objDataSet, "Customer_Table")

        objOrderDA.FillSchema(objDataSet, SchemaType.Source, "Order_Table")
        objOrderDA.Fill(objDataSet, "Order_Table")

        objDataSet.Relations.Clear()
        objDataSet.Relations.Add("Customer_Table2Order_Table", objDataSet.Tables("Customer_Table").Columns("Customer_ID"), objDataSet.Tables("Order_Table").Columns("Customer_ID"))

        objRowCustomer = objDataSet.Tables("Customer_Table").NewRow()

        objRowCustomer.Item("Customer_Name") = Name
        objRowCustomer.Item("Address") = Address
        objRowCustomer.Item("Telephone") = Telephone
        objRowCustomer.Item("Password") = pass

        objDataSet.Tables("Customer_Table").Rows.Add(objRowCustomer)
        objCustomerDA.Update(objDataSet, "Customer_Table")

        objRowOrder = objDataSet.Tables("Order_Table").NewRow
        objRowOrder.Item("Customer_ID") = objRowCustomer.Item("Customer_ID")
        objRowOrder.Item("Phone_Balance") = 0

        objDataSet.Tables("Order_Table").Rows.Add(objRowOrder)
        objOrderDA.Update(objDataSet, "Order_Table")

        Update()


    End Sub
I think it is just a small thing which is causing this error. I have spent hours going through another example that works perfectly and I cannot find any discrepancies!

I hope you can help me. Really appreciate it!

Thanks,

Dave