Inserting rows into a Datatable fires two actions: Add and Change

[Guest]

Hello,

When I insert a new row into a datatable, the following event fires:

RowChanged, with the action DataRowAction.Add

So, within the sub that handles this event I call another sub to update the
datatable with its sqldataadapter (the update command only affects one
database table)

The odd thing is that when the update completes, the following event for the
same datatable fires:

RowChanged, with the action DataRowAction.Change

.... And it refers to the same row I've inserted.

Why does it fire up ? the direction of the update is from my datatable to my
database. The fact that I'm updating the database should not affect the
contents of my actual datatable, it isn't ?

For the time being, I'm using a boolean variable to control this annoying
fact.

Thanks in advance,

Roger


..NET 2005 and DB developer

 

[WenYuan Wang [MSFT]]

Hello Roger,

Sorry, I'm not sure I have understood your issue very clearly. According to
you description, you added handler to DataTable.RowChanged event and notice
this event was fired two times. Could you please past some code snippet
about your application?

DataRowAction.Add should be fired when new row has been inserted into
table. However, DataRowAction.Chang should be fired when this row has been
changed. Did you modify current row in the RowChanged event?

By the way ,what does the insertcommand looks like? Could you past the
exact insert command text in newsgroup? This is also helpful. I assume you
are developing against WinForm project. If you are working with WebForm
Project, please don't hesitate to correct me. I'm looking forward your
reply and glad to assist you.

Have a nice day
Sincerely,
Wen Yuan
Microsoft Online Community Support
==================================================
This posting is provided "AS IS" with no warranties, and confers no rights.

 

[Guest]

Hi WenYuan, and thanks for your answer.

Now I see the reason for it: that's why I like programming... some doubts
make clear a lot of things as a domino effect! ... I will explain:

As the datatable has an ID identity column, after I update the table on my
bd, the dataadapter returns the database generated ID for updating my
datatable; this causes the change event fire again.

This is my INSERT COMMAND:

"INSERT INTO PETICIONS_TRESORERIA_LIQUIDACIONS (codtreballador, persona,
data, descripcio, import, liquidat) VALUES
(@codtreballador,@persona,@data,@descripcio,@import,@liquidat) SET @id
=(select scope_identity())", connexioLeiter)

Look at last part (set @id=...) that returns the new ID ... AHA!

I have also the following code that defines the insert command parameter:

Dim idparamOutput As New SqlParameter("@id", SqlDbType.Int)
idparamOutput.Direction = ParameterDirection.Output
idparamOutput.SourceColumn = "id"
insertCommand.Parameters.Add(idparamOutput)

My surprise was that the ID column already had a value AFTER updating the
bd, but the cause was that as the database is strongly typed, it has the ID
column with "autoincrement" property set to TRUE. In fact, a new value is
added when the row is created on the dataset before updating the db and it
MATCHES the id that the bd will create when updating the database if ONLY ONE
USER is in the program.

On the other hand, if two users are on the program and the first user
creates a new row with id "n" and updates it on the db, and after this the
second user creates a new row , we will see that before the update to the
database, on the datatable, the id will be "n", but AFTER updating it will
become "n+1" as this is the last REAL index available, as the first user has
occupied the index "n"(verified).

Now, I can see too, the cause for another related error I had long before
when I forgot to put this line on the insert parameter definitions: "
idparamOutput.SourceColumn = "id"

As that line was not present, the program worked well on single user , but
when more than one user were working with the program, the ids generated were
the same for more than one line and it caused primary key violations.

Bye,

 

[WenYuan Wang [MSFT]]

Dear Roger,
Cool! Thanks for your clarify.

Apparent, the last part in the insert command return a new ID. Thus,
DataRowAction.Chang even fires.

As that line was not present, the program worked well on single user , but when more than one user
were working with the program, the ids generated were the same for more than one line and it caused
primary key violations.

The solution for this issue is that you can set the AutoINcrementValue = -1
and the seed to -1. This way, the value submitted to the db will always be
negative, so the DB will always step in and assign it. Those way two
versions of the app won't ever step on each other. Actually, there are
many posts discussed on this issue. The best way is seting both
AutoINcrementValue and seed to -1.

Hope this helps. Please let me know if you meet any futher issue or have
anything unclear. I'm glad to assist you.
Have a great day,
Sincerely,
Wen Yuan.

 

[WenYuan Wang [MSFT]]

Dear Roger,
Cool! Thanks for your clarify.

Apparent, the last part in the insert command return a new ID. Thus,
DataRowAction.Chang even fires.

As that line was not present, the program worked well on single user , but when more than one user
were working with the program, the ids generated were the same for more than one line and it caused
primary key violations.

The solution for this issue is that you can set the AutoINcrementValue = -1
and the seed to -1. This way, the value submitted to the db will always be
negative, so the DB will always step in and assign it. Those way two
versions of the app won't ever step on each other. Actually, there are
many posts discussed on this issue. The best way is seting both
AutoINcrementValue and seed to -1.

Hope this helps. Please let me know if you meet any futher issue or have
anything unclear. I'm glad to assist you.
Have a great day,
Sincerely,
Wen Yuan.

 

[Guest]

Hello, WenYuan ...

You said:

The solution for this issue is that you can set the AutoINcrementValue = -1
and the seed to -1

I think it is not necessary as long as my update command does not transmit
the id to the database because the database creates it an I retrieve it later:

INSERT INTO PETICIONS_TRESORERIA_LIQUIDACIONS (codtreballador, persona,
data, descripcio, import, liquidat) VALUES
(@codtreballador,@persona,@data,@descripcio,@import,@liquidat) SET @id
=(select scope_identity()) ...

Also, my application cannot crash for duplicate keys now...

Thank you,


Roger Tranchez
..NET 2005 and DB developer

 

[WenYuan Wang [MSFT]]

Hi Roger

Aha, it seems like you have figured out the root cause and everything works
fine on your side now. I'm glad to hear such nice news. We are truly glad
to assist. If you meet any further issue in ADO.net, please also feel free
to let us know. I'm standing by. Have a great day, thanks.

Sincerely,
Wen Yuan
Microsoft Online Community Support
==================================================
This posting is provided "AS IS" with no warranties, and confers no rights.