DataAdapter Not Updating

[Doug Bell]

Hi,
I thought I had this sorted this morning but it is still a problem.

My application has a DataAccess Class.

When it starts, it:
Connects to a DB (OLE DB)
If it connects it uses an OleDbCommand with an SQL String and the connection
it has a DataAdapter with the command
then it fills the DataSet's DataTable with the streamed data.

It does this for 6 DataTables,
then it closes the connection.

That all works fine including saving the DataSet to an XML file and if the
connection to the DataBase is not successful, it reloads the last know good
DataSet from the pre-Saved XML.

What I am trying to achieve is to periodically (once an hour or on demand )
update 4 of the DataTables from the DataBase.

The code for this is:

ConnectMyDB() sub to connect to the Database

If MyDBConnected Then
If Not HasDownloaded Then
'Do the first download stuff
Else
daMyTable1.Update (dsMyDataSet, "MyDataTable1")
daMyTable2.Update (dsMyDataSet, "MyDataTable2")
daMyTable3.Update (dsMyDataSet, "MyDataTable3")
daMyTable4.Update (dsMyDataSet, "MyDataTable4")
End If

This runs without error but the DataSet is not updated with any of the
changes that have occured at the DataBase.

Am I missing something?

Thanks Doug

 

[Guest]

To Update the Database, you need to have an Update statement. You can try to
use the OleDb.OleDbCommandBuilder, and an OleDB.Parameter for each item to be
updated. The statement it builds is complex, and a bit daunting.

You could just use the values from the controls that represent the record to
be updated, and concatenate an Update statement for each database table. Set
the CommandText property of your OleDbCommand to the Update string. Then
open your connection and run the Command.ExecuteNonQuery method. Then close
the connection.

This is the old fashioned way of getting data back to the data store, but I
think it really is easier and more straight forward than using the
CommandBuilder. You could still assign the various OleDbCommand objects to a
DataAdapter, if you prefer, but you still need to create and assign the
CommandText for the Commands. Test it though because I think that
DataAdapter.Update runs the Insert and Delete Commands too, if such changes
have been made to a datatable.

My preference is to use the DataAdapter to Fill, and stand alone Commands to
write back.

Of course, if you build your own statements, be sure to set your WHERE
clause to the primary key of the record being updated. Most of us have
omitted that at least once, and gasped when ALL records got updated.

www.charlesfarriersoftware.com

 

[Doug Bell]

Charlie,
I do not want to update the Database!

I want to update 4 of the 6 DataTables in the DataSet!

Doug

 

[Guest]

Calling the DataAdapter update method activates the Insert, Update, and
Delete commands that are assigned to the DataAdapter. If you are trying to
refresh the data in a datatable from the database, you can use the
Datatable.Rows.Clear method, and then used the DataAdapter Fill method again
using the DataAdapter you have set up for that Datatable.

Did I understand you correctly this time?

 

[Doug Bell]

Charlie,
Yes, that is what I want to achieve.
I realise that the DataAdapter eficiently streams data. I was hoping that it
could use DeltaGrams like the Update uses to update the Database Tables but
I guess that would require the Database keeping track of the data that it
had originally streamed down.

If the connection fails between the start of clearing the data and the then
the reloading using the Fill Method is there any way to roll back like in a
transaction?

Thanks

Doug

 

[Guest]

You could open the connection in a try block before clearing the datatable
rows. Do you have trouble with connections failing after a successful open?
If so, could you use the xml file your referred to in the first post as your
backup?

 

[Guest]

You could also create a new Datatable and copy the original.
dim DT2 as new datatable()
DT2 = DT1.Copy
This creates an independent datatable object that you could use in case of
connection failure.

 

[Cor Ligthert]

Hi Doug,

I get the idea you are probably using the words "fill" and "update" in its
exact meaning in relation to each other.

Dataadapter.fill(datatable) (should update) and fill a datatable in a
dataset from a database
Dataadapter.update(ds, datatable) updates and fills a database from a
dataset datatable.

When you have autokeys, you can forget the first one for update, because the
old rows are not automaticly deleted. It does as well not add new database
rows added by others. So the most normal one would be the dataset.clear and
than that dataadapter.fill

(Another approach can be to use timestamps in your database or /and maybe
where I think on now and never tried, use the negative seed from the autokey
and delete rows with a negative keynumber directly after the update.
However, in the last case you do not get new rows added by others than the
user).

However mostly in a small dataset is the fill that fast, that doing all
those difficult things would not be needed and you refresh the datast as I
wrote above after the update from the database.

I hope this helps?

Cor

 

[Doug Bell]

Cor thanks,

I thought initially that the update, although designed to update the
database with the Local User's changes (ie the DataSet) might also update
the Local DataSet with any changes that had occured to the DataBase; ie like
replication.

But I realise, now why that can not occur. I will have to use the Clear and
Fill and build some checks to get around any failures.

Thanks

Doug

 

[Doug Bell]

Charlie,

Thanks,
It is more of a question of the connection dropping out rather than not
being able to open. It will not open for a period each night while it is
busy doing End-Of-Day and a longer period at night once a month
(End-Of-Month) but the real issue is that it has to connect from a number of
remote sites around Australia with some very questionable bandwidth etc.


Doug

 

[Doug Bell]

Again thanks,
That is the key purpose of the XML file but if some of the data updates and
some doesn't, I may as well try and use the newer data where I can except
where it will cause referential integrity problems.

I am slowly getting on top of Dot Net.

Thanks for your help.

Doug