How best to keep data set and db in synch

[Rob]

I have a form where the user may see a matrix of data in a grid (populated
via a dataset). If the user wishes to add a new row of data, they click a
button and a new form opens where the user may enter data into each
textbox, then click the add button when finished. At this point a stored
procedure is run which inserts the new row into the database (not the
dataset), given that the row is not a duplicate and the contents of each
text box are valid.

Now that the data resides in the database (via the sp), how best to update
the dataset ?
- Clear and fill OR
- add the new row to the dataset via code

How do most people accomplish this in vb.net ?

Is this a pretty common approach (use stored procs) instead of the ".update"
method ?

Thanks !

[Dennis]

RE: How best to keep data set and db in synch

I add the record to the Dataset first then do an Update using the
dataadaptor. If there is an error, I can then cancel the change the dataset.
This insures they are in sync.
--
Dennis in Houston


"Rob" wrote:
[color=blue]
> I have a form where the user may see a matrix of data in a grid (populated
> via a dataset). If the user wishes to add a new row of data, they click a
> button and a new form opens where the user may enter data into each
> textbox, then click the add button when finished. At this point a stored
> procedure is run which inserts the new row into the database (not the
> dataset), given that the row is not a duplicate and the contents of each
> text box are valid.
>
> Now that the data resides in the database (via the sp), how best to update
> the dataset ?
> - Clear and fill OR
> - add the new row to the dataset via code
>
> How do most people accomplish this in vb.net ?
>
> Is this a pretty common approach (use stored procs) instead of the ".update"
> method ?
>
> Thanks !
>
>
>[/color]

[Rob]

Re: How best to keep data set and db in synch

Dennis,

Thanks for responding... I was under the impression that the best way to
update Sql server was to use stored procedures... almost exclusively if
possible... is that the wrong way to think ?

Rob


"Dennis" <Dennis@discuss ions.microsoft. com> wrote in message
news:9E4BB300-54AF-4BB3-9A25-DFE3B41AC121@mi crosoft.com...[color=blue]
>I add the record to the Dataset first then do an Update using the
> dataadaptor. If there is an error, I can then cancel the change the
> dataset.
> This insures they are in sync.
> --
> Dennis in Houston
>
>
> "Rob" wrote:
>[color=green]
>> I have a form where the user may see a matrix of data in a grid
>> (populated
>> via a dataset). If the user wishes to add a new row of data, they click
>> a
>> button and a new form opens where the user may enter data into each
>> textbox, then click the add button when finished. At this point a
>> stored
>> procedure is run which inserts the new row into the database (not the
>> dataset), given that the row is not a duplicate and the contents of each
>> text box are valid.
>>
>> Now that the data resides in the database (via the sp), how best to
>> update
>> the dataset ?
>> - Clear and fill OR
>> - add the new row to the dataset via code
>>
>> How do most people accomplish this in vb.net ?
>>
>> Is this a pretty common approach (use stored procs) instead of the
>> ".update"
>> method ?
>>
>> Thanks !
>>
>>
>>[/color][/color]

[Dennis]

Re: How best to keep data set and db in synch

I certainly am not a database expert but I would think that if you are just
adding a record, then adding it to the DataSet then using the DataAdaptor's
Update method would be the easiest and be sure to keep them in sync.

There is probably a way to add a row to the database then get that latest
row and add it to the dataset but I don't know it.
--
Dennis in Houston


"Rob" wrote:
[color=blue]
> Dennis,
>
> Thanks for responding... I was under the impression that the best way to
> update Sql server was to use stored procedures... almost exclusively if
> possible... is that the wrong way to think ?
>
> Rob
>
>
> "Dennis" <Dennis@discuss ions.microsoft. com> wrote in message
> news:9E4BB300-54AF-4BB3-9A25-DFE3B41AC121@mi crosoft.com...[color=green]
> >I add the record to the Dataset first then do an Update using the
> > dataadaptor. If there is an error, I can then cancel the change the
> > dataset.
> > This insures they are in sync.
> > --
> > Dennis in Houston
> >
> >
> > "Rob" wrote:
> >[color=darkred]
> >> I have a form where the user may see a matrix of data in a grid
> >> (populated
> >> via a dataset). If the user wishes to add a new row of data, they click
> >> a
> >> button and a new form opens where the user may enter data into each
> >> textbox, then click the add button when finished. At this point a
> >> stored
> >> procedure is run which inserts the new row into the database (not the
> >> dataset), given that the row is not a duplicate and the contents of each
> >> text box are valid.
> >>
> >> Now that the data resides in the database (via the sp), how best to
> >> update
> >> the dataset ?
> >> - Clear and fill OR
> >> - add the new row to the dataset via code
> >>
> >> How do most people accomplish this in vb.net ?
> >>
> >> Is this a pretty common approach (use stored procs) instead of the
> >> ".update"
> >> method ?
> >>
> >> Thanks !
> >>
> >>
> >>[/color][/color]
>
>
>[/color]

