Problem Concurrency / Databinding

[E®!k \\/!sser]

I have a problem with Concurrency control.

The situation.
- A Datadriven form. I pass a name for the table to show and a PK value.
This is how the databinding is done:

LOCAL oTextbox AS System.Windows.Forms.TextBox
oTextbox := System.Windows.Forms.TextBox{}
oTextbox:Size := System.Drawing.Size{ wEditWidth,
wHeight+wEditHeight}
oTextbox:Location := System.Drawing.Point{ wLeft+wTextWidth+wVerGap,
wPixelHeight+7 }
oTextbox:Name := cName
oTextboxataBindings:Add(Binding{"Text", dt, cName } )
SELF:Controls:Add( oTextbox )


This is how the datastuff gets initialized:
oCommand:CommandText := cSqlUpdate
SELFaUpdate := SqlDataAdapter{}
SELFaUpdate:SelectCommand := oCommand
SELFtUpdate := DataTable{}
SELFaUpdate:Fill( SELF:dtUpdate )
SELFBindingSource := BindingSource{}
SELFBindingSourceataSource := SELF:dtUpdate


When the user has edited data in the form and presses the OK/Save button,
the UpdateDatabase method is called.
This works all fine!!

Now I have added code to handle situations where someone else has changed
the same record
It is based upon a sample here:
http://msdn.microsoft.com/en-us/library/ms171936.aspx
So when a dbConcurrency execption is raized, I like to show the current row
on the database (the content changed by the oher user) ,
the content of the row when de user strated editing en the changed ccontent.
Now here is where it goes wrong . When the user selects to have the database
updated with his changes (pressed yes) The call to UpdateDatabases() still
leads to a
DBConcurrencyException.

So my question is, what to do to force the changes writtten to the database?
The code in the ProcessResponce method is the same as used in several
samples I have found on the MS website, so that cannot be wrong <g>. So my
guess is it has somethnig to do with the (way I use) databinding.

Any suggestions??

tia,

Erik


VIRTUAL Method UpdateDatabase() AS System.Boolean
LOCAL lReturn AS LOGIC
TRY
lReturn := FALSE
SELF:Validate()
SELFBindingSource:EndEdit()
SqlCommandBuilder{ SELF:daUpdate }
SELF:daUpdate:Update( SELF:dtUpdate )
SELF:dtUpdate:AcceptChanges()
lReturn := TRUE
CATCH ex AS System.Data.DBConcurrencyException
LOCAL drResponse AS DialogResult
LOCAL oRow AS DataRow
oRow := (DataRow) ex:Row
drResponse := MessageBox.Show( SELF:CreateMessage( oRow ),
"Multi-user probleem",MessageBoxButtons.YesNo )
SELFrocessResonse( drResponse )
CATCH ex AS System.Exception
MessageBox.Show( ex:Message )
FINALLY
//clean up
END TRY
RETURN lReturn

VIRTUAL METHOD CreateMessage( oRow AS DataRow ) AS STRING
RETURN "Database: " + SELF:GetRowData( SELF:GetCurrentRowInDB(oRow),
DataRowVersion.Default) +CRLF +;
"Origineel: " + SELF:GetRowData(oRow, DataRowVersion.Original)
+ CRLF +;
"Voorgesteld: " + SELF:GetRowData(oRow, DataRowVersion.Current)
+ CRLF +;
"Wilt u nog steeds de Database updaten met de voorgestelde
waarde(n)?"

VIRTUAL METHOD GetRowData( oRow AS DataRow , RowVersion AS DataRowVersion )
AS String
LOCAL rowData AS STRING
LOCAL i AS System.Int32
rowData :=""
FOR i := 0 UPTO oRow:ItemArray:Length-1
RowData := RowData + oRow:Item[i, RowVersion]:ToString() + " "
NEXT
RETURN rowData

VIRTUAL METHOD GetCurrentRowInDB( oErrorRow AS DataRow ) AS DataRow
LOCAL drCurrentRow AS DataRow
SELFTempTable := DataTable{}
SELFAUpdate:Fill( SELFTempTable )
drCurrentRow := oTempTable:Select("ID="+ oErrorRow:Item[0]:ToString())[1]
RETURN drCurrentRow


VIRTUAL METHOD ProcessResonse( dr AS DialogResult )AS VOID
IF dr=Windows.Forms.DialogResult.Yes
SELF:dtUpdate:Merge( SELFTempTable, TRUE )
SELF:UpdateDatabase()
ELSEIF dr=Windows.Forms.DialogResult.No
SELFTUpdate:Merge( SELFTempTable )
ENDIF
RETURN

 

[Cor Ligthert[MVP]]

Erik,

Is you concurrency based on checking a timestamp or in checking all changed
columns.

As I saw it quick in the given sample that is based on columnchange
checking.

Cor

I have a problem with Concurrency control.

