Saving Data with DataAdapter to Jet DB

[NYWGUY54]

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 Ligthert[MVP]]

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

 

[Scott M.]

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]]

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

 

[Cor Ligthert[MVP]]

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]

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