Creating table from dataset

[John]

Hi

I have a dataset which has received its data from a remote web service. How
can I now save the dataset into a local access table?

Thanks

Regards

 

[William Ryan eMVP]

Hi John:

Is the scenario basically that you are pulling data from a Web service and
now you need to get that data into an Access DB Table? I'm probably just
restating the obvious but I just want to confirm this. If so, comments
below:

Hi

I have a dataset which has received its data from a remote web service. How
can I now save the dataset into a local access table?

In the WS, make sure that you set the DataAdapter's .AcceptChangesDuringFill
property to false. http://www.knowdotnet.com/articles/datasetmerge.html
What this will do is (as the name may suggest), suppress the Adapter from
calling AcceptChanges on each row as it's added to the datatable. That will
have the effect of having the Rowstate for each row set to Added instead of
Unchanged. So, if each row is marked as Added, then all you need is a valid
Update command for a data adapter configured for the Access DB table, and
you can just call Update on the dataset.

So, in the WS, remeber to set the AcceptChangesDuringFill to false

WebService As DataSet

//Set it to False here.
//Call Fill on the Dataset
//Return Data Set

now, in your client app. Make sure you create your web reference correctly
and then invoke the ws

DataSet ds = myWebService.GetDataSetMethod();
//At this point, your dataSet will HasChanges=True... Test it just to be
sure
Debug.Assert(ds.HasChanges, "DataSet doesn't have Changes");
//The reason for this check during debug phase is just to ensure that it has
changes, otherwise calling update on it will do nothing - This isn't
necessary for production, you just want to make sure that if your update
doesn't work that it's not because the dataset didn't have any changes

//Now assuming you have an OleDbDataAdapter and its update command is
configured properly....

myOleDbDataAdapter.Update(ds.Tables[0]);
//You can use any of the Overloads for Update, just make sure you specify
the right table. You may for instance have multiple tables pulled back from
the WS, in that case, you'll need multiple DataAdapters and you'll need to
fill the parent tables first b/c of the Key constraints if they are
applicable.

Let me know if you have any questions.

HTH,

Bill

Thanks

Regards

--
W.G. Ryan MVP Windows - Embedded

http://forums.devbuzz.com
http://www.knowdotnet.com/dataaccess.html
http://www.msmvps.com/williamryan/

 

[John]

Thanks for that. I will try this.

What happens if some of the retrieved rows are already in the destination
table? Would Update at the client end know which rows to update and which
rows to insert/add?

Thanks

Regards

Hi John:

Is the scenario basically that you are pulling data from a Web service and
now you need to get that data into an Access DB Table? I'm probably just
restating the obvious but I just want to confirm this. If so, comments
below:

Hi

I have a dataset which has received its data from a remote web service. How
can I now save the dataset into a local access table?

In the WS, make sure that you set the DataAdapter's ..AcceptChangesDuringFill
property to false. http://www.knowdotnet.com/articles/datasetmerge.html
What this will do is (as the name may suggest), suppress the Adapter from
calling AcceptChanges on each row as it's added to the datatable. That will
have the effect of having the Rowstate for each row set to Added instead of
Unchanged. So, if each row is marked as Added, then all you need is a valid
Update command for a data adapter configured for the Access DB table, and
you can just call Update on the dataset.

So, in the WS, remeber to set the AcceptChangesDuringFill to false

WebService As DataSet

//Set it to False here.
//Call Fill on the Dataset
//Return Data Set

now, in your client app. Make sure you create your web reference correctly
and then invoke the ws

DataSet ds = myWebService.GetDataSetMethod();
//At this point, your dataSet will HasChanges=True... Test it just to be
sure
Debug.Assert(ds.HasChanges, "DataSet doesn't have Changes");
//The reason for this check during debug phase is just to ensure that it has
changes, otherwise calling update on it will do nothing - This isn't
necessary for production, you just want to make sure that if your update
doesn't work that it's not because the dataset didn't have any changes

