Sync between DataGrid and DB

  • Thread starter Thread starter Pierluigi Terzoli
  • Start date Start date
P

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.
 
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.
 
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.NET 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 said:
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]
- (e-mail address removed)

Pierluigi Terzoli said:
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.
Click to expand...
Click to expand...
 
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:AutoIncrement="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
 
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.Update();
2. Provides a Insert Button and Insert by DataTable.NewRow() 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.
 
Pierluigi Terzoli said:
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.Update();
Click to expand...

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.NewRow() and refresh the
DataGrid.
Click to expand...

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
Click to expand...

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.NewRow();

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

DataTable.Add(row);

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?)
 
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.InsertAt(..)
* DataAdapter.Update(..)
Yes, I'm Italian ;)
Did you realise it by reading my bad english ?? ;)
Pierluigi.

Zürcher See said:
Pierluigi Terzoli said:
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.Update();
Click to expand...

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.NewRow() and refresh the
DataGrid.
Click to expand...

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
Click to expand...

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.NewRow();

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

DataTable.Add(row);

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.
Click to expand...
DefaultValue=0
is
Click to expand...
Click to expand...
 
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 said:
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.InsertAt(..)
* DataAdapter.Update(..)
Yes, I'm Italian ;)
Did you realise it by reading my bad english ?? ;)
Pierluigi.

on
my
source.
But I don't think this is the mistake.
You're right I already have my DataAdapter with Insert/Update
Click to expand...
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.Update();
Click to expand...

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.NewRow() and
Click to expand...
refresh
the
DataGrid.
Click to expand...

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
Click to expand...
the
new
record and rebind the DataSource
Click to expand...

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.NewRow();

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

DataTable.Add(row);

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" <[email protected]> ha scritto nel messaggio
Are you using a typed dataset?

"This column isn't visible on the grid and a properties
Click to expand...
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:AutoIncrement="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

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
Click to expand...
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.
Click to expand...
Click to expand...
Click to expand...
 
Back
Top