[Rob]

Re: How best to keep data set and db in synch

Here is how I am doing it in one case...

I have a matrix of data that is visible to the user (a dataset load)... if
they want to add data to this matrix they are taken to an "Add Data" form...
as they enter each row, it is validated and passed directly to the db via
stored proc, when they are done entering new rows (which now reside in the
db), on reactivation of the previous form (the form contining the matrix), I
clear the dataset and refresh it directly with data from the db.... that
sound ok ? Any drawbacks ?



"Dennis" <Dennis@discuss ions.microsoft. com> wrote in message
news:6B6664C5-7BAE-4FC0-873C-6FB4900AB192@mi crosoft.com...[color=blue]
>I certainly am not a database expert but I would think that if you are just
> adding a record, then adding it to the DataSet then using the
> DataAdaptor's
> Update method would be the easiest and be sure to keep them in sync.
>
> There is probably a way to add a row to the database then get that latest
> row and add it to the dataset but I don't know it.
> --
> Dennis in Houston
>
>
> "Rob" wrote:
>[color=green]
>> Dennis,
>>
>> Thanks for responding... I was under the impression that the best way to
>> update Sql server was to use stored procedures... almost exclusively if
>> possible... is that the wrong way to think ?
>>
>> Rob
>>
>>
>> "Dennis" <Dennis@discuss ions.microsoft. com> wrote in message
>> news:9E4BB300-54AF-4BB3-9A25-DFE3B41AC121@mi crosoft.com...[color=darkred]
>> >I add the record to the Dataset first then do an Update using the
>> > dataadaptor. If there is an error, I can then cancel the change the
>> > dataset.
>> > This insures they are in sync.
>> > --
>> > Dennis in Houston
>> >
>> >
>> > "Rob" wrote:
>> >
>> >> I have a form where the user may see a matrix of data in a grid
>> >> (populated
>> >> via a dataset). If the user wishes to add a new row of data, they
>> >> click
>> >> a
>> >> button and a new form opens where the user may enter data into each
>> >> textbox, then click the add button when finished. At this point a
>> >> stored
>> >> procedure is run which inserts the new row into the database (not the
>> >> dataset), given that the row is not a duplicate and the contents of
>> >> each
>> >> text box are valid.
>> >>
>> >> Now that the data resides in the database (via the sp), how best to
>> >> update
>> >> the dataset ?
>> >> - Clear and fill OR
>> >> - add the new row to the dataset via code
>> >>
>> >> How do most people accomplish this in vb.net ?
>> >>
>> >> Is this a pretty common approach (use stored procs) instead of the
>> >> ".update"
>> >> method ?
>> >>
>> >> Thanks !
>> >>
>> >>
>> >>[/color]
>>
>>
>>[/color][/color]

[Cor Ligthert [MVP]]

Re: How best to keep data set and db in synch

Rob,

For some seems to be magic around a stored procedure, beside some effects it
is nothing else than a text command. It is got at the server instead given
as a text and compiled when it is needed.

A dataset and a database is (almost) never in synch. That is exactly what
optimistic concurrency and disconnected processing means. (Optimistic
concurrency means that the change that another user updates the information
meanwhile the data is in the dataset is low. However it has to be checked if
it is not protected in other ways).

For disctonnect you can think that you have a dataset in a PDA, that you
update once a week. That is (if there is not only one user) never in Synch
with the server.

For updating a dataset are a lot of methods. See here a new sample (for
VS2005) on our website, it is a little bit else than the regular ways.

http://www.vb-tips.com/default.aspx?ID=8c3dc2d7-1232-4dd1-817e-22eaaebb2723

I hope this helps,

Cor