//Now assuming you have an OleDbDataAdapter and its update command is
configured properly....

myOleDbDataAdapter.Update(ds.Tables[0]);
//You can use any of the Overloads for Update, just make sure you specify
the right table. You may for instance have multiple tables pulled back from
the WS, in that case, you'll need multiple DataAdapters and you'll need to
fill the parent tables first b/c of the Key constraints if they are
applicable.

Let me know if you have any questions.

HTH,

Bill

Thanks

Regards

--
W.G. Ryan MVP Windows - Embedded

http://forums.devbuzz.com
http://www.knowdotnet.com/dataaccess.html
http://www.msmvps.com/williamryan/

 

[William Ryan eMVP]

It depends but it will probably throw a concurrency exception b/c it will be
calling Insert on a row that already exists. One way to solve this is to
set the ContinueUpdateOnError property to True on the adapter that's being
used to update the destination datatable. You may also want to clear the
destination table first... or if you need those rows, fill a Dataset with
those rows and then merge that dataset with the one returned from the web
service. Then call update.

--
W.G. Ryan MVP Windows - Embedded

http://forums.devbuzz.com
http://www.knowdotnet.com/dataaccess.html
http://www.msmvps.com/williamryan/

Thanks for that. I will try this.

What happens if some of the retrieved rows are already in the destination
table? Would Update at the client end know which rows to update and which
rows to insert/add?

Thanks

Regards

Hi John:

Is the scenario basically that you are pulling data from a Web service and
now you need to get that data into an Access DB Table? I'm probably just
restating the obvious but I just want to confirm this. If so, comments
below:

Hi

I have a dataset which has received its data from a remote web

service.
How

can I now save the dataset into a local access table?

In the WS, make sure that you set the DataAdapter's .AcceptChangesDuringFill
property to false. http://www.knowdotnet.com/articles/datasetmerge.html
What this will do is (as the name may suggest), suppress the Adapter from
calling AcceptChanges on each row as it's added to the datatable. That will
have the effect of having the Rowstate for each row set to Added instead of
Unchanged. So, if each row is marked as Added, then all you need is a valid
Update command for a data adapter configured for the Access DB table, and
you can just call Update on the dataset.

So, in the WS, remeber to set the AcceptChangesDuringFill to false

WebService As DataSet

//Set it to False here.
//Call Fill on the Dataset
//Return Data Set

now, in your client app. Make sure you create your web reference correctly
and then invoke the ws

DataSet ds = myWebService.GetDataSetMethod();
//At this point, your dataSet will HasChanges=True... Test it just to be
sure
Debug.Assert(ds.HasChanges, "DataSet doesn't have Changes");
//The reason for this check during debug phase is just to ensure that it has
changes, otherwise calling update on it will do nothing - This isn't
necessary for production, you just want to make sure that if your update
doesn't work that it's not because the dataset didn't have any changes

//Now assuming you have an OleDbDataAdapter and its update command is
configured properly....

myOleDbDataAdapter.Update(ds.Tables[0]);
//You can use any of the Overloads for Update, just make sure you specify
the right table. You may for instance have multiple tables pulled back from
the WS, in that case, you'll need multiple DataAdapters and you'll need to
fill the parent tables first b/c of the Key constraints if they are
applicable.

Let me know if you have any questions.

HTH,

Bill

Thanks

Regards

--
W.G. Ryan MVP Windows - Embedded

http://forums.devbuzz.com
http://www.knowdotnet.com/dataaccess.html
http://www.msmvps.com/williamryan/

 

[John]

Hi Bill

I have heard a lot about merge but have never used it. Is there an example I
can follow? Would merge update the fields in existing records and insert new
records as needed?

Thanks

Regards

It depends but it will probably throw a concurrency exception b/c it will be
calling Insert on a row that already exists. One way to solve this is to
set the ContinueUpdateOnError property to True on the adapter that's being
used to update the destination datatable. You may also want to clear the
destination table first... or if you need those rows, fill a Dataset with
those rows and then merge that dataset with the one returned from the web
service. Then call update.