The situation.
- A Datadriven form. I pass a name for the table to show and a PK value.
This is how the databinding is done:

LOCAL oTextbox AS System.Windows.Forms.TextBox
oTextbox := System.Windows.Forms.TextBox{}
oTextbox:Size := System.Drawing.Size{ wEditWidth,
wHeight+wEditHeight}
oTextbox:Location := System.Drawing.Point{ wLeft+wTextWidth+wVerGap,
wPixelHeight+7 }
oTextbox:Name := cName
oTextboxataBindings:Add(Binding{"Text", dt, cName } )
SELF:Controls:Add( oTextbox )


This is how the datastuff gets initialized:
oCommand:CommandText := cSqlUpdate
SELFaUpdate := SqlDataAdapter{}
SELFaUpdate:SelectCommand := oCommand
SELFtUpdate := DataTable{}
SELFaUpdate:Fill( SELF:dtUpdate )
SELFBindingSource := BindingSource{}
SELFBindingSourceataSource := SELF:dtUpdate


When the user has edited data in the form and presses the OK/Save button,
the UpdateDatabase method is called.
This works all fine!!

Now I have added code to handle situations where someone else has changed
the same record
It is based upon a sample here:
http://msdn.microsoft.com/en-us/library/ms171936.aspx
So when a dbConcurrency execption is raized, I like to show the current
row
on the database (the content changed by the oher user) ,
the content of the row when de user strated editing en the changed
ccontent.
Now here is where it goes wrong . When the user selects to have the
database
updated with his changes (pressed yes) The call to UpdateDatabases() still
leads to a
DBConcurrencyException.

So my question is, what to do to force the changes writtten to the
database?
The code in the ProcessResponce method is the same as used in several
samples I have found on the MS website, so that cannot be wrong <g>. So my
guess is it has somethnig to do with the (way I use) databinding.

Any suggestions??

tia,

Erik


VIRTUAL Method UpdateDatabase() AS System.Boolean
LOCAL lReturn AS LOGIC
TRY
lReturn := FALSE
SELF:Validate()
SELFBindingSource:EndEdit()
SqlCommandBuilder{ SELF:daUpdate }
SELF:daUpdate:Update( SELF:dtUpdate )
SELF:dtUpdate:AcceptChanges()
lReturn := TRUE
CATCH ex AS System.Data.DBConcurrencyException
LOCAL drResponse AS DialogResult
LOCAL oRow AS DataRow
oRow := (DataRow) ex:Row
drResponse := MessageBox.Show( SELF:CreateMessage( oRow ),
"Multi-user probleem",MessageBoxButtons.YesNo )
SELFrocessResonse( drResponse )
CATCH ex AS System.Exception
MessageBox.Show( ex:Message )
FINALLY
//clean up
END TRY
RETURN lReturn

VIRTUAL METHOD CreateMessage( oRow AS DataRow ) AS STRING
RETURN "Database: " + SELF:GetRowData(
SELF:GetCurrentRowInDB(oRow),
DataRowVersion.Default) +CRLF +;
"Origineel: " + SELF:GetRowData(oRow, DataRowVersion.Original)
+ CRLF +;
"Voorgesteld: " + SELF:GetRowData(oRow, DataRowVersion.Current)
+ CRLF +;
"Wilt u nog steeds de Database updaten met de voorgestelde
waarde(n)?"

VIRTUAL METHOD GetRowData( oRow AS DataRow , RowVersion AS
DataRowVersion )
AS String
LOCAL rowData AS STRING
LOCAL i AS System.Int32
rowData :=""
FOR i := 0 UPTO oRow:ItemArray:Length-1
RowData := RowData + oRow:Item[i, RowVersion]:ToString() + " "
NEXT
RETURN rowData

VIRTUAL METHOD GetCurrentRowInDB( oErrorRow AS DataRow ) AS DataRow
LOCAL drCurrentRow AS DataRow
SELFTempTable := DataTable{}
SELFAUpdate:Fill( SELFTempTable )
drCurrentRow := oTempTable:Select("ID="+ oErrorRow:Item[0]:ToString())[1]
RETURN drCurrentRow


VIRTUAL METHOD ProcessResonse( dr AS DialogResult )AS VOID
IF dr=Windows.Forms.DialogResult.Yes
SELF:dtUpdate:Merge( SELFTempTable, TRUE )
SELF:UpdateDatabase()
ELSEIF dr=Windows.Forms.DialogResult.No
SELFTUpdate:Merge( SELFTempTable )
ENDIF
RETURN

 

[E®!k \\/!sser]

Hi Cor,

The concurrency is based upon the internal mechanism of the dataserver
In case someone else changed the row in the meantime, this row in
UpdateServer throws an DBConcurrencyException.

SELF:daUpdate:Update( SELF:dtUpdate )

