DataSet concurrency exception (code inside)

[Sébastien]

I get a concurrency exception with the following code. Until a ghost is
updating my database while I edit my row, I am not suppose to get that
exception.

Public Sub SetFormEditAccept(ByVal frmForm As Form, ByVal bndBindingContext
As BindingContext, ByVal dtsDataSet As DataSet, ByVal strTable As String,
ByVal strConnection As String)
Dim bndBindingManagerBase As BindingManagerBase
Dim dtrDataRow As DataRow
Dim dtrDataRowView As DataRowView
Dim dttDataTable As DataTable
Dim ntgCounter As Integer
Dim oldOleDbDataAdapter As OleDb.OleDbDataAdapter

'Check if we're adding a row in a table
If blnIsEdit = True Then
dtrDataRow = dtsDataSet.Tables(strTable).NewRow

'Here we set row values (Database.Field1 = Form.TextBox1, etc...)
SetEditAccept(frmForm, dtrDataRow, strTable)

dtsDataSet.Tables(strTable).Rows.Add(dtrDataRow)
'Else, we are adding a new row
Else
bndBindingManagerBase =
bndBindingContext(dtsDataSet.DefaultViewManager, strTable)
dtrDataRowView = CType(bndBindingManagerBase.Current, DataRowView)
dtrDataRow = dtrDataRowView.Row

'Here we set row values (Database.Field1 = Form.TextBox1, etc...)
'We call that function only because there is some tables with
composed primary key
'If we just have one field as primary key in each table we update,
it is useless to call this function
SetEditAccept(frmForm, dtrDataRow, strTable)
End If

'Curious to see what it does ? The function is in this post =)
oldOleDbDataAdapter = GetDataAdapter(strTable, strConnection)

'Just one line to commit changes ? Easy =)
oldOleDbDataAdapter.Update(dtsDataSet, strTable)
End Sub

Private Function GetDataAdapter(ByVal strTable As String, ByVal
strConnection As String) As OleDb.OleDbDataAdapter
Dim oldOleDbCommandBuilder As OleDb.OleDbCommandBuilder
Dim oldOleDbDataAdapter As OleDb.OleDbDataAdapter

oldOleDbDataAdapter = New OleDb.OleDbDataAdapter("SELECT * FROM " &
strTable, strConnection)

oldOleDbCommandBuilder = New
OleDb.OleDbCommandBuilder(oldOleDbDataAdapter)

'You'll say, what are you doing, you don't need to assign that.
'I'll answer, I know, but it is the only way I found to see what was my
insert, update and delete command in debug.
oldOleDbDataAdapter.InsertCommand =
oldOleDbCommandBuilder.GetInsertCommand
oldOleDbDataAdapter.UpdateCommand =
oldOleDbCommandBuilder.GetUpdateCommand
oldOleDbDataAdapter.DeleteCommand =
oldOleDbCommandBuilder.GetDeleteCommand

Return oldOleDbDataAdapter
End Function


Hey, hey, wait. I have to explain my problem now.
That code work perfectly when I add my first row in a table or I update a
table that already contain a row. But if I add a new row in an empty table,
then I update the row, then uh oh, Concurrency exception. But why is it
working with table that already had rows before I update them ? Wow, same
problem with delete, but no problem with inserting more and more rows.

Ohhh, I did not test it long enough to be sure of what I'll say, but (hear
at my secret !) it seems I don't have ANY concurrency exception with tables
having no AutoIncrement field as primary key (OOOOoohhhhhhhhhh, strange
isn't it ?).

So, seriously, I wish someone can help me to figure out what I am doing
wrong.

Many thanks to the one who put me on the good way.
Sincerely, Sébastien

 

[David Sceppa]

Sébastien,

Submitting the new row succeeds, but if you're not
retrieving server-generated values (auto-increment, timestamp,
default, etc.) for the row, subsequent updates to the row will
fail as you've described.

You're using CommandBuilders to generate updating logic at
run-time. While this really simplifies your code, this approach
is generally discouraged for a couple reasons.

1.) CommandBuilders have to query your database for the schema
information (table name, column names, key information, etc.)
necessary to generate the updating logic. This is a non-trivial
performance hit and isn't necessary if you know your database
schema when building your application.

2.) The updating logic created by CommandBuilders is very
limited. They do not include logic to retrieve new
auto-increment values after insert, and they do not let you fine
tune your concurrency checks.

The Visual Studio.NET DataAdapter Configuration Wizard could
create this updating logic at design-time for you, giving you
better performance at run-time, and including logic to retrieve
the new auto-increment values after insert.

Here's a link to the help topic in the SDK that shows how to
write your own code to accomplish this, using the SqlClient and
OleDb .NET Data Providers. (mind the word-wrap)

ms-help://MS.VSCC/MS.MSDNVS/cpguide/html/cpconretrievingidentityor
autonumbervalues.htm

I hope this information proves helpful.

David Sceppa
Microsoft
This posting is provided "AS IS" with no warranties,
and confers no rights. You assume all risk for your use.
© 2004 Microsoft Corporation. All rights reserved.

 

[Sébastien]

Why isn't it impossible to create the DataAdapter Configuration Wizard logic
in my code ? I did not know that coding was less powerfull than wizards. If
the wizard can do it, I guess there is a way to do it into my code also ? Am
I right ?

Thank you.

 

[David Sceppa]

Sébastien,

Coding is not less powerful than wizards. The link I
provided to the topic in the SDK should help you write the code
you need:

ms-help://MS.VSCC/MS.MSDNVS/cpguide/html/cpconretrievingidentityor
autonumbervalues.htm

I hope this information proves helpful.

David Sceppa
Microsoft
This posting is provided "AS IS" with no warranties,
and confers no rights. You assume all risk for your use.
© 2004 Microsoft Corporation. All rights reserved.

 

[Sébastien]

If I could open that link, it would be nice. I have MSDN 2003. Maybe that
link don't exist in my MSDN version.

Don't you have the same link on MSDN online on the Microsoft web site ?

Thank you.

 

[Sébastien]

I can answer to myself :

http://msdn.microsoft.com/library/d...cpconRetrievingIdentityOrAutonumberValues.asp

Thank you my myself =p

 

[David Sceppa]

Excellent.

David Sceppa
Microsoft
This posting is provided "AS IS" with no warranties,
and confers no rights. You assume all risk for your use.
© 2004 Microsoft Corporation. All rights reserved.