How to edit the records in datagridview and update changes into ms access data base?

Question

When i tried with the code discussed in this forum i am able to add records but not modify any particular existing record. throwed an error "Syntax error (missing operator) in query expression '((Mailing ListID = ?) AND ((? = 1 AND FirstName IS NULL) OR (FirstName = ?)) AND ((? = 1 AND MiddleName IS NULL) OR (MiddleName = ?)) AND ((? = 1 AND LastName IS NULL) OR (LastName = ?)) AND ((? = 1 AND Suffix IS NULL) OR (Suffix = ?)) AND ((? = 1 AND Nic'.

code snippet

'to access db

Dim

con As New OleDb.OleDbConnection

con.ConnectionString =

"PROVIDER=Microsoft.Jet.OLEDB.4.0;Data Source = D:\Access Test\TestDataBase.mdb"

con.Open()

sql =

"SELECT * FROM ProductivityDetails1"

da =

New OleDb.OleDbDataAdapter(sql, con)

 

Dim builder As OleDb.OleDbCommandBuilder = New OleDb.OleDbCommandBuilder(da)

ds =

New DataSet()

da.Fill(ds,

"Productivity Details")

DataGridView1.DataSource = ds.Tables(

"Productivity Details").DefaultView

con.Close()

con =

Nothing

'to save changes

Me

.Validate()

 

Me.da.Update(Me.ds.Tables("Productivity Details"))

 

Me.ds.AcceptChanges()

Answer 1

I don't use the designers, so I don't know how much I can help, but the error you are getting is because the SQL statement you have is not valid.

It should instead start with something like:

Update myTableName
Set myFieldName = value,
      myFieldName2 = value2,
      ...

What you have looks like part of a where clause?

---

www.insteptech.com ; msmvps.com/blogs/deborahk
We are volunteers and ask only that if we are able to help you, that you mark our reply as your answer. THANKS!

Answer 2

Hi Jack,

Welcome to MSDN forums.
I can't see any problem with the code you posted above. I think the issue is caused by other place, such as datatable in database. Did you set a column as primary key  in your table?  If you want to use commandbuilder to update changes, a primary key is needed.

Here are four approaches about how to update access database.
1)  Via DataGridView: Update (Insert/Update/Delete) data back into MS Access database from DataGridView.
Code sample: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=2758290&SiteID=1 

2)  Execute Select/Insert/Delete/Update T-SQL commands in code
Code sample: http://forums.msdn.microsoft.com/en-US/vbgeneral/thread/87913f28-992d-4705-963b-cb0ffa53d8dd/ 

3)  Using Data Wizard with a BindingNavigator control.
Please check the 11th post in this thread for detailed walkthrough:
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=2471782&SiteID=1 

4)  Operate DataSet/DataTable in code
Please check the 12th post and 13th post in this thread for detailed code sample:
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=2471782&SiteID=1

For more information, refer to the following topics in the Visual Studio .NET Help documentation:
Data Walkthroughs
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/vbcon/html/vboriDataWalkthroughs.asp

OleDbDataAdapter Class
http://msdn2.microsoft.com/en-us/library/system.data.oledb.oledbdataadapter(vs.71).aspx

SqlDataAdapter Class
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpref/html/frlrfSystemDataSqlClientSqlDataAdapterClassTopic.asp

OleDbCommandBuilder Class
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpref/html/frlrfSystemDataOleDbOleDbCommandBuilderClassTopic.asp

SqlCommandBuilder Class
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpref/html/frlrfSystemDataSqlClientSqlCommandBuilderClassTopic.asp


Hope this helps

Regards
Jeff Shan

---

Please remember to mark the replies as answers if they help and unmark them if they provide no help.

Answer 3

Create a new blank project and simple table test it, it works ok.

Answer 4

Thank You Jeff. You really hav given me piles of info. I shall go through and getback.

Cheers
Jack