Adding row to OLEDB Rowset does not add row to Access db

[Will Pittenger]

I have a project where the backend is Access. I have learned (slowly) to
use OLEDB to access my data. However, to save changes, I need to generate a
SQL statement. This seems clunky. I am used to MFC recordsets. The OleDB
system will let me add the row to the rowset, but then does nothing with it.
I have tried calling BeginEdit/EndEdit, AcceptChanges (from several
different classes), System.Data.DataTable.Rows.Add, and
System.Data.DataSet.Update. They appear to add the row to the OLEDB version
of the table, but until Access sees a new row, it is worthless.

For now, I am generating SQL Insert, Update, and Delete queries as needed.
However, that code is not reusable. Is there a better way? I hope so.

 

[Nicholas Paldino [.NET/C# MVP]]

Will,

While you are using OLEDB to access your Access data, you are not using
OLEDB when you are using the DataTable, DataSet, and other classes. The
provider you are using is OleDb (because you are using classes in the
System.Data.OleDb namespace).

That being said, the dataset in .NET is a disconnected recordset,
meaning that it is populated with data, and no connection to the database is
retained. In order to update the dataset, you need to create an
OleDbDataAdapter (which you did already because you selected the information
to fill the dataset) and set the InsertCommand, DeleteCommand, and
UpdateCommand properties to instances of OleDbCommand classes which
represent the insert, delete, and update statements. Then, pass your
changed recordset to the Update method on the adapter, and it will take care
of updating the back end.

Hope this helps.

 

[Will Pittenger]

So, it still will not generate the Update, Delete, and Insert statements for
me like the MFC recordsets? With a simple table (rather than a join), I did
not even need to generate the Select statement using MFC. I miss that.

 

[Guest]

Hi Will,

A OleDbCommandBuilder will generate those statements for you :-

OleDbCommandBuilder myCB = new OleDbCommandBuilder(myDataAdapter);

Cheers.

 

[Will Pittenger]

The problem is that MS assumed that the person coding, me, knows a lot about
OLEDB. I know little to nothing about OLEDB. MFC recordsets I understand.
OleDB on the other hand, appears designed to confuse programmers. Nothing
is strait forward.

The class you mentioned is just sitting out there. There is little to no
discussion about what to do with said class.