Concurrency error handling

[John]

Hi

I have asked this question before but have not received any clear answer. I
have a winform app with bound controls via dataadapter/dataset combination.I
have a dbconcurrency exception ex. Now I have the following;

DataRow = ex.Row
DataTable = ex.Row.Table
DataColumns = ex.Row.Table.Columns
DataSet = ex.row.Table.Dataset

How can I now via code, do the following;

1. Make a copy of the datarow,

2. Re-read the row from datasource,

3. Update read row with values form row copy.

Any help woudl be appreciated.

Many Thanks

Regards

[Rich P]

Hi John,

What you need to do is to create a selectcommand, insertcommand,
updatecommand with your dataAdapter. Note: the DataAdapter does not
work with MS Access - well except for the Select command, but none of
the action commands like Insert/Update/Delete. But they work well with
MS Sql Server (and probably Oracle)

Imports System
Imports System.Data.SqlClient
...

'--form level variables here
Dim da as New SqlDataAdapter, ds As New Dataset
Dim conn As New SqlConnection

Private Sub Form1_Load(...)
conn.ConnectionString = "Data Source=yourSvr;Initial
Catalog=yourDB;Integrated Security=True"

da.SelectCommand = New SqlCommand
da.InsertCommand = New SqlCommand
da.UpdateCommand = New SqlCommand

da.SelectCommand.connection = conn
da.InsertCommand.connection = conn
da.UpdateCommand.connection = conn

da.SelectCommand..CommandText = "Select * From tblx Where..."
da.InsertCommand.CommandText = "Insert Into tblx Select @f1, @f2 @f3"

da.UpdateCommand.CommandText = "Update tblx Set f1 = @f1, f2 = @f2, f3 =
@f3 Where ID = @ID"

da.InsertCommand.Parameters.Add("@f1", SqlDBType.Varchar, 50, "f1")
...
da.UpdateCommand.Parameters.Add("@f1", SqlDbType.Varchar, 50, "f1")

da.Fill(ds, "tbl_Local")

End Sub

Private Sub Insert_Update()

Dim dr As DataRow
dr = ds.Tables("tbl_Local").NewRow
dr.BeginEdit
dr("f1") = "test1"
dr("f2") = "test2"
dr("f3") = "test3
dr.EndEdit
ds.Tables("tbl_Local").Rows.Add(dr)
da.Update(ds, "tbl_Local")

End Sub

So you create tbl_Local on the fly with the da.Fill command. Then
whenever you add a new row to tbl_Local or edit a field in one of the
rows - the dataAdapter da will automatically know what happened. You
call the da.Update command -- for Inserts and Update (and deletes also).
This will update the table on the server with either a new row or an
edit update on a particular row. tbl_local is a mirror image of the
server table in your local memory.

Rich

*** Sent via Developersdex http://www.developersdex.com ***

[Cor Ligthert[MVP]]

John,

All your three questions are impossible to do.

>
1. Make a copy of the datarow,

A datarow is a collection of objects that exist partially in the datarow
itself as item collection and partially in the datatable columns collection.

2. Re-read the row from datasource,

You never need to re-read the row from a datasource as you haven't
overwritten it.

3. Update read row with values form row copy.

As long as you don't reset your concurrency protection, you would not be
able to overwrite it with whatever you want, but if you want to do that, you
simple use the original row and disable the concurrency checking.

Cor

[John]

Hi Cor

Thanks, How can I disable concurrency checking?

Thanks

Regards

"Cor Ligthert[MVP]" <no************@planet.nlwrote in message
news:uc**************@TK2MSFTNGP02.phx.gbl...

John,

All your three questions are impossible to do.

>>
1. Make a copy of the datarow,

A datarow is a collection of objects that exist partially in the datarow
itself as item collection and partially in the datatable columns
collection.

>2. Re-read the row from datasource,

You never need to re-read the row from a datasource as you haven't
overwritten it.

>3. Update read row with values form row copy.

As long as you don't reset your concurrency protection, you would not be
able to overwrite it with whatever you want, but if you want to do that,
you simple use the original row and disable the concurrency checking.

Cor

