473,320 Members | 1,848 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

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

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)
Nov 17 '05 #1
4 3047
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.
--
- Nicholas Paldino [.NET/C# MVP]
- mv*@spam.guard.caspershouse.com

"Will Pittenger" <se***********@see.signature.net> wrote in message
news:%2****************@TK2MSFTNGP14.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.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)

Nov 17 '05 #2
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)
Nov 17 '05 #3
Hi Will,

A OleDbCommandBuilder will generate those statements for you :-

OleDbCommandBuilder myCB = new OleDbCommandBuilder(myDataAdapter);

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

Nov 17 '05 #4
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)
Nov 17 '05 #5

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

1
by: Andrea Sansottera | last post by:
Hi fellows, I was wondering if in the Java API there's a way to easily map a disconnected RowSet to a JTable... I have looked at the doc but i did not found anything. What I want is simply do...
6
by: Paul M. | last post by:
Hi, I am trying to add a dataset to a list box on an asp .net page, the query results in about 20 rows coming back when I run it in access but when I loop through the dataset adding each records...
4
by: Manish Sawjiani | last post by:
Hi Experts, I am just into dot net and i want simple code for adding records into Access table (97) using oledb. There are no queries in the databases and there is only one table: Friends with...
2
by: gigi | last post by:
I have a strange problem with an OleDB call to a stored procedure that returns a rowset. Only the first time I execute the query, after I restart SqlServer, my program crashes because the rowset...
0
by: Lewt | last post by:
I am aware of the 'hack the registry' approach. This is just not a safe way to accomplish this on a user's PC. I need to do this the right way. Is there anyone -perhaps from Microsoft - out there...
12
by: Art | last post by:
Hi everyone I was hoping someone might be able to help me with this. I'm just starting to try to work with MS Access tables through VB.net. In Access I can take an existing table and add a new...
8
by: Bonzol | last post by:
Hi there, VB 2003 1.1 Access Database, oledb, Windows Application I've been trying this for 3 days straight now and CANNOT get this to work, im about to go insane because it is soo simple....
7
by: Miro | last post by:
Im a VB Newbie so I hope I'm going about this in the right direction. I have a simple DB that has 1 Table called DBVersion and in that table the column is CurVersion ( String ) Im trying to...
1
by: vbDavidC | last post by:
I am adding a new record to a table via a dataset/adapter. I have got the following to work for me but I am wondering if there is a better way to do this. I am having to have something in my...
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.