none
Example of code that produces a concurrency error updating MS Access data row using ADO.Net Data Adapter in Visual Web Developer 2008. RRS feed

  • Question

  • I am using Visual Web Developer 2008 and a MS Access 2007 Database in a VB.Net Web Form application to retrieve and updata member information.
    The data is being accessed using ADO.Net Oledb and the Data Adapter.  The application takes a user name and PWD, reads the database, and instantiates a "Member" object which is stored in a session variable.  The data is then displayed.  When the Update button is clicked, the memberUpdate form retrieves the "Member" object from the session variable and updates the data row, using the Update method of the Data Adapter.

    It then get's the concurrnecy error:

    Concurrency violation: the UpdateCommand affected 0 of the expected 1 records.

        Private Sub getMember()
            Dim pdName As String
            Dim Param As OleDbParameter
            Dim X As Integer
            _Conn = Nothing
    
            Try
                _objConnection = New PSAAMembershipConnection
                _Conn = _objConnection.ConnectToPSAA
                pdName = "pd_get_member_information"
                _cmd = New OleDbCommand()
                _cmd.CommandText = pdName
                _cmd.CommandType = CommandType.StoredProcedure
                Param = New OleDbParameter
                Param.ParameterName = "@MemberNumber"
                Param.Value = _ID
                _cmd.Parameters.Add(Param)
                _cmd.Connection = _Conn
                _DA = New OleDbDataAdapter
                _DA.SelectCommand = _cmd
                _dsMemberInformation = New DataSet
                X = _DA.Fill(_dsMemberInformation)
                _dtMemberInformation = _dsMemberInformation.Tables(0)
                If Not _dtMemberInformation Is Nothing Then
                    If _dtMemberInformation.Rows.Count > 0 Then
                        _drMemberInformation = _dtMemberInformation.Rows(0)
                    End If
                End If
    
            Catch ex As Exception
                Throw ex
            Finally
            End Try
        End Sub   
    
    
     Public Sub saveMember()
            Try
                Dim pdDelete As String
                Dim pdInsert As String
                Dim pdUpdate As String
                pdDelete = "pd_delete_member"
                pdInsert = "pd_insert_member"
                pdUpdate = "pd_update_member"
                SaveData(_dtMemberInformation, pdDelete, pdUpdate, pdInsert)
            Catch ex As Exception
                Throw ex
            End Try
        End Sub
    
        Public Function SaveData(ByVal TableName As DataTable, ByVal DeleteProcName As String, ByVal UpdateProcName As String, ByVal InsertProcName As String, Optional ByVal UsingTransaction As Boolean = False) As Integer
            Dim ds As DataSet
            Dim ID As Integer = -1
            Try
                ds = TableName.DataSet
                If ds.HasChanges(DataRowState.Deleted) Then
                    DeleteData(TableName, DeleteProcName)
                End If
                If ds.HasChanges(DataRowState.Modified) Then
                    UpdateData(TableName, UpdateProcName)
                End If
                If ds.HasChanges(DataRowState.Added) Then
                    ID = InsertData(TableName, InsertProcName)
                End If
                Return ID
            Catch ex As Exception
                Throw ex
            End Try
        End Function
    
        Public Sub UpdateData(ByVal TableName As DataTable, ByVal pdName As String)
            Try
                _cmd = GetPdParameters(TableName)
                _cmd.CommandType = CommandType.StoredProcedure
                _cmd.CommandText = pdName
                _DA.UpdateCommand = _cmd
                _DA.UpdateCommand.Connection = _Conn
                _DA.Update(TableName.Select(Nothing, Nothing, DataViewRowState.ModifiedCurrent))
            Catch Ex As Exception
                Throw Ex
            End Try
        End Sub
    
        Public Function GetPdParameters(ByVal TableName As DataTable) As OleDbCommand
            Dim cmd As New OleDbCommand
            Dim param As OleDbParameter
            Dim Col As DataColumn
            Dim r As DataRow
            r = TableName.Rows(0)
            For Each Col In TableName.Columns
                param = New OleDbParameter
                param.ParameterName = "@" & Col.ColumnName
                param.SourceColumn = Col.ColumnName
                cmd.Parameters.Add(param)
            Next
            Return cmd
        End Function
    
    


    Windows Forms Developer
    Wednesday, December 16, 2009 12:17 PM