[Steve Gerrard]

John wrote:

Hi

I have asked this question before but have not received any clear
answer. I have a winform app with bound controls via
dataadapter/dataset combination.I have a dbconcurrency exception ex.
Now I have the following;
DataRow = ex.Row
DataTable = ex.Row.Table
DataColumns = ex.Row.Table.Columns
DataSet = ex.row.Table.Dataset

How can I now via code, do the following;

1. Make a copy of the datarow,

2. Re-read the row from datasource,

3. Update read row with values form row copy.

Any help woudl be appreciated.

Many Thanks

Regards

This sounds to me like you want to make the changes, despite encountering a
concurrency exception. If that is correct, then why are you checking concurrency
in the first place? It is not required; it is something you decide to do if you
need to block an update when the row on the server has changed. If you don't
want to block such an update, then don't check it.

What is the purpose of the copy of the datarow? Whether you use it or the
original to update the "read row" would make no difference at all. Either way,
when you are done, the "read row" will now match both the original row and the
copy; what actually is the point of that?

I have a suspicion that you want to identify which columns the user has changed,
and change those, leaving the remaining columns as they exist on the server. If
that is the case, option one would be to only update changed columns in the
first place, instead of all columns. The other option is to re-read the row on
the server, then identify the changes made by a user and apply those to the
newly read row. That would work as long as the row doesn't get changed on the
server again in the mean time.

You can examine a row to see what has been changed, by looking at the Original
and Current values of each column. Here is an example. Perhaps thinking about
this will help you define what you are trying to do, and help you find a way to
do it.

Private Sub TestRow(ByVal DR As DataRow)
Dim Curr As Object
Dim Orig As Object
Dim dc As DataColumn
For Each dc In DR.Table.Columns
Curr = DR.Item(dc, DataRowVersion.Current)
Orig = DR.Item(dc, DataRowVersion.Original)
If Curr.Equals(Orig) Then
' user has not changed it
Else
' user changed this column
End If
Next
End Sub

[Cor Ligthert[MVP]]

Hi John,

I thought that I had answered you, however I don't see my answer

:-)

Have a look at the SQL code for the update in your program or stored
procedures where is written something as.

Select TimeStamp from Table

If TimeStamp = @TimeStamp
Begin and then the update transaction in SQL .

As you delete that part, then the SQL code will probably eat everything you
deliver to it.

(It can also be a range of old values, compared with the new selected
existing values)

(It is probably easier to write the Update SQL yourself new by hand and than
skip the Select of the current values or current tinestamp)

Cor
"John" <in**@nospam.infovis.co.ukschreef in bericht
news:u0**************@TK2MSFTNGP05.phx.gbl...

Hi Cor

Thanks, How can I disable concurrency checking?

Thanks

Regards

"Cor Ligthert[MVP]" <no************@planet.nlwrote in message
news:uc**************@TK2MSFTNGP02.phx.gbl...

>John,

All your three questions are impossible to do.

>>>
1. Make a copy of the datarow,

A datarow is a collection of objects that exist partially in the datarow
itself as item collection and partially in the datatable columns
collection.

>>2. Re-read the row from datasource,

You never need to re-read the row from a datasource as you haven't
overwritten it.

>>3. Update read row with values form row copy.

As long as you don't reset your concurrency protection, you would not be
able to overwrite it with whatever you want, but if you want to do that,
you simple use the original row and disable the concurrency checking.

Cor

[John]

Hi Cor

I have the below Update SP (slightly simplified to exclude irrelevant
columns);

UPDATE [dbo].[Clients] SET [Company] = @Company, [Address 1] = @Address_1
WHERE (([ID] = @Original_ID) AND ([SSMA_TimeStamp] =
@Original_SSMA_TimeStamp));
SELECT ID, Company, [Address 1] SSMA_TimeStamp FROM Clients WHERE (ID = @ID)

Ideally on concurrency violation I would like to give user option to force
save or cancel save. What would be the mechanism for that? My applications
is a vb.net winform app.

Many Thanks

Regards

"Cor Ligthert[MVP]" <no************@planet.nlwrote in message
news:0D**********************************@microsof t.com...