If this is the case and the users wants his changes to be written to the
database,
this code is executed:

VIRTUAL METHOD ProcessResonse( dr AS DialogResult )AS VOID
IF dr=Windows.Forms.DialogResult.Yes
SELF:dtUpdate:Merge( SELFTempTable, TRUE )
SELF:UpdateDatabase()

This should work according to the walkthrough about handling concurrency on
http://msdn.microsoft.com/en-us/library/ms171936.aspx

But, unfortunatally, it does not.

Now the MS sample does not have databinding to controls, so I guess this
asks for on or two extra methodcalls on the Bindingsource, the dataadapter
and/or the datatable. But I have tried everythnig I could think of, without
luck.

Any suggestions?
(groeten uit Groningen)

tia.

Erik

Erik,

Is you concurrency based on checking a timestamp or in checking all
changed columns.

As I saw it quick in the given sample that is based on columnchange
checking.

Cor

I have a problem with Concurrency control.

The situation.
- A Datadriven form. I pass a name for the table to show and a PK value.
This is how the databinding is done:

LOCAL oTextbox AS System.Windows.Forms.TextBox
oTextbox := System.Windows.Forms.TextBox{}
oTextbox:Size := System.Drawing.Size{ wEditWidth,
wHeight+wEditHeight}
oTextbox:Location := System.Drawing.Point{ wLeft+wTextWidth+wVerGap,
wPixelHeight+7 }
oTextbox:Name := cName
oTextboxataBindings:Add(Binding{"Text", dt, cName } )
SELF:Controls:Add( oTextbox )


This is how the datastuff gets initialized:
oCommand:CommandText := cSqlUpdate
SELFaUpdate := SqlDataAdapter{}
SELFaUpdate:SelectCommand := oCommand
SELFtUpdate := DataTable{}
SELFaUpdate:Fill( SELF:dtUpdate )
SELFBindingSource := BindingSource{}
SELFBindingSourceataSource := SELF:dtUpdate


When the user has edited data in the form and presses the OK/Save button,
the UpdateDatabase method is called.
This works all fine!!

Now I have added code to handle situations where someone else has changed
the same record
It is based upon a sample here:
http://msdn.microsoft.com/en-us/library/ms171936.aspx
So when a dbConcurrency execption is raized, I like to show the current
row
on the database (the content changed by the oher user) ,
the content of the row when de user strated editing en the changed
ccontent.
Now here is where it goes wrong . When the user selects to have the
database
updated with his changes (pressed yes) The call to UpdateDatabases()
still leads to a
DBConcurrencyException.

So my question is, what to do to force the changes writtten to the
database?
The code in the ProcessResponce method is the same as used in several
samples I have found on the MS website, so that cannot be wrong <g>. So
my guess is it has somethnig to do with the (way I use) databinding.

Any suggestions??

tia,

Erik


VIRTUAL Method UpdateDatabase() AS System.Boolean
LOCAL lReturn AS LOGIC
TRY
lReturn := FALSE
SELF:Validate()
SELFBindingSource:EndEdit()
SqlCommandBuilder{ SELF:daUpdate }
SELF:daUpdate:Update( SELF:dtUpdate )
SELF:dtUpdate:AcceptChanges()
lReturn := TRUE
CATCH ex AS System.Data.DBConcurrencyException
LOCAL drResponse AS DialogResult
LOCAL oRow AS DataRow
oRow := (DataRow) ex:Row
drResponse := MessageBox.Show( SELF:CreateMessage( oRow ),
"Multi-user probleem",MessageBoxButtons.YesNo )
SELFrocessResonse( drResponse )
CATCH ex AS System.Exception
MessageBox.Show( ex:Message )
FINALLY
//clean up
END TRY
RETURN lReturn

VIRTUAL METHOD CreateMessage( oRow AS DataRow ) AS STRING
RETURN "Database: " + SELF:GetRowData(
SELF:GetCurrentRowInDB(oRow),
DataRowVersion.Default) +CRLF +;
"Origineel: " + SELF:GetRowData(oRow,
DataRowVersion.Original)
+ CRLF +;
"Voorgesteld: " + SELF:GetRowData(oRow, DataRowVersion.Current)
+ CRLF +;
"Wilt u nog steeds de Database updaten met de voorgestelde
waarde(n)?"

VIRTUAL METHOD GetRowData( oRow AS DataRow , RowVersion AS
DataRowVersion )
AS String
LOCAL rowData AS STRING
LOCAL i AS System.Int32
rowData :=""
FOR i := 0 UPTO oRow:ItemArray:Length-1
RowData := RowData + oRow:Item[i, RowVersion]:ToString() + " "
NEXT
RETURN rowData