--
W.G. Ryan MVP Windows - Embedded

http://forums.devbuzz.com
http://www.knowdotnet.com/dataaccess.html
http://www.msmvps.com/williamryan/

Thanks for that. I will try this.

What happens if some of the retrieved rows are already in the destination
table? Would Update at the client end know which rows to update and which
rows to insert/add?

Thanks

Regards

Hi John:

Is the scenario basically that you are pulling data from a Web service and
now you need to get that data into an Access DB Table? I'm probably just
restating the obvious but I just want to confirm this. If so, comments
below:


Hi

I have a dataset which has received its data from a remote web service.
How
can I now save the dataset into a local access table?

In the WS, make sure that you set the DataAdapter's .AcceptChangesDuringFill
property to false. http://www.knowdotnet.com/articles/datasetmerge.html
What this will do is (as the name may suggest), suppress the Adapter from
calling AcceptChanges on each row as it's added to the datatable.

That
will

have the effect of having the Rowstate for each row set to Added

instead
of

Unchanged. So, if each row is marked as Added, then all you need is a valid
Update command for a data adapter configured for the Access DB table, and
you can just call Update on the dataset.

So, in the WS, remeber to set the AcceptChangesDuringFill to false

WebService As DataSet

//Set it to False here.
//Call Fill on the Dataset
//Return Data Set

now, in your client app. Make sure you create your web reference correctly
and then invoke the ws

DataSet ds = myWebService.GetDataSetMethod();
//At this point, your dataSet will HasChanges=True... Test it just to be
sure
Debug.Assert(ds.HasChanges, "DataSet doesn't have Changes");
//The reason for this check during debug phase is just to ensure that

it
has

changes, otherwise calling update on it will do nothing - This isn't
necessary for production, you just want to make sure that if your update
doesn't work that it's not because the dataset didn't have any changes

//Now assuming you have an OleDbDataAdapter and its update command is
configured properly....

myOleDbDataAdapter.Update(ds.Tables[0]);
//You can use any of the Overloads for Update, just make sure you specify
the right table. You may for instance have multiple tables pulled

back
from

the WS, in that case, you'll need multiple DataAdapters and you'll

need

to

 

[William Ryan eMVP]

Hi Bill

I have heard a lot about merge but have never used it. Is there an example I
can follow?

http://msdn.microsoft.com/library/d...tml/frlrfsystemdatadatasetclassmergetopic.asp
http://authors.aspalliance.com/aspxtreme/sys/Data/datasetclassmerge.aspx
Would merge update the fields in existing records and insert new

records as needed?

yes, but it depends on how you set everything up and what you merge into
what. Check out the MSDN link above and the other example, I think it will
help you out.

Thanks

Regards

It depends but it will probably throw a concurrency exception b/c it

will

be
calling Insert on a row that already exists. One way to solve this is to
set the ContinueUpdateOnError property to True on the adapter that's being
used to update the destination datatable. You may also want to clear the
destination table first... or if you need those rows, fill a Dataset with
those rows and then merge that dataset with the one returned from the web
service. Then call update.

--
W.G. Ryan MVP Windows - Embedded

http://forums.devbuzz.com
http://www.knowdotnet.com/dataaccess.html
http://www.msmvps.com/williamryan/
service
and table,
and

to

be

that

it

has
changes, otherwise calling update on it will do nothing - This isn't
necessary for production, you just want to make sure that if your update
doesn't work that it's not because the dataset didn't have any changes

//Now assuming you have an OleDbDataAdapter and its update command is
configured properly....

myOleDbDataAdapter.Update(ds.Tables[0]);
//You can use any of the Overloads for Update, just make sure you specify
the right table. You may for instance have multiple tables pulled back
from
the WS, in that case, you'll need multiple DataAdapters and you'll

need

to
fill the parent tables first b/c of the Key constraints if they are
applicable.

Let me know if you have any questions.

HTH,