Hi John,

I thought that I had answered you, however I don't see my answer

:-)

Have a look at the SQL code for the update in your program or stored
procedures where is written something as.

Select TimeStamp from Table

If TimeStamp = @TimeStamp
Begin and then the update transaction in SQL .

As you delete that part, then the SQL code will probably eat everything
you
deliver to it.

(It can also be a range of old values, compared with the new selected
existing values)

(It is probably easier to write the Update SQL yourself new by hand and
than
skip the Select of the current values or current tinestamp)

Cor
"John" <in**@nospam.infovis.co.ukschreef in bericht
news:u0**************@TK2MSFTNGP05.phx.gbl...

>Hi Cor

Thanks, How can I disable concurrency checking?

Thanks

Regards

"Cor Ligthert[MVP]" <no************@planet.nlwrote in message
news:uc**************@TK2MSFTNGP02.phx.gbl...

>>John,

All your three questions are impossible to do.
1. Make a copy of the datarow,

A datarow is a collection of objects that exist partially in the datarow
itself as item collection and partially in the datatable columns
collection.

2. Re-read the row from datasource,

You never need to re-read the row from a datasource as you haven't
overwritten it.
3. Update read row with values form row copy.

As long as you don't reset your concurrency protection, you would not be
able to overwrite it with whatever you want, but if you want to do that,
you simple use the original row and disable the concurrency checking.

Cor

[Cor Ligthert[MVP]]

John,

Then create an extra Update where that WHERE clause part from the timestamp
from the one you show now is deleted.

I can not tell you how to do it in VB as I don't know what you use to update
in that, it can be an execute nonquery or a xxAdapter. Those you have of
course to make new, or simple set the right update sql to its command every
time you use the original or the overwritting one.
However be aware what you doing, as this can give big errors.
(There is a sample in banking busines where this was used to fraud).

Cor

"John" <in**@nospam.infovis.co.ukschreef in bericht
news:u5*************@TK2MSFTNGP02.phx.gbl...

Hi Cor

I have the below Update SP (slightly simplified to exclude irrelevant
columns);

UPDATE [dbo].[Clients] SET [Company] = @Company, [Address 1] = @Address_1
WHERE (([ID] = @Original_ID) AND ([SSMA_TimeStamp] =
@Original_SSMA_TimeStamp));
SELECT ID, Company, [Address 1] SSMA_TimeStamp FROM Clients WHERE (ID =
@ID)

Ideally on concurrency violation I would like to give user option to force
save or cancel save. What would be the mechanism for that? My applications
is a vb.net winform app.

Many Thanks

Regards

"Cor Ligthert[MVP]" <no************@planet.nlwrote in message
news:0D**********************************@microsof t.com...

>Hi John,

I thought that I had answered you, however I don't see my answer

:-)

Have a look at the SQL code for the update in your program or stored
procedures where is written something as.

Select TimeStamp from Table

If TimeStamp = @TimeStamp
Begin and then the update transaction in SQL .

As you delete that part, then the SQL code will probably eat everything
you
deliver to it.

(It can also be a range of old values, compared with the new selected
existing values)

(It is probably easier to write the Update SQL yourself new by hand and
than
skip the Select of the current values or current tinestamp)

Cor
"John" <in**@nospam.infovis.co.ukschreef in bericht
news:u0**************@TK2MSFTNGP05.phx.gbl...

>>Hi Cor

Thanks, How can I disable concurrency checking?

Thanks

Regards

"Cor Ligthert[MVP]" <no************@planet.nlwrote in message
news:uc**************@TK2MSFTNGP02.phx.gbl...
John,

All your three questions are impossible to do.

>
1. Make a copy of the datarow,
>
A datarow is a collection of objects that exist partially in the
datarow itself as item collection and partially in the datatable
columns collection.

2. Re-read the row from datasource,
>
You never need to re-read the row from a datasource as you haven't
overwritten it.
3. Update read row with values form row copy.
>
As long as you don't reset your concurrency protection, you would not
be able to overwrite it with whatever you want, but if you want to do
that, you simple use the original row and disable the concurrency
checking.

Cor