Datagrid with Trigger fails to update the third? time

[MB]

Hello!

I am using a trigger to set ChangedDate and Changed by:

CREATE trigger [tri_tblCustomers_update] on [tblCustomers] for update as
declare @ID int
select @ID=(select [TreeID] from inserted)
update [tblCustomers] set ChangedDate=GetDate(), ChangedBy=suser_sname()
where [TreeID]=@ID

The problem is that when I change a value (a column called CustomerName) in
a datagrid or other data component having ds.tables("tblCustomers") as
datasource and then pressing my save button, it works fine the first and
second time, but then (the third time I think) when changing a value and
clicking my save button, I got the error message "Additional information:
Concurrency violation: the UpdateCommand affected 0 records."

I am using simple da.fill and da.update commands.
Is something wrong with my trigger or do I need some additional code?

Regards Magnus

 

[Kevin Yu [MSFT]]

Hi MB,

First of all, I would like to confirm my understanding of your issue. From
your description, I understand that when you have a trigger in the
database, when updating the data source, there will be a concurrency
vialoation thrown. If there is any misunderstanding, please feel free to
let me know.

I have check the trigger, it doesn't seem to be the trigger that causes the
problem. Does the error always occurs at the third time you update the data
source? If you remove the trigger, does the error still occurs? Could you
please paste you update command here so that I can make further research on
this issue? Thanks for your cooperation!

If anything is unclear, please feel free to reply to the post.

Kevin Yu
=======
"This posting is provided "AS IS" with no warranties, and confers no
rights."

 

[Kevin Yu [MSFT]]

Hi MB,

Furthermore, it is not recommended to use triggers. I think you can try to
set the change date in the update command in a stored procedure.

Kevin Yu
=======
"This posting is provided "AS IS" with no warranties, and confers no
rights."

 

[Kevin Yu [MSFT]]

Hi MB,

I'd like to know if this issue has been resolved yet. Is there anything
that I can help. I'm still monitoring on it. If you have any questions,
please feel free to post them in the community.

Kevin Yu
=======
"This posting is provided "AS IS" with no warranties, and confers no
rights."

 

[MB]

Hello!

Sorry for the delay. Had some other issues a few days, but now I'm back online.
I still think I should use the triggers, If I could solve this problem.
Yes, the problem still exists. I will below add a routine for reproducing the problem.
I would be very glad if you can help me, because this is very annoying for me.

1. Create a database called UpdateTriggerProblem (this was made using MS SQL Server 2000)
2. Add a table named MyTbl with the three columns CustID (varchar(50) Primary key), CustName(varchar(50)), ChangedDate(datetime)
3. Add a trigger as follows

CREATE trigger [tri_MyTbl_update] on dbo.MyTbl for update as
declare @ID varchar(50)
select @ID=(select [CustID] from inserted)
update [MyTbl] set ChangedDate=GetDate()
where [CustID]=@ID

2. Create a new VB application with a form.
2. Add a datagrid and a button to the form
3. Add the code below in the form code

Dim ds As New DataSet
Dim cn As New SqlClient.SqlConnection("integrated security=SSPI;data source=localhost;initial catalog=UpdateTriggerProblem")
Dim da As New SqlClient.SqlDataAdapter("select * from MyTbl", cn)
Dim cb As New SqlClient.SqlCommandBuilder(da)
Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
da.Fill(ds, "MyTbl")
DataGrid1.DataSource = ds.Tables("MyTbl")
End Sub
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
If ds.HasChanges Then
cb.RefreshSchema()
da.Update(ds, "MyTbl")
End If
End Sub

4. Change the data source at second row (localhost) if necessary.

Now it is ready for testing. To test the project do as follows (always change the rowfocus before pressing the button).
1. Run project
2. Add the first row by adding values in CustID and CustName, and press the button
3. Change the CustName, and press the button
4. Do the 3:rd item again, and again if necessary

You should have got the problem by now.

Best regards Magnus

 

[Kevin Yu [MSFT]]

Hi MB,

Thanks for your steps to reproduce the problem. It makes things easier for
me to debug, and I have found the cause of the DBConcurrencyException.

I reproduced the problem with 2 trials of updating. The first time we
update the data source, the new value is written. Then the trigger comes
up. It modifies the ChangedDate field of that record. When the second
update fires, the DataAdapter found that the record in data source has been
changed, so an DBConcurrencyException was thrown.

To avoid this, we have to refresh the data in application after each
update. Here is a code snippet which refreshes the single row by the
primary key.

Private Sub UpdateRow(ByVal TableName As String, ByVal ID As String)
'Get a reference to the specified row
Dim dr As DataRow = dsAllData.Tables(TableName).Rows.Find(ID)

'Create a Command update to pull the new underlying data
Dim cmd As New SqlClient.SqlCommand("SELECT * FROM " & TableName & _
" WHERE ID=" & ID, connCustSvc)

'Open the connection and create the DataReader
connCustSvc.Open()
Dim rdr As SqlClient.SqlDataReader = cmd.ExecuteReader()
rdr.Read()

'Copy the new data from the database to the DataRow
Dim dc As DataColumn
For Each dc In dr.Table.Columns
If dc.ReadOnly = False Then _
dr.Item(dc.ColumnName) = rdr.Item(dc.ColumnName)
Next

'Accept changes in the DataRow
dr.AcceptChanges()
connCustSvc.Close()
End Sub

For more information about tackling data concurrency exceptions using the
dataSet object, please check the following article. It is a good article
which explains the cause of difference concurrency issue for you.

http://msdn.microsoft.com/msdnmag/issues/03/04/dataconcurrency/default.aspx

HTH. If anything is unclear, please feel free to reply to the post.

Kevin Yu
=======
"This posting is provided "AS IS" with no warranties, and confers no
rights."

 

[Kevin Yu [MSFT]]

Hi MB,

I'd like to know if this issue has been resolved yet. Is there anything
that I can help. I'm still monitoring on it. If you have any questions,
please feel free to post them in the community.

Kevin Yu
=======
"This posting is provided "AS IS" with no warranties, and confers no
rights."