VIRTUAL METHOD GetCurrentRowInDB( oErrorRow AS DataRow ) AS DataRow
LOCAL drCurrentRow AS DataRow
SELFTempTable := DataTable{}
SELFAUpdate:Fill( SELFTempTable )
drCurrentRow := oTempTable:Select("ID="+ oErrorRow:Item[0]:ToString())[1]
RETURN drCurrentRow


VIRTUAL METHOD ProcessResonse( dr AS DialogResult )AS VOID
IF dr=Windows.Forms.DialogResult.Yes
SELF:dtUpdate:Merge( SELFTempTable, TRUE )
SELF:UpdateDatabase()
ELSEIF dr=Windows.Forms.DialogResult.No
SELFTUpdate:Merge( SELFTempTable )
ENDIF
RETURN

 

[Cor Ligthert[MVP]]

Erik,

As I assume that it is about SQL server, then there is no default
mechanisme.

All is done by transact SQL where can be written, to check the current time
stamp first with the one in your row, to check all items (ore some
significant ones) to the previous one.

The sample you showed is in my idea based on the latter one, as that is the
default in AdoNet.

Cor

Hi Cor,

The concurrency is based upon the internal mechanism of the dataserver
In case someone else changed the row in the meantime, this row in
UpdateServer throws an DBConcurrencyException.

SELF:daUpdate:Update( SELF:dtUpdate )

If this is the case and the users wants his changes to be written to the
database,
this code is executed:

VIRTUAL METHOD ProcessResonse( dr AS DialogResult )AS VOID
IF dr=Windows.Forms.DialogResult.Yes
SELF:dtUpdate:Merge( SELFTempTable, TRUE )
SELF:UpdateDatabase()

This should work according to the walkthrough about handling concurrency
on
http://msdn.microsoft.com/en-us/library/ms171936.aspx

But, unfortunatally, it does not.

Now the MS sample does not have databinding to controls, so I guess this
asks for on or two extra methodcalls on the Bindingsource, the dataadapter
and/or the datatable. But I have tried everythnig I could think of,
without luck.

Any suggestions?
(groeten uit Groningen)

tia.

Erik

Erik,

Is you concurrency based on checking a timestamp or in checking all
changed columns.

As I saw it quick in the given sample that is based on columnchange
checking.

Cor

I have a problem with Concurrency control.

The situation.
- A Datadriven form. I pass a name for the table to show and a PK value.
This is how the databinding is done:

LOCAL oTextbox AS System.Windows.Forms.TextBox
oTextbox := System.Windows.Forms.TextBox{}
oTextbox:Size := System.Drawing.Size{ wEditWidth,
wHeight+wEditHeight}
oTextbox:Location := System.Drawing.Point{
wLeft+wTextWidth+wVerGap,
wPixelHeight+7 }
oTextbox:Name := cName
oTextboxataBindings:Add(Binding{"Text", dt, cName } )
SELF:Controls:Add( oTextbox )


This is how the datastuff gets initialized:
oCommand:CommandText := cSqlUpdate
SELFaUpdate := SqlDataAdapter{}
SELFaUpdate:SelectCommand := oCommand
SELFtUpdate := DataTable{}
SELFaUpdate:Fill( SELF:dtUpdate )
SELFBindingSource := BindingSource{}
SELFBindingSourceataSource := SELF:dtUpdate


When the user has edited data in the form and presses the OK/Save
button,
the UpdateDatabase method is called.
This works all fine!!

Now I have added code to handle situations where someone else has
changed
the same record
It is based upon a sample here:
http://msdn.microsoft.com/en-us/library/ms171936.aspx
So when a dbConcurrency execption is raized, I like to show the current
row
on the database (the content changed by the oher user) ,
the content of the row when de user strated editing en the changed
ccontent.
Now here is where it goes wrong . When the user selects to have the
database
updated with his changes (pressed yes) The call to UpdateDatabases()
still leads to a
DBConcurrencyException.

So my question is, what to do to force the changes writtten to the
database?
The code in the ProcessResponce method is the same as used in several
samples I have found on the MS website, so that cannot be wrong <g>. So
my guess is it has somethnig to do with the (way I use) databinding.

Any suggestions??

tia,

Erik


VIRTUAL Method UpdateDatabase() AS System.Boolean
LOCAL lReturn AS LOGIC
TRY
lReturn := FALSE
SELF:Validate()
SELFBindingSource:EndEdit()
SqlCommandBuilder{ SELF:daUpdate }
SELF:daUpdate:Update( SELF:dtUpdate )
SELF:dtUpdate:AcceptChanges()
lReturn := TRUE
CATCH ex AS System.Data.DBConcurrencyException
LOCAL drResponse AS DialogResult
LOCAL oRow AS DataRow
oRow := (DataRow) ex:Row
drResponse := MessageBox.Show( SELF:CreateMessage( oRow ),
"Multi-user probleem",MessageBoxButtons.YesNo )
SELFrocessResonse( drResponse )
CATCH ex AS System.Exception
MessageBox.Show( ex:Message )
FINALLY
//clean up
END TRY
RETURN lReturn