Bill

Thanks

Regards



--
W.G. Ryan MVP Windows - Embedded

http://forums.devbuzz.com
http://www.knowdotnet.com/dataaccess.html
http://www.msmvps.com/williamryan/

--
W.G. Ryan MVP Windows - Embedded

http://forums.devbuzz.com
http://www.knowdotnet.com/dataaccess.html
http://www.msmvps.com/williamryan/

 

[John]

The MSDN examples deal with the table in the same dataset and can check for
errors in ds after inserting rows into the associated table but before
merge. In my case the ds/tables are separate and I can presumably only check
for errors after the merge? Which could be different from the way depicted
in the examples???

Regards

Hi Bill

I have heard a lot about merge but have never used it. Is there an

example

I
can follow?

http://msdn.microsoft.com/library/d...tml/frlrfsystemdatadatasetclassmergetopic.asp
http://authors.aspalliance.com/aspxtreme/sys/Data/datasetclassmerge.aspx
Would merge update the fields in existing records and insert new

records as needed?

yes, but it depends on how you set everything up and what you merge into
what. Check out the MSDN link above and the other example, I think it will
help you out.

Thanks

Regards

will

is

a

valid
Update command for a data adapter configured for the Access DB table,
and
you can just call Update on the dataset.

So, in the WS, remeber to set the AcceptChangesDuringFill to false

WebService As DataSet

//Set it to False here.
//Call Fill on the Dataset
//Return Data Set

now, in your client app. Make sure you create your web reference
correctly
and then invoke the ws

DataSet ds = myWebService.GetDataSetMethod();
//At this point, your dataSet will HasChanges=True... Test it just

to

be
sure
Debug.Assert(ds.HasChanges, "DataSet doesn't have Changes");
//The reason for this check during debug phase is just to ensure

that

it
has
changes, otherwise calling update on it will do nothing - This isn't
necessary for production, you just want to make sure that if your update
doesn't work that it's not because the dataset didn't have any changes

//Now assuming you have an OleDbDataAdapter and its update command is
configured properly....

myOleDbDataAdapter.Update(ds.Tables[0]);
//You can use any of the Overloads for Update, just make sure you
specify
the right table. You may for instance have multiple tables pulled back
from
the WS, in that case, you'll need multiple DataAdapters and you'll need
to
fill the parent tables first b/c of the Key constraints if they are
applicable.

Let me know if you have any questions.

HTH,

Bill

Thanks

Regards



--
W.G. Ryan MVP Windows - Embedded

http://forums.devbuzz.com
http://www.knowdotnet.com/dataaccess.html
http://www.msmvps.com/williamryan/

--
W.G. Ryan MVP Windows - Embedded

http://forums.devbuzz.com
http://www.knowdotnet.com/dataaccess.html
http://www.msmvps.com/williamryan/

 

[William Ryan eMVP]

John:

I'm not sure which errors it's referring to. It sounds like it's the
HasErrors property but if you are getting the data and trying to transfer it
right after a Fill, I'm not sure how this relates.

Let's go back to the original scenario... you have a web service and that
data needs to get into an Access db. So you use your WS, set the
AcceptChangesDuringFill to false so that all the RowStates are Added. Now,
you have a DataSet witha bunch of rows that all think they should be added
to the access db. You have a problem wherein you are worried about
collisions b/c some of those values already may exist in teh Access db. I
suggested pulling the data from the access table too and merging the data as
one possible approach. Before the merge there would be no errors in either
row but let's take a different track.. If you set the ContinueUpdateOnError
property to true on the adapter that's doign the updates, everything that's
not already in ths access DB will be added. At each pass, when a row tries
to update, you can trap the OnRowUpdating event, and one of the event args
is Errors. If you get any b/c the row exists, you can stick that rows
values somewhere (like in a collection) and then when the update finishes,
just go through your collection and call Update instead of insert using the
values of each row taht failed which will be stored in your collection. you
can grab the Row through the EventArgs
http://msdn.microsoft.com/library/d...mdatacommonrowupdatingeventargsclasstopic.asp
so you have everything you could need.
Regarding the Errors you mentioned, I wasn't sure if these are the ones
you're referring to or not, or if you mean the HasErrors property fo the
table. Either way this should address your problem.

