Sync between DataGrid and DB

[Pierluigi Terzoli]

Hi everybody,
I need help for this problem:
I'm using a DataGrid to Insert/Modify/Cancel data from a single table.
At the moment, every cell modification of a pre-existing row is correctly
update on the DB.
I have some doubts about inserting a new row.
The table has a Id field that is a primary key defined ad Identity
properties on SQLServer engine.
This column isn't visible on the grid and a properties DefaultValue=0 is
defined.
How can I synchronise the DataTable and the DB.
If I understood right I must sync after a row inserting on DataTable
otherwise I can insert another row on DataTable (duplicate Key on Id Column,
obviously).
I tried to catch a RowChanged event forcing a Adapter.Update( ) but I didn't
meet my goal.
Any suggestion ??
Thanks in advance, Pierluigi.

[Nicholas Paldino [.NET/C# MVP]]

Pierluigi,

In this case, you might have to refresh the whole table, just so that
you can get your id's right. Actually, you would have to. The only way to
prevent this from happening is to handle the updates yourself, instead of
through a data adapter, so that you can actually try and get the last id
inserted (in SQL Server, you can select @@identity to get the last identity
column inserted).

Hope this helps.
--
- Nicholas Paldino [.NET/C# MVP]
- mv*@spam.guard. caspershouse.co m

"Pierluigi Terzoli" <pi************ ***@hotmail.com > wrote in message
news:O5******** ********@TK2MSF TNGP09.phx.gbl. ..

Hi everybody,
I need help for this problem:
I'm using a DataGrid to Insert/Modify/Cancel data from a single table.
At the moment, every cell modification of a pre-existing row is correctly
update on the DB.
I have some doubts about inserting a new row.
The table has a Id field that is a primary key defined ad Identity
properties on SQLServer engine.
This column isn't visible on the grid and a properties DefaultValue=0 is
defined.
How can I synchronise the DataTable and the DB.
If I understood right I must sync after a row inserting on DataTable
otherwise I can insert another row on DataTable (duplicate Key on Id
Column,
obviously).
I tried to catch a RowChanged event forcing a Adapter.Update( ) but I
didn't
meet my goal.
Any suggestion ??
Thanks in advance, Pierluigi.

[Pierluigi Terzoli]

Thanks Nicholas to reply me so soon.
Maybe I'm getting wrong, but even working through a Data Adapter should in
the way you explained.
If you left VisualStudio.NE T create the DataAdapter you find a Insert
Statement who:
- INSERT a new record
- SELECT ... where Id=@@IDENTITY to refresh with the new data
This happen everytime I want to insert a record by DataAdapter.
Obviously the Insert Statement described above is invoked only by
Adapter.Update( ) method.
The method check the differences between the DataTable and the DB and call
the necessary Insert/Update/Delete SQL Statement.
I don't have this behavior and I' dont' understand why ?? Grrrr !! ;))
Pierluigi.

"Nicholas Paldino [.NET/C# MVP]" <mv*@spam.guard .caspershouse.c om> ha
scritto nel messaggio news:OA******** *****@TK2MSFTNG P12.phx.gbl...

Pierluigi,

In this case, you might have to refresh the whole table, just so that
you can get your id's right. Actually, you would have to. The only way to prevent this from happening is to handle the updates yourself, instead of
through a data adapter, so that you can actually try and get the last id
inserted (in SQL Server, you can select @@identity to get the last identity column inserted).

Hope this helps.
--
- Nicholas Paldino [.NET/C# MVP]
- mv*@spam.guard. caspershouse.co m

"Pierluigi Terzoli" <pi************ ***@hotmail.com > wrote in message
news:O5******** ********@TK2MSF TNGP09.phx.gbl. ..

Hi everybody,
I need help for this problem:
I'm using a DataGrid to Insert/Modify/Cancel data from a single table.
At the moment, every cell modification of a pre-existing row is correctly update on the DB.
I have some doubts about inserting a new row.
The table has a Id field that is a primary key defined ad Identity
properties on SQLServer engine.
This column isn't visible on the grid and a properties DefaultValue=0 is
defined.
How can I synchronise the DataTable and the DB.
If I understood right I must sync after a row inserting on DataTable
otherwise I can insert another row on DataTable (duplicate Key on Id
Column,
obviously).
I tried to catch a RowChanged event forcing a Adapter.Update( ) but I
didn't
meet my goal.
Any suggestion ??
Thanks in advance, Pierluigi.

[Zürcher See]

Are you using a typed dataset?

"This column isn't visible on the grid and a properties DefaultValue=0 is
defined."

Use the generate dataset link of the dataadapter to generate the typed
dataset.

Normally a generated typed dataset in a indentity column place the following
parameter:

msdata:AutoIncr ement="true"
Visualstudio dataadapter wizard add a select after the insert/update
statment to retrive the new id, the row id in the dataset will be updated
automatically

"Pierluigi Terzoli" <pi************ ***@hotmail.com > schrieb im Newsbeitrag
news:O5******** ********@TK2MSF TNGP09.phx.gbl. ..

Hi everybody,
I need help for this problem:
I'm using a DataGrid to Insert/Modify/Cancel data from a single table.
At the moment, every cell modification of a pre-existing row is correctly
update on the DB.
I have some doubts about inserting a new row.
The table has a Id field that is a primary key defined ad Identity
properties on SQLServer engine.
This column isn't visible on the grid and a properties DefaultValue=0 is
defined.
How can I synchronise the DataTable and the DB.
If I understood right I must sync after a row inserting on DataTable
otherwise I can insert another row on DataTable (duplicate Key on Id Column, obviously).
I tried to catch a RowChanged event forcing a Adapter.Update( ) but I didn't meet my goal.
Any suggestion ??
Thanks in advance, Pierluigi.

[Pierluigi Terzoli]

Hi Zurcher,
I'm not using a typed dataset, I'm just using a DataTable I declared on my
source.
But I don't think this is the mistake.
You're right I already have my DataAdapter with Insert/Update Statement that
retrive the new id.
I need to see just a piece of source explaining how to insert a row in a
DataGrid.
What a Have to do, what choice ?
1. leave the user insert directly on the DataGrid and invoke
DataAdapter.Upd ate();
2. Provides a Insert Button and Insert by DataTable.NewRo w() and refresh the
DataGrid.
3. Provides a Insert Button and invoke a StoredProcedure who insert the new
record and rebind the DataSource
I don't understand how yet !
Apologise my understanding.
Pierluigi.

"Zürcher See" <aq****@cannabi smail.com> ha scritto nel messaggio
news:%2******** ********@TK2MSF TNGP15.phx.gbl. ..

Are you using a typed dataset?

"This column isn't visible on the grid and a properties DefaultValue=0 is
defined."

Use the generate dataset link of the dataadapter to generate the typed
dataset.

Normally a generated typed dataset in a indentity column place the following parameter:

msdata:AutoIncr ement="true"
Visualstudio dataadapter wizard add a select after the insert/update
statment to retrive the new id, the row id in the dataset will be updated
automatically

"Pierluigi Terzoli" <pi************ ***@hotmail.com > schrieb im Newsbeitrag
news:O5******** ********@TK2MSF TNGP09.phx.gbl. ..

Hi everybody,
I need help for this problem:
I'm using a DataGrid to Insert/Modify/Cancel data from a single table.
At the moment, every cell modification of a pre-existing row is correctly update on the DB.
I have some doubts about inserting a new row.
The table has a Id field that is a primary key defined ad Identity
properties on SQLServer engine.
This column isn't visible on the grid and a properties DefaultValue=0 is
defined.
How can I synchronise the DataTable and the DB.
If I understood right I must sync after a row inserting on DataTable
otherwise I can insert another row on DataTable (duplicate Key on Id

Column,

obviously).
I tried to catch a RowChanged event forcing a Adapter.Update( ) but I

didn't

meet my goal.
Any suggestion ??
Thanks in advance, Pierluigi.

[Zürcher See]

"Pierluigi Terzoli" <pi************ ***@hotmail.com > schrieb im Newsbeitrag
news:OT******** ******@tk2msftn gp13.phx.gbl...

Hi Zurcher,
I'm not using a typed dataset, I'm just using a DataTable I declared on my
source.
But I don't think this is the mistake.
You're right I already have my DataAdapter with Insert/Update Statement that retrive the new id.
I need to see just a piece of source explaining how to insert a row in a
DataGrid.
What a Have to do, what choice ?
1. leave the user insert directly on the DataGrid and invoke
DataAdapter.Upd ate();
To my opinion this is the best solution.
Add an "Update" button, when clicked call the DataAdpter on the DataTable
2. Provides a Insert Button and Insert by DataTable.NewRo w() and refresh the DataGrid.
Only if you have the impression that with a button all is more clearly
3. Provides a Insert Button and invoke a StoredProcedure who insert the new record and rebind the DataSource
I don't like stored procedure.
If you want to add some default value, you can always add them to the new
DataRow, before add it to the DataTable

DataRow row=DataTable.N ewRow();

//values that will be stored in db and never changed
row["IdOfUserWhoCre atedThisRow"]=userId;
row["DateOfCreation "]=DateTime.Now

DataTable.Add(r ow);

About DataTable event:

The DataTable are like double buffered, when you change a DataTable you can
alway reject all the changes.
The events take place when you acepts the chages to the DataTable.

(Do you speak italian?)
I don't understand how yet !
Apologise my understanding.
Pierluigi.

"Zürcher See" <aq****@cannabi smail.com> ha scritto nel messaggio
news:%2******** ********@TK2MSF TNGP15.phx.gbl. ..

Are you using a typed dataset?

"This column isn't visible on the grid and a properties DefaultValue=0 is

defined."

Use the generate dataset link of the dataadapter to generate the typed
dataset.

Normally a generated typed dataset in a indentity column place the

following

parameter:

msdata:AutoIncr ement="true"
Visualstudio dataadapter wizard add a select after the insert/update
statment to retrive the new id, the row id in the dataset will be updated automatically

"Pierluigi Terzoli" <pi************ ***@hotmail.com > schrieb im Newsbeitrag news:O5******** ********@TK2MSF TNGP09.phx.gbl. ..

Hi everybody,
I need help for this problem:
I'm using a DataGrid to Insert/Modify/Cancel data from a single table.
At the moment, every cell modification of a pre-existing row is

correctly update on the DB.
I have some doubts about inserting a new row.
The table has a Id field that is a primary key defined ad Identity
properties on SQLServer engine.
This column isn't visible on the grid and a properties DefaultValue=0 is defined.
How can I synchronise the DataTable and the DB.
If I understood right I must sync after a row inserting on DataTable
otherwise I can insert another row on DataTable (duplicate Key on Id

Column,

obviously).
I tried to catch a RowChanged event forcing a Adapter.Update( ) but I

didn't

meet my goal.
Any suggestion ??
Thanks in advance, Pierluigi.

[Pierluigi Terzoli]

Thanks again Zurcher,
so, a few minutes I received the last your post a solved (for the moment)
using the second of my options:
- I provide a button
- On click
* Create a DataRow
* Column Initialization (to respect DB constrain)
* DataTable.Inser tAt(..)
* DataAdapter.Upd ate(..)
Yes, I'm Italian ;)
Did you realise it by reading my bad english ?? ;)
Pierluigi.

"Zürcher See" <aq****@cannabi smail.com> ha scritto nel messaggio
news:Oe******** ******@TK2MSFTN GP15.phx.gbl...

"Pierluigi Terzoli" <pi************ ***@hotmail.com > schrieb im Newsbeitrag
news:OT******** ******@tk2msftn gp13.phx.gbl...

Hi Zurcher,
I'm not using a typed dataset, I'm just using a DataTable I declared on my

source.
But I don't think this is the mistake.
You're right I already have my DataAdapter with Insert/Update Statement that

retrive the new id.
I need to see just a piece of source explaining how to insert a row in a
DataGrid.
What a Have to do, what choice ?
1. leave the user insert directly on the DataGrid and invoke
DataAdapter.Upd ate();

To my opinion this is the best solution.
Add an "Update" button, when clicked call the DataAdpter on the DataTable

2. Provides a Insert Button and Insert by DataTable.NewRo w() and refresh

the

DataGrid.

Only if you have the impression that with a button all is more clearly

3. Provides a Insert Button and invoke a StoredProcedure who insert the

new

record and rebind the DataSource

I don't like stored procedure.
If you want to add some default value, you can always add them to the new
DataRow, before add it to the DataTable

DataRow row=DataTable.N ewRow();

//values that will be stored in db and never changed
row["IdOfUserWhoCre atedThisRow"]=userId;
row["DateOfCreation "]=DateTime.Now

DataTable.Add(r ow);

About DataTable event:

The DataTable are like double buffered, when you change a DataTable you

can alway reject all the changes.
The events take place when you acepts the chages to the DataTable.

(Do you speak italian?)

I don't understand how yet !
Apologise my understanding.
Pierluigi.

"Zürcher See" <aq****@cannabi smail.com> ha scritto nel messaggio
news:%2******** ********@TK2MSF TNGP15.phx.gbl. ..

Are you using a typed dataset?

"This column isn't visible on the grid and a properties DefaultValue=0

is defined."

Use the generate dataset link of the dataadapter to generate the typed
dataset.

Normally a generated typed dataset in a indentity column place the

following

parameter:

msdata:AutoIncr ement="true"
Visualstudio dataadapter wizard add a select after the insert/update
statment to retrive the new id, the row id in the dataset will be updated automatically

"Pierluigi Terzoli" <pi************ ***@hotmail.com > schrieb im Newsbeitrag news:O5******** ********@TK2MSF TNGP09.phx.gbl. ..
> Hi everybody,
> I need help for this problem:
> I'm using a DataGrid to Insert/Modify/Cancel data from a single table. > At the moment, every cell modification of a pre-existing row is

correctly

> update on the DB.
> I have some doubts about inserting a new row.
> The table has a Id field that is a primary key defined ad Identity
> properties on SQLServer engine.
> This column isn't visible on the grid and a properties

DefaultValue=0 is > defined.
> How can I synchronise the DataTable and the DB.
> If I understood right I must sync after a row inserting on DataTable
> otherwise I can insert another row on DataTable (duplicate Key on Id
Column,
> obviously).
> I tried to catch a RowChanged event forcing a Adapter.Update( ) but I
didn't
> meet my goal.
> Any suggestion ??
> Thanks in advance, Pierluigi.
>
>

[Zürcher See]

Dal nome visto che sono italiano anch'io, il mio nome non lo scrivo, non mi
piace lo spam, "Zurcher See" non vuol dire altro che "Lago di Zuigo" in
tedesco

"Pierluigi Terzoli" <pi************ ***@hotmail.com > schrieb im Newsbeitrag
news:eK******** ******@tk2msftn gp13.phx.gbl...

Thanks again Zurcher,
so, a few minutes I received the last your post a solved (for the moment)
using the second of my options:
- I provide a button
- On click
* Create a DataRow
* Column Initialization (to respect DB constrain)
* DataTable.Inser tAt(..)
* DataAdapter.Upd ate(..)
Yes, I'm Italian ;)
Did you realise it by reading my bad english ?? ;)
Pierluigi.

"Zürcher See" <aq****@cannabi smail.com> ha scritto nel messaggio
news:Oe******** ******@TK2MSFTN GP15.phx.gbl...

"Pierluigi Terzoli" <pi************ ***@hotmail.com > schrieb im Newsbeitrag

news:OT******** ******@tk2msftn gp13.phx.gbl...

Hi Zurcher,
I'm not using a typed dataset, I'm just using a DataTable I declared on

my source.
But I don't think this is the mistake.
You're right I already have my DataAdapter with Insert/Update

Statement

that

retrive the new id.
I need to see just a piece of source explaining how to insert a row in

a DataGrid.
What a Have to do, what choice ?
1. leave the user insert directly on the DataGrid and invoke
DataAdapter.Upd ate();

To my opinion this is the best solution.
Add an "Update" button, when clicked call the DataAdpter on the

DataTable

2. Provides a Insert Button and Insert by DataTable.NewRo w() and refresh

the

DataGrid.

Only if you have the impression that with a button all is more clearly

3. Provides a Insert Button and invoke a StoredProcedure who insert

the new

record and rebind the DataSource

I don't like stored procedure.
If you want to add some default value, you can always add them to the new DataRow, before add it to the DataTable

DataRow row=DataTable.N ewRow();

//values that will be stored in db and never changed
row["IdOfUserWhoCre atedThisRow"]=userId;
row["DateOfCreation "]=DateTime.Now

DataTable.Add(r ow);

About DataTable event:

The DataTable are like double buffered, when you change a DataTable you

can

alway reject all the changes.
The events take place when you acepts the chages to the DataTable.

(Do you speak italian?)

I don't understand how yet !
Apologise my understanding.
Pierluigi.

"Zürcher See" <aq****@cannabi smail.com> ha scritto nel messaggio
news:%2******** ********@TK2MSF TNGP15.phx.gbl. ..
> Are you using a typed dataset?
>
> "This column isn't visible on the grid and a properties

DefaultValue=0 is

> defined."
>
> Use the generate dataset link of the dataadapter to generate the

typed > dataset.
>
> Normally a generated typed dataset in a indentity column place the
following
> parameter:
>
> msdata:AutoIncr ement="true"
>
>
> Visualstudio dataadapter wizard add a select after the insert/update
> statment to retrive the new id, the row id in the dataset will be

updated

> automatically
>
> "Pierluigi Terzoli" <pi************ ***@hotmail.com > schrieb im

Newsbeitrag

> news:O5******** ********@TK2MSF TNGP09.phx.gbl. ..
> > Hi everybody,
> > I need help for this problem:
> > I'm using a DataGrid to Insert/Modify/Cancel data from a single

table. > > At the moment, every cell modification of a pre-existing row is
correctly
> > update on the DB.
> > I have some doubts about inserting a new row.
> > The table has a Id field that is a primary key defined ad Identity
> > properties on SQLServer engine.
> > This column isn't visible on the grid and a properties

DefaultValue=0

is

> > defined.
> > How can I synchronise the DataTable and the DB.
> > If I understood right I must sync after a row inserting on DataTable > > otherwise I can insert another row on DataTable (duplicate Key on Id > Column,
> > obviously).
> > I tried to catch a RowChanged event forcing a Adapter.Update( ) but I > didn't
> > meet my goal.
> > Any suggestion ??
> > Thanks in advance, Pierluigi.
> >
> >
>
>