VIRTUAL METHOD CreateMessage( oRow AS DataRow ) AS STRING
RETURN "Database: " + SELF:GetRowData(
SELF:GetCurrentRowInDB(oRow),
DataRowVersion.Default) +CRLF +;
"Origineel: " + SELF:GetRowData(oRow,
DataRowVersion.Original)
+ CRLF +;
"Voorgesteld: " + SELF:GetRowData(oRow,
DataRowVersion.Current)
+ CRLF +;
"Wilt u nog steeds de Database updaten met de voorgestelde
waarde(n)?"

VIRTUAL METHOD GetRowData( oRow AS DataRow , RowVersion AS
DataRowVersion )
AS String
LOCAL rowData AS STRING
LOCAL i AS System.Int32
rowData :=""
FOR i := 0 UPTO oRow:ItemArray:Length-1
RowData := RowData + oRow:Item[i, RowVersion]:ToString() + " "
NEXT
RETURN rowData

VIRTUAL METHOD GetCurrentRowInDB( oErrorRow AS DataRow ) AS DataRow
LOCAL drCurrentRow AS DataRow
SELFTempTable := DataTable{}
SELFAUpdate:Fill( SELFTempTable )
drCurrentRow := oTempTable:Select("ID="+
oErrorRow:Item[0]:ToString())[1]
RETURN drCurrentRow


VIRTUAL METHOD ProcessResonse( dr AS DialogResult )AS VOID
IF dr=Windows.Forms.DialogResult.Yes
SELF:dtUpdate:Merge( SELFTempTable, TRUE )
SELF:UpdateDatabase()
ELSEIF dr=Windows.Forms.DialogResult.No
SELFTUpdate:Merge( SELFTempTable )
ENDIF
RETURN

 

[E®!k \\/!sser]

Cor,

As I assume that it is about SQL server, then there is no default
mechanism.

You assume wrong. It has nothing to do with the database backend.

All is done by transact SQL where can be written, to check the current
time stamp first with the one in your row, to check all items (ore some
significant ones) to the previous one.

What is done by transact SQL? I do not use it here.

The sample you showed is in my idea based on the latter one, as that is
the default in AdoNet.

Again, it has nothing to checking timestamps. I do not care and do not have
to care ( if that is what you mean) what mechanism is used internally by the
framework.
The problem is, using the .NET databinding of controls, you can/must write
changes to the database using code like this:
Calling update on the dataadapter commits the changes to the database.

SELF:Validate()
SELFBindingSource:EndEdit()
SqlCommandBuilder{ SELF:daUpdate }
SELF:daUpdate:Update( SELF:dtUpdate )
SELF:dtUpdate:AcceptChanges()

In C# it would look like this:

this.Validate();
this.oBindingSource.EndEdit();
new SqlCommandBuilder(this.DaUpdate);
this.DaUpdate.Update(this.DtUpdate);
this.DtUpdate.AcceptChanges();

Now when somewhere else the same record has been changed, this throws an
exception. This DBConcurrencyException has a property for the offening row.
A temporary datatable is instantiated and the DaUpdate dataTable is merged
with this temporary table. See my code in the first posting.
This is documented by MS as the way you can force / should force to write
your changes to the database.
Unfortunattaly, calling the update method for the dataAdapter leads in my
situation to a new DBConcurrencyException.
So my question is, what do I miss.
Why does the the code of the "Walkthrough: Handling a Concurrency Exception"
in the chapter Concurrency Control in Ado.NET
of the MSDN VSA2008 Developer Center not work in my scenario?


Erik

Cor

Hi Cor,

The concurrency is based upon the internal mechanism of the dataserver
In case someone else changed the row in the meantime, this row in
UpdateServer throws an DBConcurrencyException.

SELF:daUpdate:Update( SELF:dtUpdate )

If this is the case and the users wants his changes to be written to the
database,
this code is executed:

VIRTUAL METHOD ProcessResonse( dr AS DialogResult )AS VOID
IF dr=Windows.Forms.DialogResult.Yes
SELF:dtUpdate:Merge( SELFTempTable, TRUE )
SELF:UpdateDatabase()

This should work according to the walkthrough about handling concurrency
on
http://msdn.microsoft.com/en-us/library/ms171936.aspx

But, unfortunatally, it does not.

Now the MS sample does not have databinding to controls, so I guess this
asks for on or two extra methodcalls on the Bindingsource, the
dataadapter and/or the datatable. But I have tried everythnig I could
think of, without luck.

Any suggestions?
(groeten uit Groningen)

tia.

Erik

Erik,

Is you concurrency based on checking a timestamp or in checking all
changed columns.

As I saw it quick in the given sample that is based on columnchange
checking.

Cor