HTH,

Bill

--
W.G. Ryan MVP Windows - Embedded

http://forums.devbuzz.com
http://www.knowdotnet.com/dataaccess.html
http://www.msmvps.com/williamryan/

The MSDN examples deal with the table in the same dataset and can check for
errors in ds after inserting rows into the associated table but before
merge. In my case the ds/tables are separate and I can presumably only check
for errors after the merge? Which could be different from the way depicted
in the examples???

Regards

example

http://msdn.microsoft.com/library/d...tml/frlrfsystemdatadatasetclassmergetopic.asp

http://authors.aspalliance.com/aspxtreme/sys/Data/datasetclassmerge.aspx
Would merge update the fields in existing records and insert new
yes, but it depends on how you set everything up and what you merge into
what. Check out the MSDN link above and the other example, I think it will
help you out. is
to clear
the the
web

need

is

a
valid
Update command for a data adapter configured for the Access DB table,
and
you can just call Update on the dataset.

So, in the WS, remeber to set the AcceptChangesDuringFill to false

WebService As DataSet

//Set it to False here.
//Call Fill on the Dataset
//Return Data Set

now, in your client app. Make sure you create your web reference
correctly
and then invoke the ws

DataSet ds = myWebService.GetDataSetMethod();
//At this point, your dataSet will HasChanges=True... Test it

just
to

be
sure
Debug.Assert(ds.HasChanges, "DataSet doesn't have Changes");
//The reason for this check during debug phase is just to ensure that
it
has
changes, otherwise calling update on it will do nothing - This isn't
necessary for production, you just want to make sure that if your
update
doesn't work that it's not because the dataset didn't have any changes

//Now assuming you have an OleDbDataAdapter and its update

command
is

configured properly....

myOleDbDataAdapter.Update(ds.Tables[0]);
//You can use any of the Overloads for Update, just make sure you
specify
the right table. You may for instance have multiple tables pulled
back
from
the WS, in that case, you'll need multiple DataAdapters and you'll
need
to
fill the parent tables first b/c of the Key constraints if they are
applicable.

Let me know if you have any questions.

HTH,

Bill

Thanks

Regards



--
W.G. Ryan MVP Windows - Embedded

http://forums.devbuzz.com
http://www.knowdotnet.com/dataaccess.html
http://www.msmvps.com/williamryan/

--
W.G. Ryan MVP Windows - Embedded

http://forums.devbuzz.com
http://www.knowdotnet.com/dataaccess.html
http://www.msmvps.com/williamryan/

 

[John]

Thanks that clarifies it. The only tricky bit would be saving records in
some sort of collection and then use it to update records, but I will see
what I can do.

Thanks

Regards

John:

I'm not sure which errors it's referring to. It sounds like it's the
HasErrors property but if you are getting the data and trying to transfer it
right after a Fill, I'm not sure how this relates.

Let's go back to the original scenario... you have a web service and that
data needs to get into an Access db. So you use your WS, set the
AcceptChangesDuringFill to false so that all the RowStates are Added. Now,
you have a DataSet witha bunch of rows that all think they should be added
to the access db. You have a problem wherein you are worried about
collisions b/c some of those values already may exist in teh Access db. I
suggested pulling the data from the access table too and merging the data as
one possible approach. Before the merge there would be no errors in either
row but let's take a different track.. If you set the ContinueUpdateOnError
property to true on the adapter that's doign the updates, everything that's
not already in ths access DB will be added. At each pass, when a row tries
to update, you can trap the OnRowUpdating event, and one of the event args
is Errors. If you get any b/c the row exists, you can stick that rows
values somewhere (like in a collection) and then when the update finishes,
just go through your collection and call Update instead of insert using the
values of each row taht failed which will be stored in your collection. you
can grab the Row through the EventArgs
http://msdn.microsoft.com/library/d...mdatacommonrowupdatingeventargsclasstopic.asp
so you have everything you could need.
Regarding the Errors you mentioned, I wasn't sure if these are the ones
you're referring to or not, or if you mean the HasErrors property fo the
table. Either way this should address your problem.

