| Groups | Blog | Home |
dotnet ado.net : Saving Data with DataAdapter to Jet DB
I am trying to save changes to a dataset using the dataAdapters update method
to an Access 2003 DB, therefore I'm using an OleDbDataAdapter to accomplish this. I am using a For/Next loop to change a single column of data in the dataTable, & I know this works. The problem arises when I try to write the changes to the Access DB, nothing happens but the code process properly. I use virtually the same code set in another project writing to a SQL server DB & that works just fine. Is there some issue with OLEDB & Jet that this does not work or that i must do something different? Just for laughs I included a code snipit of what i'm doing. Try Conn.Open() daVoters.Update(dsCommittee, "Voters") Conn.Close() dsCommittee.AcceptChanges() Catch ex As OleDb.OleDbException Conn.Close() MessageBox.Show("There was an error updating the Database" & vbCr & vbLf & ex.Message, "Prep Data", MessageBoxButtons.OK, MessageBoxIcon.Error) End Try BTW I can do this by writing each change to the DB as I change it in the datatable, but this requires me to open & close the connection nearly 1/2 million times (there are over400,000 records in the DB) Thanks Gary
Cor, look at his supplied code. He is calling AcceptChanges after he uses
the Update method of the DataAdapter, not before, as you talk about. -Scott [quoted text, click to view] "Cor Ligthert[MVP]" <notmyfirstname@planet.nl> wrote in message news:4FCC8D5B-A7B8-46FE-BD16-E64C235B3A13@microsoft.com... > Hi, > > About 100000000000000000000 times (or probably more) is written in this > newsgroup not to use the Acceptchanges before you do an update. > > The Acceptchanges accept all the changes in the DataSet as done, so there > is nothing anymore to update. > As it is about a complete dataTable or DataSet, then the DataAdapter has > an inbuild AcceptChanges as soon as the update is done. > > Cor > > > "NYWGUY54" <NYWGUY54@discussions.microsoft.com> schreef in bericht > news:92E3B536-5A3C-4CEE-9605-AF25BCF8485F@microsoft.com... >>I am trying to save changes to a dataset using the dataAdapters update >>method >> to an Access 2003 DB, therefore I'm using an OleDbDataAdapter to >> accomplish >> this. >> I am using a For/Next loop to change a single column of data in the >> dataTable, & I know this works. The problem arises when I try to write >> the >> changes to the Access DB, nothing happens but the code process properly. >> I >> use virtually the same code set in another project writing to a SQL >> server DB >> & that works just fine. Is there some issue with OLEDB & Jet that this >> does >> not work or that i must do something different? >> Just for laughs I included a code snipit of what i'm doing. >> >> Try >> Conn.Open() >> daVoters.Update(dsCommittee, "Voters") >> Conn.Close() >> dsCommittee.AcceptChanges() >> Catch ex As OleDb.OleDbException >> Conn.Close() >> MessageBox.Show("There was an error updating the Database" & >> vbCr & vbLf & ex.Message, "Prep Data", MessageBoxButtons.OK, >> MessageBoxIcon.Error) >> >> End Try >> >> BTW I can do this by writing each change to the DB as I change it in the >> datatable, but this requires me to open & close the connection nearly 1/2 >> million times (there are over400,000 records in the DB) >> >> Thanks >> Gary >> >> >
Consider that when you write to a JET database, the data is NOT written to
the database--at least not immediately. Changes are saved to a local cache and when the engine goes into an idle state, the changes are posted to the file. I would let the application rest for awhile after writing. Another approach is to encapsulate the operations in a transaction. This reduces performance (as the engine writes each change and keeps a rollback state) but it seems to work. Of course, that's assuming I would use JET at all... which I wouldn't. I would first consider SQL Compact or some DBMS engine that I can encrypt. -- __________________________________________________________________________ William R. Vaughn President and Founder Beta V Corporation Author, Mentor, Dad, Grandpa Microsoft MVP (425) 556-9205 (Pacific time) Hitchhiker’s Guide to Visual Studio and SQL Server (7th Edition) ____________________________________________________________________________________________ [quoted text, click to view] "NYWGUY54" <NYWGUY54@discussions.microsoft.com> wrote in message
news:92E3B536-5A3C-4CEE-9605-AF25BCF8485F@microsoft.com... >I am trying to save changes to a dataset using the dataAdapters update >method > to an Access 2003 DB, therefore I'm using an OleDbDataAdapter to > accomplish > this. > I am using a For/Next loop to change a single column of data in the > dataTable, & I know this works. The problem arises when I try to write the > changes to the Access DB, nothing happens but the code process properly. I > use virtually the same code set in another project writing to a SQL server > DB > & that works just fine. Is there some issue with OLEDB & Jet that this > does > not work or that i must do something different? > Just for laughs I included a code snipit of what i'm doing. > > Try > Conn.Open() > daVoters.Update(dsCommittee, "Voters") > Conn.Close() > dsCommittee.AcceptChanges() > Catch ex As OleDb.OleDbException > Conn.Close() > MessageBox.Show("There was an error updating the Database" & > vbCr & vbLf & ex.Message, "Prep Data", MessageBoxButtons.OK, > MessageBoxIcon.Error) > > End Try > > BTW I can do this by writing each change to the DB as I change it in the > datatable, but this requires me to open & close the connection nearly 1/2 > million times (there are over400,000 records in the DB) > > Thanks > Gary > >
Hi,
About 100000000000000000000 times (or probably more) is written in this newsgroup not to use the Acceptchanges before you do an update. The Acceptchanges accept all the changes in the DataSet as done, so there is nothing anymore to update. As it is about a complete dataTable or DataSet, then the DataAdapter has an inbuild AcceptChanges as soon as the update is done. Cor "NYWGUY54" <NYWGUY54@discussions.microsoft.com> schreef in bericht news:92E3B536-5A3C-4CEE-9605-AF25BCF8485F@microsoft.com... [quoted text, click to view] >I am trying to save changes to a dataset using the dataAdapters update
>method > to an Access 2003 DB, therefore I'm using an OleDbDataAdapter to > accomplish > this. > I am using a For/Next loop to change a single column of data in the > dataTable, & I know this works. The problem arises when I try to write the > changes to the Access DB, nothing happens but the code process properly. I > use virtually the same code set in another project writing to a SQL server > DB > & that works just fine. Is there some issue with OLEDB & Jet that this > does > not work or that i must do something different? > Just for laughs I included a code snipit of what i'm doing. > > Try > Conn.Open() > daVoters.Update(dsCommittee, "Voters") > Conn.Close() > dsCommittee.AcceptChanges() > Catch ex As OleDb.OleDbException > Conn.Close() > MessageBox.Show("There was an error updating the Database" & > vbCr & vbLf & ex.Message, "Prep Data", MessageBoxButtons.OK, > MessageBoxIcon.Error) > > End Try > > BTW I can do this by writing each change to the DB as I change it in the > datatable, but this requires me to open & close the connection nearly 1/2 > million times (there are over400,000 records in the DB) > > Thanks > Gary > >
Scott
You are right , and you won't believe this, I have checked this more than one time, Probably the way the code showed up here, Thanks for the correction. Cor "Scott M." <smar@nospam.nospam> schreef in bericht news:%23nxNuBioIHA.4308@TK2MSFTNGP06.phx.gbl... [quoted text, click to view] > Cor, look at his supplied code. He is calling AcceptChanges after he
> uses the Update method of the DataAdapter, not before, as you talk about. > > -Scott > > "Cor Ligthert[MVP]" <notmyfirstname@planet.nl> wrote in message > news:4FCC8D5B-A7B8-46FE-BD16-E64C235B3A13@microsoft.com... >> Hi, >> >> About 100000000000000000000 times (or probably more) is written in this >> newsgroup not to use the Acceptchanges before you do an update. >> >> The Acceptchanges accept all the changes in the DataSet as done, so there >> is nothing anymore to update. >> As it is about a complete dataTable or DataSet, then the DataAdapter has >> an inbuild AcceptChanges as soon as the update is done. >> >> Cor >> >> >> "NYWGUY54" <NYWGUY54@discussions.microsoft.com> schreef in bericht >> news:92E3B536-5A3C-4CEE-9605-AF25BCF8485F@microsoft.com... >>>I am trying to save changes to a dataset using the dataAdapters update >>>method >>> to an Access 2003 DB, therefore I'm using an OleDbDataAdapter to >>> accomplish >>> this. >>> I am using a For/Next loop to change a single column of data in the >>> dataTable, & I know this works. The problem arises when I try to write >>> the >>> changes to the Access DB, nothing happens but the code process properly. >>> I >>> use virtually the same code set in another project writing to a SQL >>> server DB >>> & that works just fine. Is there some issue with OLEDB & Jet that this >>> does >>> not work or that i must do something different? >>> Just for laughs I included a code snipit of what i'm doing. >>> >>> Try >>> Conn.Open() >>> daVoters.Update(dsCommittee, "Voters") >>> Conn.Close() >>> dsCommittee.AcceptChanges() >>> Catch ex As OleDb.OleDbException >>> Conn.Close() >>> MessageBox.Show("There was an error updating the Database" & >>> vbCr & vbLf & ex.Message, "Prep Data", MessageBoxButtons.OK, >>> MessageBoxIcon.Error) >>> >>> End Try >>> >>> BTW I can do this by writing each change to the DB as I change it in the >>> datatable, but this requires me to open & close the connection nearly >>> 1/2 >>> million times (there are over400,000 records in the DB) >>> >>> Thanks >>> Gary >>> >>> >> > >
Hi,
Sorry for my misread, Did you investigate your connection string or maybe your SQL transact code, be aware that SQL for OleDB is not always the same as for SQL server. Cor "NYWGUY54" <NYWGUY54@discussions.microsoft.com> schreef in bericht news:92E3B536-5A3C-4CEE-9605-AF25BCF8485F@microsoft.com... [quoted text, click to view] >I am trying to save changes to a dataset using the dataAdapters update
>method > to an Access 2003 DB, therefore I'm using an OleDbDataAdapter to > accomplish > this. > I am using a For/Next loop to change a single column of data in the > dataTable, & I know this works. The problem arises when I try to write the > changes to the Access DB, nothing happens but the code process properly. I > use virtually the same code set in another project writing to a SQL server > DB > & that works just fine. Is there some issue with OLEDB & Jet that this > does > not work or that i must do something different? > Just for laughs I included a code snipit of what i'm doing. > > Try > Conn.Open() > daVoters.Update(dsCommittee, "Voters") > Conn.Close() > dsCommittee.AcceptChanges() > Catch ex As OleDb.OleDbException > Conn.Close() > MessageBox.Show("There was an error updating the Database" & > vbCr & vbLf & ex.Message, "Prep Data", MessageBoxButtons.OK, > MessageBoxIcon.Error) > > End Try > > BTW I can do this by writing each change to the DB as I change it in the > datatable, but this requires me to open & close the connection nearly 1/2 > million times (there are over400,000 records in the DB) > > Thanks > Gary > >
Thanks everyone,
I finally figured out what i had wrong. My code (sample) does work when I add this before it : Dim cb_voters As OleDbCommandBuilder = New OleDbCommandBuilder(daVoters) This will build a default update command. I instantiate all my dataadapters in code (not as objects) & forgot to assign an updatecommand. The odd thing is when I wrote another piece of code to test this the debugger threw an error that told me I needed an update command, my primary code didn't through the error, just processed it and moved on & that is what confounded me. William, as far as the DB goes, you are correct I don't want to use Jet either, but this has to be both low cost & very portable. But if you know of another DB that i can simple copy from machine to machine (with no install) that would perform better than Jet (Access), I'll gladly take your advice. Thanks Gary [quoted text, click to view] "Cor Ligthert[MVP]" wrote:
> Hi, > > Sorry for my misread, > > Did you investigate your connection string or maybe your SQL transact code, > be aware that SQL for OleDB is not always the same as for SQL server. > > Cor > > "NYWGUY54" <NYWGUY54@discussions.microsoft.com> schreef in bericht > news:92E3B536-5A3C-4CEE-9605-AF25BCF8485F@microsoft.com... > >I am trying to save changes to a dataset using the dataAdapters update > >method > > to an Access 2003 DB, therefore I'm using an OleDbDataAdapter to > > accomplish > > this. > > I am using a For/Next loop to change a single column of data in the > > dataTable, & I know this works. The problem arises when I try to write the > > changes to the Access DB, nothing happens but the code process properly. I > > use virtually the same code set in another project writing to a SQL server > > DB > > & that works just fine. Is there some issue with OLEDB & Jet that this > > does > > not work or that i must do something different? > > Just for laughs I included a code snipit of what i'm doing. > > > > Try > > Conn.Open() > > daVoters.Update(dsCommittee, "Voters") > > Conn.Close() > > dsCommittee.AcceptChanges() > > Catch ex As OleDb.OleDbException > > Conn.Close() > > MessageBox.Show("There was an error updating the Database" & > > vbCr & vbLf & ex.Message, "Prep Data", MessageBoxButtons.OK, > > MessageBoxIcon.Error) > > > > End Try > > > > BTW I can do this by writing each change to the DB as I change it in the > > datatable, but this requires me to open & close the connection nearly 1/2 > > million times (there are over400,000 records in the DB) > > > > Thanks > > Gary > > > >
The SQL Server Compact .SDF database is small, fast and light, requires no
"install" (there are 6 or so DLLs that can be attached to the project) and can be passed around just like a JET database. It's also free. See my EBook at www.hitchhikerguides.net. -- __________________________________________________________________________ William R. Vaughn President and Founder Beta V Corporation Author, Mentor, Dad, Grandpa Microsoft MVP (425) 556-9205 (Pacific time) Hitchhiker’s Guide to Visual Studio and SQL Server (7th Edition) ____________________________________________________________________________________________ [quoted text, click to view] "NYWGUY54" <NYWGUY54@discussions.microsoft.com> wrote in message
news:E967B937-0913-478D-B552-C6BC4D2F6714@microsoft.com... > Thanks everyone, > I finally figured out what i had wrong. My code (sample) does work when I > add this before it : > Dim cb_voters As OleDbCommandBuilder = New OleDbCommandBuilder(daVoters) > This will build a default update command. I instantiate all my > dataadapters > in code (not as objects) & forgot to assign an updatecommand. The odd > thing > is when I wrote another piece of code to test this the debugger threw an > error that told me I needed an update command, my primary code didn't > through > the error, just processed it and moved on & that is what confounded me. > William, as far as the DB goes, you are correct I don't want to use Jet > either, but this has to be both low cost & very portable. But if you know > of > another DB that i can simple copy from machine to machine (with no > install) > that would perform better than Jet (Access), I'll gladly take your advice. > Thanks > Gary > > "Cor Ligthert[MVP]" wrote: > >> Hi, >> >> Sorry for my misread, >> >> Did you investigate your connection string or maybe your SQL transact >> code, >> be aware that SQL for OleDB is not always the same as for SQL server. >> >> Cor >> >> "NYWGUY54" <NYWGUY54@discussions.microsoft.com> schreef in bericht >> news:92E3B536-5A3C-4CEE-9605-AF25BCF8485F@microsoft.com... >> >I am trying to save changes to a dataset using the dataAdapters update >> >method >> > to an Access 2003 DB, therefore I'm using an OleDbDataAdapter to >> > accomplish >> > this. >> > I am using a For/Next loop to change a single column of data in the >> > dataTable, & I know this works. The problem arises when I try to write >> > the >> > changes to the Access DB, nothing happens but the code process >> > properly. I >> > use virtually the same code set in another project writing to a SQL >> > server >> > DB >> > & that works just fine. Is there some issue with OLEDB & Jet that this >> > does >> > not work or that i must do something different? >> > Just for laughs I included a code snipit of what i'm doing. >> > >> > Try >> > Conn.Open() >> > daVoters.Update(dsCommittee, "Voters") >> > Conn.Close() >> > dsCommittee.AcceptChanges() >> > Catch ex As OleDb.OleDbException >> > Conn.Close() >> > MessageBox.Show("There was an error updating the Database" & >> > vbCr & vbLf & ex.Message, "Prep Data", MessageBoxButtons.OK, >> > MessageBoxIcon.Error) >> > >> > End Try >> > >> > BTW I can do this by writing each change to the DB as I change it in >> > the >> > datatable, but this requires me to open & close the connection nearly >> > 1/2 >> > million times (there are over400,000 records in the DB) >> > >> > Thanks >> > Gary >> > >> > >>
Don't see what you're looking for? Try a search.
|
June 2003
July 2003
August 2003
September 2003
October 2003
November 2003
December 2003
January 2004
February 2004
March 2004
April 2004
May 2004
June 2004
July 2004
August 2004
September 2004
October 2004
November 2004
December 2004
January 2005
February 2005
March 2005
April 2005
May 2005
June 2005
July 2005
August 2005
September 2005
October 2005
November 2005
December 2005
January 2006
February 2006
March 2006
April 2006
May 2006
June 2006
July 2006
August 2006
September 2006
October 2006
November 2006
December 2006
January 2007
February 2007
March 2007
April 2007
May 2007
June 2007
July 2007
August 2007
September 2007
October 2007
November 2007
December 2007
January 2008
February 2008
March 2008
April 2008
| home · blog · groups | Questions? Comments? Contact the dn |