PLEASE HELP - Cannot add new datarow to access Db

[dave12har]

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:

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

[si_the_geek]

The problem is probably due to one of your field names (probably password, and maybe more) being a reserved word, and as such should not be used as a field/table name (it confuses the query parser within the database system).

If you change the name (in the database and your code), it is likely to work correctly.

For more information (including lists of Reserved words), see the article What names should I NOT use for tables/fields/views/stored procedures/...? from our Database Development FAQs/Tutorials (at the top of the Database Development forum)

[dave12har]

The problem is probably due to one of your field names (probably password, and maybe more) being a reserved word, and as such should not be used as a field/table name (it confuses the query parser within the database system).

If you change the name (in the database and your code), it is likely to work correctly.

For more information (including lists of Reserved words), see the article What names should I NOT use for tables/fields/views/stored procedures/...? from our Database Development FAQs/Tutorials (at the top of the Database Development forum)

si_the_geek you are a LIFE SAVER!

I changed the "Password" field to "Customer_Password" and it works perfectly!

Thanks so much!

Dave

[si_the_geek]

No problem.


As you now have it sorted out, could you please do us a little favour, and mark the thread as Resolved?
(this saves time reading for those of us who like to answer questions, and also helps those who search to find answers)

You can do it by clicking on "Thread tools" just above the first post in this thread, then "Mark thread resolved". (like various other features of this site, you need JavaScript enabled in your browser for this to work).