Another Update Problem

Posted by: pietf on 8 September 2017, 1:26 pm EST

  • Posted 8 September 2017, 1:26 pm EST


    Using: VB2005 / Spread for Winforms 2.5

    In my app I have encountered a problem with updating rows to the datasource.

    When I:

    1. insert a row 
    2. save it
    3. delete that same row
    4. save it

    Then on the last save the app reacts with a "DBConcurrencyException"

    What is going wrong here, is there somebody who can help me?

    Here is the code I use:

    sub LoadData
            Dim conStr As String = "Provider=Microsoft.JET.OLEDB.4.0;data source= " & tmpFile
            Dim sqlStr As String = "SELECT * FROM Data ORDER BY group, paragraph, nr;"
            Dim conn As New System.Data.OleDb.OleDbConnection(conStr)

            Dim ds As New DataSet()
            _da = New OleDbDataAdapter(sqlStr, conn)
            _da.MissingSchemaAction = MissingSchemaAction.AddWithKey  'force unique key in spreadsheet
            _da.AcceptChangesDuringUpdate = True
            _cb = New OleDbCommandBuilder(_da) 'setup the commands

            With fpSS.ActiveSheet
                .AutoGenerateColumns = False
                .DataAutoSizeColumns = False
                .DataAutoHeadings = False
                .DataMember = "Data"
                _da.Fill(ds, "Data")
                .DataSource = ds
            End With
    End Sub

    Public Function DeleteRow(ByVal Row As Integer) As Integer
                fpSS.ActiveSheet.RemoveRows(Row, 1)
                Return fpSS.ActiveSheet.ActiveRow.Index

            Catch ex As Exception
                Debug.Print("DeleteError " & ex.Message)
                Return -1
            End Try
    End Sub

    Public Function InsertRow() As Integer
            With fpSS.ActiveSheet
                Dim row As Integer = .ActiveRow.Index
                Dim hfdst As Integer = .GetValue(row, CalcColumn.HFDST)
                Dim par As Integer = .GetValue(row, CalcColumn.PAR)
                Dim wfm As Double = 1.0
                Dim wfa As Double = 1.0
                Dim targr As String = "A"
                Dim nr As Integer = pGetNextNr(row)
                row += 1 ' insert position
                .SetActiveCell(row, CalcColumn.AANTAL)

                .AddUnboundRows(row, 1)
               'setup some default values
                .SetValue(row, CalcColumn.RECTYPE, 0)
                .SetValue(row, CalcColumn.BEGRNR, 1)
                .SetValue(row, CalcColumn.HFDST, hfdst)
                .SetValue(row, CalcColumn.PAR, par)
                .SetValue(row, CalcColumn.NR, nr)
                .SetValue(row, CalcColumn.WFA, wfa)
                .SetValue(row, CalcColumn.WFM, wfm)
                .SetValue(row, CalcColumn.OMSCHR, "FARPOINT TEST")
                .SetValue(row, CalcColumn.TARGR, targr)
                .AddRowToDataSource(row, True) 'add row to datasource
                Return row
            End With
    End Function

       Public Sub Save()
                fpSS.EditMode = False

                Dim ds As DataSet = fpSS.ActiveSheet.DataSource
                Dim dt As DataTable = ds.Tables("Data")
                AddHandler dt.RowChanged, New DataRowChangeEventHandler(AddressOf OnRowChanged)
                Me.BindingContext().Item(ds, "Data").EndCurrentEdit() 'end all current edits

                If ds.HasChanges Then
                    ' First process deletes.
                    _da.Update(dt.Select(Nothing, Nothing, DataViewRowState.Deleted))
                    ' Next process updates.
                    _da.Update(dt.Select(Nothing, Nothing, DataViewRowState.ModifiedCurrent))
                    ' Finally, process inserts.
                    _da.Update(dt.Select(Nothing, Nothing, DataViewRowState.Added))
                End If

            Catch ex As Exception
                Debug.Print("UpdateError " & ex.Message)
            End Try
        End Sub

  • Replied 8 September 2017, 1:26 pm EST


    The code you have for removing the row from the Spread and thus the bound Spread is correct. The problem is in the DataAdapter. You are checking the state of the DataRows and this row is in the DataSet with a RowState of Deleted. However, since you never saved this row to the database, it is not in the database. All you did was insert it into the DataSet.

    You are not saving the data to the database when you call AcceptChanges. When you call AcceptChanges the DataRows from the DataSet/DataTable are all marked back to a RowState of UnModified, so you can know of  any changes made since the last time you called AcceptChanges. When you call this all bound controls will refetch. Since adding rows to a DataSet puts the row at the end of the current DataTable, rebinding the Spread will show the new row at the end. This would be the same of any control.

  • Replied 8 September 2017, 1:26 pm EST


    Thank you for your quick response,

    But you say I never saved it to the database, I don't agree with that.
    I have checked the database and the row was physically there.
    I also did a check on the rowstates in the dataset after the first save. They all had a state of unchanged.
    After the delete action a exeption is thrown when I trie to save it.

    I believe it when you say the problem is in the adapter.
    But the adapter gets his information from the datasource of Spread I think.
    There must be something that is going wrong. (Do I have to create a new instance of the dataadapter every time I do a save action?)

    Thank you for your comment about issue 2.
    Maybe I can do a sort on the spread and the row will apear on the right place.
    Scott, do I have to use AcceptChanges() in my situation. If not all stays in place.

    I hope I am not anoying to you


  • Replied 8 September 2017, 1:26 pm EST


    I missed where you said you called the save method after inserting the row. So, you are correct, that the row is in the database. I am not sure what is causing this exception. I would suggest searching the internet on the exception. There was a lot of things I found to try to fix the issue. You should not recreate the DataAdapter.

    One other thing to try is to bind the DataSource to the DataGrid control and do the same action. Some times they give more description on databinding errors.

  • Replied 8 September 2017, 1:26 pm EST


    When you insert a record into the bound Spread, it will add this row as a new DataRow into the bound DataSource and make it's RowState set to Added. Then, when you remove the row from the bound Spread, this will cause the RowState for this DataRow in the bound DataSource to be set to Deleted. WHen you go to update this row to the database, the DataAdapter you are using does not know whether you want to add this row, or add it and then delete it, so the Exception is thrown.

    This is an issue with your DataAdapter. I woudl suggest searching this exception online. I found many things to try to get around this issue with the exception.

  • Replied 8 September 2017, 1:26 pm EST

    Hi Scott,

    Thanks for your quick reply.
    I will look into your suggestions and try to solve my problem via the internet.
    But there is still one thing I don't understand.
    I thought that with the acceptchanges() the records were marked as unchanged and saved to the database.
    So the next action to delete it, is a normal delete action. Aparently not?
    Can you explain this?

    Another issue I run into is, that when you insert a row somewhere in the sheet and save it with acceptchanges() ,
    Spread shifts the new row to the last row. This is what probably happens in the database but isn't that another layer?
    How can I prevent this behavior. (the new row should stay where it was  inserted)

    thanks in advance

  • Replied 8 September 2017, 1:26 pm EST


    Maybe that is the issue. You are trying to Delete the records marked as Delete, but this record marked as Delete is not in the database. You only inserted into the bound DataSource.

  • Replied 8 September 2017, 1:26 pm EST

    Hello Scott,

    That's interesting, in other words I do not delete anything. I only remove a row.
    So how should I delete a row from a bound spreadsheet. There is nothing about this in your samples directory or in the help files.

    Did you see issue 2 in my previous reply?
    Can you answer that? Its really important to me.


  • Replied 8 September 2017, 1:26 pm EST

    Hi Scott,

    To end an edit you wrote somewhere on the forum:

     Dim dt As DataTable = FpSpread1.Sheets(0).DataSource

    But does the following code also do the trick :

                    Dim ds As DataSet = fpSS.ActiveSheet.DataSource
                    Dim dt As DataTable = ds.Tables("Data")
      ' store some value
     fpSS.ActiveSheet.SetValue(targetRow, targetCol, someValue)


  • Replied 8 September 2017, 1:26 pm EST


    This is the exact same code. My code just checks indexes to make sure the Spread has not been sorted.

Need extra support?

Upgrade your support plan and get personal unlimited phone support with our customer engagement team

Learn More

Forum Channels