Problems updating Access database using .net

[ZebbCarter]

I have been having problems trying to update a table in MS Access out of a .net project. I had found that if I changed my primary key to an autonumbered field it would then update. I am working with an MDI type system. I have since installed the SP2 update. Now I can update a table from a single form, but still cannot update from a form inside an MDI project.

The error message I get is:
"An unhandled exception of type 'System.InvalidOperationException' occurred in system.data.dll"

Anybody HELP!!!!!!!!!!!!!

Thanks,

ZebbCarter

[Jym]

what is the code you are trying to run ?

[Athley]

Well I agree with the preceding speaker, need some code here. I have myself added records to an Access table with a primary key from a MDI project. So we know that it is possible.

/Leyan

[ZebbCarter]

Thanks for the replies.

The code I am using is associated with a system generated OleDBDataAdapter and DataSet. I created the DataAdapter by dragging the table from a connection shown on the server onto the form. Then cycled through the data adapter configuration wizard to generate all of the Select, Insert, Update, and Delete SQL command statements. Everything indicates that all command statements are correct. I then generated the DataSet directly from the DataAdapter.

My Code is as follows:

DataSet.clear()
DataAdapter.Fill(DataSet)

This code fills a data grid on a form. The data grid uses the DataSet as its data source.

I then make changes to the table, NOT to the primary key, and then try to save the changes to the database using the following command:

DataAdapter.Update(DataSet)

At that point the program crashes and generates the following error:
"An unhandled exception of type 'System.InvalidOperationException' occurred in system.data.dll"

I have tried changing the code to:
DataAdapter.Update(DataSet, "TableName")

This gives the same error.

I appreciate your responses and any help you can give would get my undying thanks as I am up against a brick wall here.

Sincerely,

ZebbCarter

[Athley]

I drew a datagrid and a button on a form and added the following code to the form:

Dim cn As New OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;" _
    & "Data Source=C:\Program Files\Microsoft Office\Office10\Samples\Northwind.mdb;")

Dim da As New OleDb.OleDbDataAdapter("SELECT * FROM employees", cn)
Dim ds As New DataSet()

Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
    cn.Open()
    da.Fill(ds, "employees")
    cn.Close()
    DataGrid1.DataSource = ds
    DataGrid1.DataMember = "employees"
End Sub

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
    Dim cmdb As New OleDb.OleDbCommandBuilder(da)
    da.UpdateCommand = cmdb.GetUpdateCommand
    da.InsertCommand = cmdb.GetInsertCommand
    da.DeleteCommand = cmdb.GetDeleteCommand
    da.Update(ds, "employees")
    DataGrid1.Refresh()
End Sub

This works for me.

/Leyan

[ZebbCarter]

Leyan,

Thank you for the code option.

One question though? Is the database you are using and the table from the Northwind example which comes from MS?

I have used the Northwind example for test purposes. That particular database and tables seem to be updateable, whereas the database which I created and need to use will not update.

Again, thanks for your response and I will try this code tonite and respond if it works or not.

ZebbCarter

[Athley]

Propably has to do with the fileds settings of database then. If you cant get it to work you can mail the database to me or at least take a screenshot of the design of the table and mail that and I can have a try (look).

Or maybe it has to do with security, do you have any security options set for the db?

/Leyan

[Athley]

...... and it is the Northwind db shipped with MS Office XP that I've used..... forgot to answer that, sorry.

/Leyan

[ZebbCarter]

Leyjan,

Thanks for the code example. It WORKS!!!!!

What I had been trying to use was the .net generated DataAdapter and DataSet. There must be some sort of bug in that part of the application.

Again, thanks so much for your time and trouble. At least now I have something that will work, it may be more work on my part then a drag-and-drop, but it is under control and works.

If you ever need some fumble fingered help please don't hesitate to contact me.

Sincerely,

ZebbCarter

[ZebbCarter]

Leyan,

PS: If you ever run across someone who can use the .net generated DataAdapter and DataSet to update an Access database, I would really appreciate hearing about it.

Thanks again,

ZebbCarter

[Royster]

I got the same blank error message that you did, when my program tried to update an Access database. There is an error message there though.

The secret is to put a

Try
[YourDataAdapter].Update
Catch err as Exception
MsgBox(err.Message)
End Try

statement around the update method to see an actual error message that is generated. I found that I had a problem with one of the field names in the database, and this generated an "Error In INSERT INTO Statement". I had a field called "Value", this is a reserved word in Access, so although it isn't bracketed in Access like "Order" would be, it is still a reserved word.

In the code generated by the dataset designer, you can search for any instances of reserved words and put square brackets around them. It worked for me.

[mguilhem]

Don't forget the brackets if you are trying to update an acess database !

I know it thanks to this site (but you show me the path)

http://www.dotnet247.com/247referenc...47/239009.aspx

Bye !