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

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Will Pittenger - 19 Jul 2005 21:29 GMT

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.
----------
Will Pittenger
E-Mail: mailto:will.pittenger1 at gmail.com
All mail filtered by Qurb (www.qurb.com)

Reply to this Message

Nicholas Paldino [.NET/C# MVP] - 19 Jul 2005 21:45 GMT

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.

Signature

- Nicholas Paldino [.NET/C# MVP]
- mvp@spam.guard.caspershouse.com

>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
[quoted text clipped - 11 lines]
> E-Mail: mailto:will.pittenger1 at gmail.com
> All mail filtered by Qurb (www.qurb.com)

Reply to this Message

Will Pittenger - 19 Jul 2005 23:52 GMT

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.
----------
Will Pittenger
E-Mail: mailto:will.pittenger1 at gmail.com
All mail filtered by Qurb (www.qurb.com)

Reply to this Message

Tony - 20 Jul 2005 10:26 GMT

Hi Will,

A OleDbCommandBuilder will generate those statements for you :-

OleDbCommandBuilder myCB = new OleDbCommandBuilder(myDataAdapter);

Cheers.

> 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
[quoted text clipped - 3 lines]
> E-Mail: mailto:will.pittenger1 at gmail.com
> All mail filtered by Qurb (www.qurb.com)

Reply to this Message

Will Pittenger - 20 Jul 2005 17:37 GMT

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.
----------
Will Pittenger
E-Mail: mailto:will.pittenger1 at gmail.com
All mail filtered by Qurb (www.qurb.com)

Reply to this Message

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

Free Magazines