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 13 2049
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" wrote: 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
Charlie,
I do not want to update the Database!
I want to update 4 of the 6 DataTables in the DataSet!
Doug
"Charlie" <cfarrier at charlesfarriersoftware.com> wrote in message
news:34**********************************@microsof t.com... 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" wrote:
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
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" wrote: Charlie, I do not want to update the Database!
I want to update 4 of the 6 DataTables in the DataSet!
Doug
"Charlie" <cfarrier at charlesfarriersoftware.com> wrote in message news:34**********************************@microsof t.com... 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" wrote:
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
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
"Charlie" <cfarrier at charlesfarriersoftware.com> wrote in message
news:23**********************************@microsof t.com... 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" wrote:
Charlie, I do not want to update the Database!
I want to update 4 of the 6 DataTables in the DataSet!
Doug
"Charlie" <cfarrier at charlesfarriersoftware.com> wrote in message news:34**********************************@microsof t.com... 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" wrote:
> 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 > > >
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?
"Doug Bell" wrote: 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
"Charlie" <cfarrier at charlesfarriersoftware.com> wrote in message news:23**********************************@microsof t.com... 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" wrote:
Charlie, I do not want to update the Database!
I want to update 4 of the 6 DataTables in the DataSet!
Doug
"Charlie" <cfarrier at charlesfarriersoftware.com> wrote in message news:34**********************************@microsof t.com... > 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" wrote: > > > 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 > > > > > >
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.
"Charlie" wrote: 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?
"Doug Bell" wrote:
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
"Charlie" <cfarrier at charlesfarriersoftware.com> wrote in message news:23**********************************@microsof t.com... 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" wrote:
> Charlie, > I do not want to update the Database! > > I want to update 4 of the 6 DataTables in the DataSet! > > Doug > > "Charlie" <cfarrier at charlesfarriersoftware.com> wrote in message > news:34**********************************@microsof t.com... > > 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" wrote: > > > > > 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 > > > > > > > > > > > >
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" <dug@bigpond> 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
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
"Cor Ligthert" <no************@planet.nl> wrote in message
news:ep**************@TK2MSFTNGP09.phx.gbl... 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" <dug@bigpond>
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
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
"Cor Ligthert" <no************@planet.nl> wrote in message
news:ep**************@TK2MSFTNGP09.phx.gbl... 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" <dug@bigpond>
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
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
"Charlie" <cfarrier at charlesfarriersoftware.com> wrote in message
news:70**********************************@microsof t.com... 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?
"Doug Bell" wrote:
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
"Charlie" <cfarrier at charlesfarriersoftware.com> wrote in message news:23**********************************@microsof t.com... 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" wrote:
> Charlie, > I do not want to update the Database! > > I want to update 4 of the 6 DataTables in the DataSet! > > Doug > > "Charlie" <cfarrier at charlesfarriersoftware.com> wrote in message > news:34**********************************@microsof t.com... > > 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" wrote: > > > > > 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 > > > > > > > > > > > >
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
"Charlie" <cfarrier at charlesfarriersoftware.com> wrote in message
news:70**********************************@microsof t.com... 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?
"Doug Bell" wrote:
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
"Charlie" <cfarrier at charlesfarriersoftware.com> wrote in message news:23**********************************@microsof t.com... 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" wrote:
> Charlie, > I do not want to update the Database! > > I want to update 4 of the 6 DataTables in the DataSet! > > Doug > > "Charlie" <cfarrier at charlesfarriersoftware.com> wrote in message > news:34**********************************@microsof t.com... > > 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" wrote: > > > > > 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 > > > > > > > > > > > >
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
"Charlie" <cfarrier at charlesfarriersoftware.com> wrote in message
news:07**********************************@microsof t.com... 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.
"Charlie" wrote:
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?
"Doug Bell" wrote:
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
"Charlie" <cfarrier at charlesfarriersoftware.com> wrote in message news:23**********************************@microsof t.com... > 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" wrote: > > > Charlie, > > I do not want to update the Database! > > > > I want to update 4 of the 6 DataTables in the DataSet! > > > > Doug > > > > "Charlie" <cfarrier at charlesfarriersoftware.com> wrote in
message > > news:34**********************************@microsof t.com... > > > 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" wrote: > > > > > > > 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 > > > > > > > > > > > > > > > > > >
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
"Charlie" <cfarrier at charlesfarriersoftware.com> wrote in message
news:07**********************************@microsof t.com... 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.
"Charlie" wrote:
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?
"Doug Bell" wrote:
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
"Charlie" <cfarrier at charlesfarriersoftware.com> wrote in message news:23**********************************@microsof t.com... > 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" wrote: > > > Charlie, > > I do not want to update the Database! > > > > I want to update 4 of the 6 DataTables in the DataSet! > > > > Doug > > > > "Charlie" <cfarrier at charlesfarriersoftware.com> wrote in
message > > news:34**********************************@microsof t.com... > > > 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" wrote: > > > > > > > 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 > > > > > > > > > > > > > > > > > > This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics
by: Ms.net |
last post by:
I am having problem updating data with dataadapter....in some of my tables.
I realize that, Dataadapter doesn't SOMETIME fire event rowupdating even if
there are new rows.
Because of that,rows...
|
by: Ms.net |
last post by:
am having problem updating data with dataadapter....in some of my tables.
I realize that, Dataadapter doesn't SOMETIME fire event rowupdating even if
there are new rows.
Because of that,rows...
|
by: Ruslan Shlain |
last post by:
Is there a way to tell DataAdapter to continue updating if it encounters
error. So, in other words once it starts to update records in the database
and it fails on one of them, then it would...
|
by: RSH |
last post by:
Hi,
I have a situation in where i have two instances of SQL server, the first is
our Production Environment, the second is our Development environment. Both
servers contain the same databases...
|
by: susan.f.barrett |
last post by:
Hi,
Despite me being able to type the following in to SQL Server and it
updating 1 row:
> updatestockcategory 1093, 839
In my code, it is not updating any rows.
dataSet = new DataSet();
|
by: Rich |
last post by:
Dim da As New SqlDataAdapter("Select * from tbl1", conn)
dim tblx As New DataTable
da.Fill(tblx) '--works OK up to this point
da.UpdateCommand = New SqlCommand
da.UpdateCommand.Connection =...
|
by: George |
last post by:
I have set DataAdapter.AcceptChangesDuringUpdate = true;
However, I find that I still need to call AcceptChanges on the associated
DataTable,
DataTable.AcceptChanges();
Has anyone...
|
by: mfleet1973 |
last post by:
Hi Gang.
I'm using VB.Net 2003. In the following code I'm updating the
dataadapter with a new record added to the table:
OleDbDataAdapter1.Update(DataSet11.GetChanges, "Table")
However,...
|
by: Rich |
last post by:
What is the diffeence bewtween a dataAdapter.InsertCommand and
dataAdapter.SelectCommand (and dataAdapter.UpdateCommand for that matter)?
Dim da As SqlDataAdapter
conn.Open
da.SelectCommand =...
|
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...
|
by: ryjfgjl |
last post by:
ExcelToDatabase: batch import excel into database automatically...
|
by: isladogs |
last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM).
In this month's session, we are pleased to welcome back...
|
by: isladogs |
last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM).
In this month's session, we are pleased to welcome back...
|
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...
|
by: CloudSolutions |
last post by:
Introduction:
For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
|
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....
|
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
|
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...
| |