Answers

  • Hello Daniel,

     

    Thank you for sending me the demo project.  I do repro the problem at my side.  It’s a really tough issue.  L  I researched the problem for half a day and tried many workarounds even modifying the data provider (Office 2007 Data Connectivity Components).   Here are some detailed information:

     

    First, the problem is not only occurred in ASP.NET web applications, because I create a simple WinForm application and I repro it as well. 

     

    Second, it is not related to the data provider, because “Microsoft.ACE.OLEDB.12” still cannot solve the problem.  

     

    The workaround that I found is to use the OleDbCommandBuilder instead of the stored procedure pd_update_member.   The codes that I modified are listed here:  (All are in the Member.vb file)

    ==================================================================================================================
            Private Sub getMember()

            Dim pdName As String

            Dim Param As OleDbParameter

            Dim X As Integer

            _Conn = Nothing

     

            Try

                _objConnection = New PSAAMembershipConnection

                _Conn = _objConnection.ConnectToPSAA

                pdName = "select * from pd_get_member_information"

                _cmd = New OleDbCommand()

                _cmd.CommandText = pdName

                _cmd.CommandType = CommandType.Text

                'Param = New OleDbParameter

                'Param.ParameterName = "@MemberNumber"

                'Param.Value = _ID

                 '_cmd.Parameters.Add(Param)

    ……

     

            Public Sub UpdateData(ByVal TableName As DataTable, ByVal pdName As String)

            Try

                _cmd = GetPdParameters(TableName)

                '_cmd.CommandType = CommandType.StoredProcedure

                '_cmd.CommandText = pdName

                '_cmd.CommandType = CommandType.Text

                Dim builder As New OleDbCommandBuilder(_DA)

                '_DA.UpdateCommand = _cmd

                '_DA.UpdateCommand.Connection = _Conn

                _DA.Update(TableName.Select(Nothing, Nothing, DataViewRowState.ModifiedCurrent))

            Catch Ex As Exception

                Throw Ex

            End Try

    ==================================================================================================================

     

    I am still troubleshooting the root cause of the problem.  First I thought it was because the primary key, however, even I directly query the member_information table and use FillSchema method to get the PK column, the pd_update_member procedure is still not working correctly.  Any founding is that the strongly typed TableAdapter for the member_information table generates such UpdateCommands:

    ==================================================================================================================

    UPDATE       member_information

    SET                last_name = ?, first_name = ?, middle_name = ?, street_address = ?, city = ?, state = ?, zip_code = ?, logon_name = ?, logon_password = ?, Role = ?

    WHERE        (membership_number = ?) AND (? = 1 AND last_name IS NULL OR

                             last_name = ?) AND (? = 1 AND first_name IS NULL OR

                             first_name = ?) AND (? = 1 AND middle_name IS NULL OR

                             middle_name = ?) AND (? = 1 AND street_address IS NULL OR

                             street_address = ?) AND (? = 1 AND city IS NULL OR

                             city = ?) AND (? = 1 AND state IS NULL OR

                             state = ?) AND (? = 1 AND zip_code IS NULL OR

                             zip_code = ?) AND (? = 1 AND logon_name IS NULL OR

                             logon_name = ?) AND (? = 1 AND logon_password IS NULL OR

                             logon_password = ?) AND (? = 1 AND Role IS NULL OR

                             Role = ?)
    ==================================================================================================================

     

    From this command, the problem may be caused by the nullable columns.  I will perform a further investigation to find out the root cause. 

     

    If any other community members are interested in this issue, here is my mail address: v-micsun @ microsoft.com.   I can send you a demo WinForm application.

     

     

    Have a great day, all!

     

     

    Best Regards,
    Lingzhi Sun

    MSDN Subscriber Support in Forum

    If you have any feedback on our support, please contact msdnmg@microsoft.com


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.
    Thursday, December 17, 2009 10:30 AM
    Moderator