"E®!k \/!sser" <[email protected]> schreef in bericht
I have a problem with Concurrency control.

The situation.
- A Datadriven form. I pass a name for the table to show and a PK
value.
This is how the databinding is done:

LOCAL oTextbox AS System.Windows.Forms.TextBox
oTextbox := System.Windows.Forms.TextBox{}
oTextbox:Size := System.Drawing.Size{ wEditWidth,
wHeight+wEditHeight}
oTextbox:Location := System.Drawing.Point{
wLeft+wTextWidth+wVerGap,
wPixelHeight+7 }
oTextbox:Name := cName
oTextboxataBindings:Add(Binding{"Text", dt, cName } )
SELF:Controls:Add( oTextbox )


This is how the datastuff gets initialized:
oCommand:CommandText := cSqlUpdate
SELFaUpdate := SqlDataAdapter{}
SELFaUpdate:SelectCommand := oCommand
SELFtUpdate := DataTable{}
SELFaUpdate:Fill( SELF:dtUpdate )
SELFBindingSource := BindingSource{}
SELFBindingSourceataSource := SELF:dtUpdate


When the user has edited data in the form and presses the OK/Save
button,
the UpdateDatabase method is called.
This works all fine!!

Now I have added code to handle situations where someone else has
changed
the same record
It is based upon a sample here:
http://msdn.microsoft.com/en-us/library/ms171936.aspx
So when a dbConcurrency execption is raized, I like to show the current
row
on the database (the content changed by the oher user) ,
the content of the row when de user strated editing en the changed
ccontent.
Now here is where it goes wrong . When the user selects to have the
database
updated with his changes (pressed yes) The call to UpdateDatabases()
still leads to a
DBConcurrencyException.

So my question is, what to do to force the changes writtten to the
database?
The code in the ProcessResponce method is the same as used in several
samples I have found on the MS website, so that cannot be wrong <g>. So
my guess is it has somethnig to do with the (way I use) databinding.

Any suggestions??

tia,

Erik


VIRTUAL Method UpdateDatabase() AS System.Boolean
LOCAL lReturn AS LOGIC
TRY
lReturn := FALSE
SELF:Validate()
SELFBindingSource:EndEdit()
SqlCommandBuilder{ SELF:daUpdate }
SELF:daUpdate:Update( SELF:dtUpdate )
SELF:dtUpdate:AcceptChanges()
lReturn := TRUE
CATCH ex AS System.Data.DBConcurrencyException
LOCAL drResponse AS DialogResult
LOCAL oRow AS DataRow
oRow := (DataRow) ex:Row
drResponse := MessageBox.Show( SELF:CreateMessage( oRow ),
"Multi-user probleem",MessageBoxButtons.YesNo )
SELFrocessResonse( drResponse )
CATCH ex AS System.Exception
MessageBox.Show( ex:Message )
FINALLY
//clean up
END TRY
RETURN lReturn

VIRTUAL METHOD CreateMessage( oRow AS DataRow ) AS STRING
RETURN "Database: " + SELF:GetRowData(
SELF:GetCurrentRowInDB(oRow),
DataRowVersion.Default) +CRLF +;
"Origineel: " + SELF:GetRowData(oRow,
DataRowVersion.Original)
+ CRLF +;
"Voorgesteld: " + SELF:GetRowData(oRow,
DataRowVersion.Current)
+ CRLF +;
"Wilt u nog steeds de Database updaten met de voorgestelde
waarde(n)?"

VIRTUAL METHOD GetRowData( oRow AS DataRow , RowVersion AS
DataRowVersion )
AS String
LOCAL rowData AS STRING
LOCAL i AS System.Int32
rowData :=""
FOR i := 0 UPTO oRow:ItemArray:Length-1
RowData := RowData + oRow:Item[i, RowVersion]:ToString() + " "
NEXT
RETURN rowData

VIRTUAL METHOD GetCurrentRowInDB( oErrorRow AS DataRow ) AS DataRow
LOCAL drCurrentRow AS DataRow
SELFTempTable := DataTable{}
SELFAUpdate:Fill( SELFTempTable )
drCurrentRow := oTempTable:Select("ID="+
oErrorRow:Item[0]:ToString())[1]
RETURN drCurrentRow


VIRTUAL METHOD ProcessResonse( dr AS DialogResult )AS VOID
IF dr=Windows.Forms.DialogResult.Yes
SELF:dtUpdate:Merge( SELFTempTable, TRUE )
SELF:UpdateDatabase()
ELSEIF dr=Windows.Forms.DialogResult.No
SELFTUpdate:Merge( SELFTempTable )
ENDIF
RETURN

 

[Cor Ligthert[MVP]]

Erik,

I miss a lot in what you write and the sample you are refering to.

By instance from where did you create that AcceptChanges, the data is wrong
so it has not to be set as done.

