-
Mar 17th, 2011, 08:33 AM
#1
Thread Starter
Junior Member
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
-
Mar 17th, 2011, 08:41 AM
#2
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)
-
Mar 17th, 2011, 08:48 AM
#3
Thread Starter
Junior Member
Re: PLEASE HELP - Cannot add new datarow to access Db
Originally Posted by 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)
si_the_geek you are a LIFE SAVER!
I changed the "Password" field to "Customer_Password" and it works perfectly!
Thanks so much!
Dave
-
Mar 17th, 2011, 09:00 AM
#4
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|