All replies

  • Hi,
    I do not know the reason of this error message. However I have some suggestions which can help to avoid further problems.
    Be aware that MS Access database was not designed to beying use in web applications. Refer to:
    http://support.microsoft.com/default.aspx/kb/299973

    I do not see the declaration of some objects like _cmd, _DA.
    Did you define these objects globally?
    Be aware that ado.net objects are not thread safe (maybe it is the reason for this error message). There is no information whether this error occurs just with 1 user.

    For example for OledbDataAdapter:

    http://msdn.microsoft.com/en-us/library/system.data.oledb.oledbdataadapter.aspx
    "Any instance members are not guaranteed to be thread safe."

    Please review also following article:
    http://msdn.microsoft.com/en-us/library/ms998569.aspx

    Best regards

    Ryszard Gawron [MSFT]

    „This posting is provided "AS IS" with no warranties, and confers no rights.”

    Wednesday, December 16, 2009 3:54 PM
  • Could you post the SQL statement in the DataAdapter UpdateCommand?
    Paul ~~~~ Microsoft MVP (Visual Basic)
    Wednesday, December 16, 2009 7:01 PM
  • The variables are defined and this is running in debug mode locally.  I have been using this MS Access DB for several years now, in an ASP environment, using ADO recordsets, to do exactly what I am trying to do here with ADO.Net, and have had no issues to date.

    I am posting the update query here:

    UPDATE member_information
     SET
     member_information.last_name = "@last_name"
    , member_information.first_name = "@first_name"
    , member_information.middle_name = "@middle_name"
    , member_information.street_address = "@street_address"
    , member_information.city = "@city"
    , member_information.state = "@state"
    , member_information.zip_code = "@zip_code"
    , member_information.logon_name = "@logon_name"
    , member_information.logon_password = "@logon_password"
    
    WHERE membership_number = @membership_number;

    Windows Forms Developer
    Wednesday, December 16, 2009 10:13 PM
  • The error is pretty straightforward. The runtime builds an update statement based on what is in the datatable/dataset.
    Since ADO.Net is disconnected it uses optimistic concurrency which means there are no locks in the database. So the created updatestatement
    will do a check on each and every column and the value that it had when the data was retriveved. See more here:

    "Generating Commands with CommandBuilders (ADO.NET)"
    http://msdn.microsoft.com/en-us/library/tf579hcz.aspx

    It then executes the update and if no row matches the WHERE clause in the updatestatement, it will give this error.

    For Access database this could typically happen when using Autonumber in the database. If you then insert a row in a datagrid it will get a
    new value in the database, however, it will still be -1 in the datagrid so when an update is made on that row it will try to update a row
    where the pk is -1. And since there is no such row, you will get the error.

    Hope this makes some sense. Have a look at these documents, the first on describes your problem:

    "Tackle Data Concurrency Exceptions Using the DataSet Object"
    http://msdn.microsoft.com/en-us/magazine/cc188748.aspx
    "Walkthrough: Saving Data from Related Data Tables (Hierarchical Update)"
    http://msdn.microsoft.com/en-us/library/bb384432.aspx
    "Retrieving Identity or Autonumber Values (ADO.NET)"
    http://msdn.microsoft.com/en-us/library/ks9f57t0.aspx
    "HOW TO: Retrieve the Identity Value While Inserting Records into Access Database By Using Visual Basic .NET"
    http://support.microsoft.com/default.aspx?scid=kb;en-us;815629

    //Michael
    This posting is provided "AS IS" with no warranties.
    Thursday, December 17, 2009 8:39 AM
  • Hello Daniel,

     

    Thank you for sending me the demo project.  I do repro the problem at my side.  It’s a really tough issue.  L  I researched the problem for half a day and tried many workarounds even modifying the data provider (Office 2007 Data Connectivity Components).   Here are some detailed information:

     

    First, the problem is not only occurred in ASP.NET web applications, because I create a simple WinForm application and I repro it as well. 

     

    Second, it is not related to the data provider, because “Microsoft.ACE.OLEDB.12” still cannot solve the problem.  

     

    The workaround that I found is to use the OleDbCommandBuilder instead of the stored procedure pd_update_member.   The codes that I modified are listed here:  (All are in the Member.vb file)

    ==================================================================================================================
            Private Sub getMember()

            Dim pdName As String

            Dim Param As OleDbParameter

            Dim X As Integer

            _Conn = Nothing

     

            Try

                _objConnection = New PSAAMembershipConnection

                _Conn = _objConnection.ConnectToPSAA

                pdName = "select * from pd_get_member_information"

                _cmd = New OleDbCommand()

                _cmd.CommandText = pdName

                _cmd.CommandType = CommandType.Text

                'Param = New OleDbParameter

                'Param.ParameterName = "@MemberNumber"

                'Param.Value = _ID

                 '_cmd.Parameters.Add(Param)

    ……

     

            Public Sub UpdateData(ByVal TableName As DataTable, ByVal pdName As String)

            Try

                _cmd = GetPdParameters(TableName)

                '_cmd.CommandType = CommandType.StoredProcedure

                '_cmd.CommandText = pdName

                '_cmd.CommandType = CommandType.Text

                Dim builder As New OleDbCommandBuilder(_DA)

                '_DA.UpdateCommand = _cmd

                '_DA.UpdateCommand.Connection = _Conn

                _DA.Update(TableName.Select(Nothing, Nothing, DataViewRowState.ModifiedCurrent))

            Catch Ex As Exception

                Throw Ex

            End Try

    ==================================================================================================================

     

    I am still troubleshooting the root cause of the problem.  First I thought it was because the primary key, however, even I directly query the member_information table and use FillSchema method to get the PK column, the pd_update_member procedure is still not working correctly.  Any founding is that the strongly typed TableAdapter for the member_information table generates such UpdateCommands:

    ==================================================================================================================

    UPDATE       member_information

    SET                last_name = ?, first_name = ?, middle_name = ?, street_address = ?, city = ?, state = ?, zip_code = ?, logon_name = ?, logon_password = ?, Role = ?

    WHERE        (membership_number = ?) AND (? = 1 AND last_name IS NULL OR

                             last_name = ?) AND (? = 1 AND first_name IS NULL OR

                             first_name = ?) AND (? = 1 AND middle_name IS NULL OR

                             middle_name = ?) AND (? = 1 AND street_address IS NULL OR

                             street_address = ?) AND (? = 1 AND city IS NULL OR

                             city = ?) AND (? = 1 AND state IS NULL OR

                             state = ?) AND (? = 1 AND zip_code IS NULL OR

                             zip_code = ?) AND (? = 1 AND logon_name IS NULL OR

                             logon_name = ?) AND (? = 1 AND logon_password IS NULL OR

                             logon_password = ?) AND (? = 1 AND Role IS NULL OR

                             Role = ?)
    ==================================================================================================================

     

    From this command, the problem may be caused by the nullable columns.  I will perform a further investigation to find out the root cause. 

     

    If any other community members are interested in this issue, here is my mail address: v-micsun @ microsoft.com.   I can send you a demo WinForm application.

     

     

    Have a great day, all!

     

     

    Best Regards,
    Lingzhi Sun

    MSDN Subscriber Support in Forum

    If you have any feedback on our support, please contact msdnmg@microsoft.com


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.
    Thursday, December 17, 2009 10:30 AM
    Moderator
  • Michael,

    Thank you for the CommandBuilder information and finding that it works with that object.  I did try it with a stored proceedure, and indeed, it will only work with a "dynamic" query.  Not sure that I understand what the difference is, but it does update the row.

    For the information of the others with this problem.  I have tried all the other solutions that were suggested, and non of them made any difference, this is the only suggestion that worked.

    Now to find out if it will work with an insert and a delete.
    Windows Forms Developer
    Thursday, December 17, 2009 6:35 PM
  • An Access database doesn't really have true stored procedures - they're actually called QueryDefs. When executing in Access the SQL standard (ANSI 89 Level 1) is different than when executing via ADO/ADO.NET and OLEDB (ANSI SQL-92) - this can cause some syntactical issues. 

    Also note that when using OLEDB in ADO.NET parameters are resolved according to their ordinal position in the Parameters collection and mapped to the corresponding positions in the SQL statement. You can use names for your parameters but the names cannot be used to map to the parameter placeholders in the SQL statement. That's why you will see ? characters as parameter placeholders in many instances.

    I don't know whether any of this caused your particular problem, but it's something to keep in mind.


    Paul ~~~~ Microsoft MVP (Visual Basic)
    Thursday, December 17, 2009 7:04 PM