none
Concurrency Violation executing Delete Command RRS feed

  • Question

  • I am using VBExpress to manage some data stored in an Excel 2007 workbook. I am able to add data to the workbook just fine. But if I try to delete a row, I always get a concurrency violation. Other discussions of this problem seem to involve deleting records that have just been added to the dataset. My problem occurs when I attempt to delete a record that was in the original data store.

    Specifically:

            str= "provider=Microsoft.ACE.OLEDB.12.0;Extended Properties=""Excel 12.0;HDR=YES;"";data source=C:\Documents and Settings\...\MeterDB.xlsx;
            cnMain = New OleDbConnection(str)
            Try
                'RareData is a named range in the workbook
                'dt is a data table that was previously filled from RareData
                daMain = New OleDbDataAdapter("SELECT * from [RareData]", cnMain)
                ' Create the update commands.
                Dim strD As String 
    Dim strI As String strD = "DELETE * FROM [RareData] WHERE Date_Time = ? AND BG = ? AND Carbs = ? AND Bolus = ? AND Basal = ?" strI = "INSERT INTO [RareData] (Date_Time, BG, Carbs, Bolus, Basal) VALUES (?, ?, ?, ?, ?)" strU = "UPDATE [RareData] SET Date_Time = ?, BG = ?, Carbs = ?, Bolus = ?, Basal = ?" Dim cmdD As New OleDbCommand(strD, cnMain) cmdD.Parameters.Add(dt.Date_TimeColumn.ColumnName, OleDbType.Date, 32, dt.Date_TimeColumn.ColumnName).SourceVersion = DataRowVersion.Original cmdD.Parameters.Add(dt.BGColumn.ColumnName, OleDbType.Single, 32, dt.BGColumn.ColumnName).SourceVersion = DataRowVersion.Original cmdD.Parameters.Add(dt.CarbsColumn.ColumnName, OleDbType.Single, 32, dt.CarbsColumn.ColumnName).SourceVersion = DataRowVersion.Original cmdD.Parameters.Add(dt.BolusColumn.ColumnName, OleDbType.Single, 32, dt.BolusColumn.ColumnName).SourceVersion = DataRowVersion.Original cmdD.Parameters.Add(dt.BasalColumn.ColumnName, OleDbType.Single, 32, dt.BasalColumn.ColumnName).SourceVersion = DataRowVersion.Original Dim cmdI As New OleDbCommand(strI, cnMain) cmdI.Parameters.Add(dt.Date_TimeColumn.ColumnName, OleDbType.Date, 32, dt.Date_TimeColumn.ColumnName) cmdI.Parameters.Add(dt.BGColumn.ColumnName, OleDbType.Single, 32, dt.BGColumn.ColumnName) cmdI.Parameters.Add(dt.CarbsColumn.ColumnName, OleDbType.Single, 32, dt.CarbsColumn.ColumnName) cmdI.Parameters.Add(dt.BolusColumn.ColumnName, OleDbType.Single, 32, dt.BolusColumn.ColumnName) cmdI.Parameters.Add(dt.BasalColumn.ColumnName, OleDbType.Single, 32, dt.BasalColumn.ColumnName) Dim cmdU As New OleDbCommand(strU, cnMain) cmdU.Parameters.Add(dt.Date_TimeColumn.ColumnName, OleDbType.Date, 32, dt.Date_TimeColumn.ColumnName) cmdU.Parameters.Add(dt.BGColumn.ColumnName, OleDbType.Single, 32, dt.BGColumn.ColumnName) cmdU.Parameters.Add(dt.CarbsColumn.ColumnName, OleDbType.Single, 32, dt.CarbsColumn.ColumnName) cmdU.Parameters.Add(dt.BolusColumn.ColumnName, OleDbType.Single, 32, dt.BolusColumn.ColumnName) cmdU.Parameters.Add(dt.BasalColumn.ColumnName, OleDbType.Single, 32, dt.BasalColumn.ColumnName) daMain.DeleteCommand = cmdD daMain.InsertCommand = cmdI daMain.UpdateCommand = cmdU 'Update the dataset cnMain.Open() Debug.Print("cnMain opened:" & vbCrLf _ & vbTab & "connection string = " & cnMain.ConnectionString) n = daMain.Update(dt) Catch e As OleDbException CatchOleDbExceptions(e) Debug.Print("Delete: " & daMain.DeleteCommand.CommandText) Debug.Print("Insert: " & daMain.InsertCommand.CommandText) Debug.Print("Update: " & daMain.UpdateCommand.CommandText) Debug.Print(e.Message) Catch e As DBConcurrencyException Debug.Print("Delete: " & daMain.DeleteCommand.CommandText) Debug.Print(e.Message) ' Finally Debug.Print("Closing cnMain:" & vbCrLf _ & vbTab & "connection string = " & cnMain.ConnectionString) cnMain.Close() End Try


    The output of the Debug.Print statements is:

    cnMain opened:
     connection string = provider=Microsoft.ACE.OLEDB.12.0;Extended Properties="Excel 12.0;HDR=YES;";data source=C:\Documents and Settings\Mary\BearsData\SBear\Medical\Diabetes\TrialPumpMeterDB.xlsx;
    Delete: DELETE * FROM [RareData] WHERE Date_Time = ? AND BG = ? AND Carbs = ? AND Bolus = ? AND Basal = ?
    Concurrency violation: the DeleteCommand affected 0 of the expected 1 records.
    Closing cnMain:
     connection string = provider=Microsoft.ACE.OLEDB.12.0;Extended Properties="Excel 12.0;HDR=YES;";data source=C:\Documents and Settings\Mary\BearsData\SBear\Medical\Diabetes\TrialPumpMeterDB.xlsx;

    Can anyone suggest a reason for the concurrency violation?

    Thanks,
    --Mable

    • Moved by Chris Robinson- MSFT Thursday, June 18, 2009 3:47 PM Seems a provider issue (From:ADO.NET DataSet)
    Wednesday, June 17, 2009 6:12 PM

Answers

  • AFAIK, the ability to delete a row from an Excel Worksheet, using either the Jet or ACE OLEDB provider, has never been supported.
    Paul ~~~~ Microsoft MVP (Visual Basic)
    • Marked as answer by Mable Friday, June 19, 2009 4:37 PM
    Friday, June 19, 2009 1:49 PM

All replies