HTH,

Bill

--
W.G. Ryan MVP Windows - Embedded

http://forums.devbuzz.com
http://www.knowdotnet.com/dataaccess.html
http://www.msmvps.com/williamryan/

The MSDN examples deal with the table in the same dataset and can check for
errors in ds after inserting rows into the associated table but before
merge. In my case the ds/tables are separate and I can presumably only check
for errors after the merge? Which could be different from the way depicted
in the examples???

Regards

http://msdn.microsoft.com/library/d...tml/frlrfsystemdatadatasetclassmergetopic.asp this

is

to
set the ContinueUpdateOnError property to True on the adapter that's
being
used to update the destination datatable. You may also want to clear
the
destination table first... or if you need those rows, fill a Dataset
with
those rows and then merge that dataset with the one returned from the
web
service. Then call update.

--
W.G. Ryan MVP Windows - Embedded

http://forums.devbuzz.com
http://www.knowdotnet.com/dataaccess.html
http://www.msmvps.com/williamryan/
Thanks for that. I will try this.

What happens if some of the retrieved rows are already in the
destination
table? Would Update at the client end know which rows to update and
which
rows to insert/add?

Thanks

Regards


Hi John:

Is the scenario basically that you are pulling data from a Web
service
and
now you need to get that data into an Access DB Table? I'm probably
just
restating the obvious but I just want to confirm this. If so,
comments
below:


Hi

I have a dataset which has received its data from a remote web
service.
How
can I now save the dataset into a local access table?

In the WS, make sure that you set the DataAdapter's
.AcceptChangesDuringFill
property to false.
http://www.knowdotnet.com/articles/datasetmerge.html
What this will do is (as the name may suggest), suppress the Adapter
from
calling AcceptChanges on each row as it's added to the datatable.
That
will
have the effect of having the Rowstate for each row set to Added
instead
of
Unchanged. So, if each row is marked as Added, then all you

need

is
a
valid
Update command for a data adapter configured for the Access DB
table,
and
you can just call Update on the dataset.

So, in the WS, remeber to set the AcceptChangesDuringFill to false

WebService As DataSet

//Set it to False here.
//Call Fill on the Dataset
//Return Data Set

now, in your client app. Make sure you create your web reference
correctly
and then invoke the ws

DataSet ds = myWebService.GetDataSetMethod();
//At this point, your dataSet will HasChanges=True... Test it just
to
be
sure
Debug.Assert(ds.HasChanges, "DataSet doesn't have Changes");
//The reason for this check during debug phase is just to ensure
that
it
has
changes, otherwise calling update on it will do nothing - This
isn't
necessary for production, you just want to make sure that if your
update
doesn't work that it's not because the dataset didn't have any
changes

//Now assuming you have an OleDbDataAdapter and its update command
is
configured properly....

myOleDbDataAdapter.Update(ds.Tables[0]);
//You can use any of the Overloads for Update, just make sure you
specify
the right table. You may for instance have multiple tables pulled
back
from
the WS, in that case, you'll need multiple DataAdapters and you'll
need
to
fill the parent tables first b/c of the Key constraints if

they
are

applicable.

Let me know if you have any questions.

HTH,

Bill

Thanks

Regards



--
W.G. Ryan MVP Windows - Embedded

http://forums.devbuzz.com
http://www.knowdotnet.com/dataaccess.html
http://www.msmvps.com/williamryan/










--
W.G. Ryan MVP Windows - Embedded

http://forums.devbuzz.com
http://www.knowdotnet.com/dataaccess.html
http://www.msmvps.com/williamryan/