But moreover, I don't see any part where you retrieve the current situation
after that the exception was throwed.

(Mostly is that done by Fill, although some get the actual row with a
datareader)

Cor

Hi Cor,

The concurrency is based upon the internal mechanism of the dataserver
In case someone else changed the row in the meantime, this row in
UpdateServer throws an DBConcurrencyException.

SELF:daUpdate:Update( SELF:dtUpdate )

If this is the case and the users wants his changes to be written to the
database,
this code is executed:

VIRTUAL METHOD ProcessResonse( dr AS DialogResult )AS VOID
IF dr=Windows.Forms.DialogResult.Yes
SELF:dtUpdate:Merge( SELFTempTable, TRUE )
SELF:UpdateDatabase()

This should work according to the walkthrough about handling concurrency
on
http://msdn.microsoft.com/en-us/library/ms171936.aspx

But, unfortunatally, it does not.

Now the MS sample does not have databinding to controls, so I guess this
asks for on or two extra methodcalls on the Bindingsource, the dataadapter
and/or the datatable. But I have tried everythnig I could think of,
without luck.

Any suggestions?
(groeten uit Groningen)

tia.

Erik

Erik,

Is you concurrency based on checking a timestamp or in checking all
changed columns.

As I saw it quick in the given sample that is based on columnchange
checking.

Cor

I have a problem with Concurrency control.

The situation.
- A Datadriven form. I pass a name for the table to show and a PK value.
This is how the databinding is done:

LOCAL oTextbox AS System.Windows.Forms.TextBox
oTextbox := System.Windows.Forms.TextBox{}
oTextbox:Size := System.Drawing.Size{ wEditWidth,
wHeight+wEditHeight}
oTextbox:Location := System.Drawing.Point{
wLeft+wTextWidth+wVerGap,
wPixelHeight+7 }
oTextbox:Name := cName
oTextboxataBindings:Add(Binding{"Text", dt, cName } )
SELF:Controls:Add( oTextbox )


This is how the datastuff gets initialized:
oCommand:CommandText := cSqlUpdate
SELFaUpdate := SqlDataAdapter{}
SELFaUpdate:SelectCommand := oCommand
SELFtUpdate := DataTable{}
SELFaUpdate:Fill( SELF:dtUpdate )
SELFBindingSource := BindingSource{}
SELFBindingSourceataSource := SELF:dtUpdate


When the user has edited data in the form and presses the OK/Save
button,
the UpdateDatabase method is called.
This works all fine!!

Now I have added code to handle situations where someone else has
changed
the same record
It is based upon a sample here:
http://msdn.microsoft.com/en-us/library/ms171936.aspx
So when a dbConcurrency execption is raized, I like to show the current
row
on the database (the content changed by the oher user) ,
the content of the row when de user strated editing en the changed
ccontent.
Now here is where it goes wrong . When the user selects to have the
database
updated with his changes (pressed yes) The call to UpdateDatabases()
still leads to a
DBConcurrencyException.

So my question is, what to do to force the changes writtten to the
database?
The code in the ProcessResponce method is the same as used in several
samples I have found on the MS website, so that cannot be wrong <g>. So
my guess is it has somethnig to do with the (way I use) databinding.

Any suggestions??

tia,

Erik


VIRTUAL Method UpdateDatabase() AS System.Boolean
LOCAL lReturn AS LOGIC
TRY
lReturn := FALSE
SELF:Validate()
SELFBindingSource:EndEdit()
SqlCommandBuilder{ SELF:daUpdate }
SELF:daUpdate:Update( SELF:dtUpdate )
SELF:dtUpdate:AcceptChanges()
lReturn := TRUE
CATCH ex AS System.Data.DBConcurrencyException
LOCAL drResponse AS DialogResult
LOCAL oRow AS DataRow
oRow := (DataRow) ex:Row
drResponse := MessageBox.Show( SELF:CreateMessage( oRow ),
"Multi-user probleem",MessageBoxButtons.YesNo )
SELFrocessResonse( drResponse )
CATCH ex AS System.Exception
MessageBox.Show( ex:Message )
FINALLY
//clean up
END TRY
RETURN lReturn

VIRTUAL METHOD CreateMessage( oRow AS DataRow ) AS STRING
RETURN "Database: " + SELF:GetRowData(
SELF:GetCurrentRowInDB(oRow),
DataRowVersion.Default) +CRLF +;
"Origineel: " + SELF:GetRowData(oRow,
DataRowVersion.Original)
+ CRLF +;
"Voorgesteld: " + SELF:GetRowData(oRow,
DataRowVersion.Current)
+ CRLF +;
"Wilt u nog steeds de Database updaten met de voorgestelde
waarde(n)?"

