dcsimg
Results 1 to 4 of 4

Thread: PLEASE HELP - Cannot add new datarow to access Db

  1. #1

    Thread Starter
    Junior Member
    Join Date
    Nov 2009
    Location
    Bulgaria
    Posts
    18

    PLEASE HELP - Cannot add new datarow to access Db

    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

  2. #2
    Super Moderator si_the_geek's Avatar
    Join Date
    Jul 2002
    Location
    Bristol, UK
    Posts
    41,410

    Re: PLEASE HELP - Cannot add new datarow to access Db

    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)

  3. #3

    Thread Starter
    Junior Member
    Join Date
    Nov 2009
    Location
    Bulgaria
    Posts
    18

    Re: PLEASE HELP - Cannot add new datarow to access Db

    Quote Originally Posted by si_the_geek View Post
    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

  4. #4
    Super Moderator si_the_geek's Avatar
    Join Date
    Jul 2002
    Location
    Bristol, UK
    Posts
    41,410

    Re: PLEASE HELP - Cannot add new datarow to access Db

    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).

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Featured


Click Here to Expand Forum to Full Width