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.Dat aTable.Rows.Add , and
System.Data.Dat aSet.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.pit tenger1 at gmail.com
All mail filtered by Qurb (www.qurb.com)

[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.Ole Db 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
OleDbDataAdapte r (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.
--
- Nicholas Paldino [.NET/C# MVP]
- mv*@spam.guard. caspershouse.co m

"Will Pittenger" <se***********@ see.signature.n et> wrote in message
news:%2******** ********@TK2MSF TNGP14.phx.gbl. ..

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.Dat aTable.Rows.Add , and
System.Data.Da taSet.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.pit tenger1 at gmail.com
All mail filtered by Qurb (www.qurb.com)

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

[Tony]

Hi Will,

A OleDbCommandBui lder will generate those statements for you :-

OleDbCommandBui lder myCB = new OleDbCommandBui lder(myDataAdap ter);

Cheers.

"Will Pittenger" wrote:

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.pit tenger1 at gmail.com
All mail filtered by Qurb (www.qurb.com)

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