VIRTUAL METHOD GetRowData( oRow AS DataRow , RowVersion AS
DataRowVersion )
AS String
LOCAL rowData AS STRING
LOCAL i AS System.Int32
rowData :=""
FOR i := 0 UPTO oRow:ItemArray:Length-1
RowData := RowData + oRow:Item[i, RowVersion]:ToString() + " "
NEXT
RETURN rowData

VIRTUAL METHOD GetCurrentRowInDB( oErrorRow AS DataRow ) AS DataRow
LOCAL drCurrentRow AS DataRow
SELFTempTable := DataTable{}
SELFAUpdate:Fill( SELFTempTable )
drCurrentRow := oTempTable:Select("ID="+
oErrorRow:Item[0]:ToString())[1]
RETURN drCurrentRow


VIRTUAL METHOD ProcessResonse( dr AS DialogResult )AS VOID
IF dr=Windows.Forms.DialogResult.Yes
SELF:dtUpdate:Merge( SELFTempTable, TRUE )
SELF:UpdateDatabase()
ELSEIF dr=Windows.Forms.DialogResult.No
SELFTUpdate:Merge( SELFTempTable )
ENDIF
RETURN

 

[E®!k \\/!sser]

Cor,

I miss a lot in what you write and the sample you are refering to.

By instance from where did you create that AcceptChanges, the data is
wrong so it has not to be set as done.

The user presses an OK button and the eventhandler bound to that butten
calls UpdateDatabase.

VIRTUAL Method UpdateDatabase() AS System.Boolean
LOCAL lReturn AS LOGIC
TRY
lReturn := FALSE
SELF:Validate()
SELFBindingSource:EndEdit()
SqlCommandBuilder{ SELF:daUpdate }
SELF:daUpdate:Update( SELF:dtUpdate )
SELF:dtUpdate:AcceptChanges()
lReturn := TRUE
CATCH ex AS System.Data.DBConcurrencyException
LOCAL drResponse AS DialogResult
LOCAL oRow AS DataRow
oRow := (DataRow) ex:Row
drResponse := MessageBox.Show( SELF:CreateMessage( oRow ),
"Multi-user probleem",MessageBoxButtons.YesNo )
SELFrocessResonse( drResponse )
CATCH ex AS System.Exception
MessageBox.Show( ex:Message )
FINALLY
//clean up
END TRY
RETURN lReturn

On the (SqlDataAdapter ) SELF:daUpdate the Update method is called. In case
of a concurrency problem, here the dbConcurrencyException is raized, so the
call to (DataTable) SELF:dtUpdate:AcceptChanges() is never reached.

But moreover, I don't see any part where you retrieve the current
situation after that the exception was throwed.

(Mostly is that done by Fill, although some get the actual row with a
datareader)

The CreateMessage method builds a string of three rows, 1) the data from the
database, 2) the data before the user started to edit and 3) the data
modified by the user.
For 1) there is a call to a GetCureentRowInDB method. That method
instantiates a new datatable oTempTable and filled with the data from the
database.


VIRTUAL METHOD CreateMessage( oRow AS DataRow ) AS STRING
RETURN "Database: " + SELF:GetRowData( SELF:GetCurrentRowInDB(oRow),
DataRowVersion.Default) +CRLF +;
"Origineel: " + SELF:GetRowData(oRow, DataRowVersion.Original)
+ CRLF +;
"Voorgesteld: " + SELF:GetRowData(oRow, DataRowVersion.Current)
+ CRLF +;
"Wilt u nog steeds de Database updaten met de voorgestelde
waarde(n)?"

VIRTUAL METHOD GetRowData( oRow AS DataRow , RowVersion AS DataRowVersion )
AS String
LOCAL rowData AS STRING
LOCAL i AS System.Int32
rowData :=""
FOR i := 0 UPTO oRow:ItemArray:Length-1
RowData := RowData + oRow:Item[i, RowVersion]:ToString() + " "
NEXT
RETURN rowData

VIRTUAL METHOD GetCurrentRowInDB( oErrorRow AS DataRow ) AS DataRow
LOCAL drCurrentRow AS DataRow
SELFTempTable := DataTable{}
SELFAUpdate:Fill( SELFTempTable )

drCurrentRow := oTempTable:Select("ID="+ oErrorRow:Item[0]:ToString())[1]
RETURN drCurrentRow

This is all the same according to the sample on:
http://msdn.microsoft.com/en-us/library/ms171936.aspx
This sample is pretty well documented and shows the code in both VB and C#

Now when the user wants his/her changes commit to the database anyhow, this
code is executed:

SELF:dtUpdate:Merge( SELFTempTable, TRUE , MissingSchemaAction.Ignore )
SELF:UpdateDatabase()

Now the problem is that the merge method does not work like it is
documented.
The result of this call is, in my situation, that dtUpdate now has two rows
in stead of one.
The 'recursive' call to UpdateDatabase() leads now to another
dbConcurrencyException.

Erik