Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > .NET > Other .NET > ADO.NET
Password Reminder
Register
| FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read
ADO.NET For discussion about ADO.NET.  Topics such as question regarding the System.Data namespace are appropriate.  Questions specific to a particular application should be posted in a forum specific to the application .
Welcome to the p2p.wrox.com Forums.

You are currently viewing the ADO.NET section of the Wrox Programmer to Programmer discussions. This is a community of tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developers’ questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Reply
 
Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old June 11th, 2003, 05:07 PM
Authorized User
 
Join Date: Jun 2003
Location: Gainesville, FL, USA.
Posts: 25
Thanks: 0
Thanked 0 Times in 0 Posts
Default Concurrency Violation Exception

This is giving me a head-ache. I hope someone will take pity and take a look. If you have any suggestion please, *please* let me know.
Thanks in advance for your time!

The Problem:
============
Shortest possible description:
System.Data.DBConcurrencyException: "Concurrency violation: the DeleteCommand affected 0 records." exception when deleting a row/record from a dataset.

A bit more:
In order to delete a record from the DB, I delete the corresponding row from the data set bound to the database table, and then call the Update method of the DataAdapter. I receive the following exception message:

System.Data.DBConcurrencyException: Concurrency violation: the DeleteCommand affected 0 records.

As an additional bonus, I use very similar code on a different table, and everything works smoothly!

The Gory Details:
=================
The exception reports the following trace stack:
   at System.Data.Common.DbDataAdapter.Update(DataRow[] dataRows, DataTableMapping tableMapping)
   at System.Data.Common.DbDataAdapter.Update(DataSet dataSet, String srcTable)
   at System.Data.Common.DbDataAdapter.Update(DataSet dataSet)
   at myFinances.Maintain_Accounts.UpdateDB() in <blah>\maintain_accounts.cs:line 55
   at myFinances.Maintain_Accounts.DeleteRecord(Int32 row) in <blah>\maintain_accounts.cs:line 74
   at myFinances.Maintain_Accounts.HandleDeleteRowReques t(Int32 row) in <blah>\maintain_accounts.cs:line 126
   at myFinances.Maintain_Accounts.dataGrid_MouseDown(Ob ject sender, MouseEventArgs e) in <blah>\maintain_accounts.cs:line 146
   at System.Windows.Forms.Control.OnMouseDown(MouseEven tArgs e)
   at System.Windows.Forms.DataGrid.OnMouseDown(MouseEve ntArgs e)
   at System.Windows.Forms.Control.WmMouseDown(Message& m, MouseButtons button, Int32 clicks)
   at System.Windows.Forms.Control.WndProc(Message& m)
   at System.Windows.Forms.ControlNativeWindow.OnMessage (Message& m)
   at System.Windows.Forms.ControlNativeWindow.WndProc(M essage& m)
   at System.Windows.Forms.NativeWindow.Callback(IntPtr hWnd, Int32 msg, IntPtr wparam, IntPtr lparam)

I receive the exception when I try to delete a pinned record in the ACCOUNTS table.

The trouble-class is the Maintain_Accounts; the details on that class are preceeded by a description of the set-up and a section on the Maintain_AccountTypes (which works!).

I am working on a Win XP machine, with .NET 1.0.
I am the only one using the DB: that's why I am using Access, and I am relying on the OptimisticConcurrency behavior of the DataSet/Adapter/Connection.

I am using a simple Access 2K DB, which includes the following relevant tables and fields:

table: ACCOUNT_TYPES
====================
  ID PK, Long Integer, Required, Indexed (No Duplicates)
  NAME Text(50), Required, NO-ZERO-LENGTH, Indexed (No Duplicates)
  DESC Text(250)

table: ACCOUNTS
===============
  ID PK, Long Integer, Required, Indexed (No Duplicates)
  NAME Text(50), Required, NO-ZERO-LENGTH, Indexed (No Duplicates)
  TYPE Long Integer, Required
  CREATED Short Date, Required
  DESC Text(250)
  BALANCE Double, Required

table: TRANSACTIONS
===================
  ID PK, Long Integer, Required, Indexed (No Duplicates)
  ACCOUNT Long Integer, Required
  TYPE Long Integer, Required
  DATE Short Date, Required
  AMOUNT Double, Required
  DESC Text(250)

These three tables create a simple hierarchy, based on the following relationships (note that I did not define these relationships in Access, so they are implicit):

ACCOUNT_TYPES.ID --[1-to-many]--ACCOUNTS.TYPE

and

ACCOUNTS.ID --[1-to-many]--TRANSACTIONS.ACCOUNT

[don't let the TRANSACTION.TYPE fool you: it leads to another table
which has nothing to do with this problem]

My intent is that of providing simple administrative tools to manage this database.
So, I created a C# project, with a Main Form, from which the user can select which function they need to perform. Among others:

~ Maintain Account Types: this is implemented by the Maintain_AccountTypes class (see below);
~ Maintain Accounts: this is implemented by the Maintain_Accounts class (see below).

The Maintain_AccountTypes and Maintain_Accounts classes should be rather similar. In fact, I developed the Maintain_AccountTypes first, and expected to re-use much of the code in the other one (though by re-use I mean "copy-paste-adapt").

The Maintain_AccountTypes class:
================================
This one works. It's a simple form, with an editeable data grid displaying the data from the ACCOUNT_TYPES table, and the simple "Refresh" (to load data from the DB), "Save Changes" (to save data to the DB), and "Close" buttons.
Here's some code snipplets (I am leaving out what seems unrelated), followed by a description of what is going on with all this code:

Code:
/* OleDbConnection1, oleDbDataAdapter1, and its commands were
  generated by using the VisualStudio's wizard */
    private System.Data.OleDb.OleDbDataAdapter oleDbDataAdapter1;
    private System.Data.OleDb.OleDbCommand oleDbSelectCommand1;
    private System.Data.OleDb.OleDbCommand oleDbInsertCommand1;
    private System.Data.OleDb.OleDbCommand oleDbUpdateCommand1;
    private System.Data.OleDb.OleDbCommand oleDbDeleteCommand1;
    private System.Data.OleDb.OleDbConnection oleDbConnection1;
/* ds_AccountTypes1, and dataGrid1 were generated using the
  Visual Studio's wizard; dataGridTableStyle1, and its column styles
  were created using the VisualStudio's Interface to the dataGrid1
  properties */
    private myFinances.ds_AccountTypes ds_AccountTypes1;
    private System.Windows.Forms.DataGrid dataGrid1;
    private System.Windows.Forms.DataGridTableStyle
    dataGridTableStyle1;
    private System.Windows.Forms.DataGridTextBoxColumn
    dataGridTextBoxColumn1;
    private System.Windows.Forms.DataGridTextBoxColumn
    dataGridTextBoxColumn2;
    private System.Windows.Forms.DataGridTextBoxColumn
    dataGridTextBoxColumn3;

    public Maintain_AccountTypes()
    {
      //
      // Required for Windows Form Designer support
      InitializeComponent();

      PopulateDataGrid();
    }

    private void PopulateDataGrid()
    {
      ds_AccountTypes1.Clear();
      oleDbDataAdapter1.Fill(ds_AccountTypes1);
    }
    private void UpdateDB()
    {
      oleDbDataAdapter1.Update(ds_AccountTypes1);
    }

    private void DeleteRecord(int row)
    {
this.ds_AccountTypes1.Tables["ACCOUNT_TYPES"].Rows[row].Delete();
      UpdateDB();
      PopulateDataGrid();
    }

    private void Substitute(int from, int to)
    {
string subCommandString = "UPDATE ACCOUNTS SET TYPE="+(to.ToString()) +
        " WHERE TYPE="+(from.ToString());
      OleDbCommand subCommand = new OleDbCommand(subCommandString,
        oleDbConnection1);
      subCommand.Connection.Open();
      subCommand.ExecuteNonQuery();
      oleDbConnection1.Close();
    }

    private bool CanDelete(int row)
    {
System.Data.DataRow theRow = this.ds_AccountTypes1.Tables["ACCOUNT_TYPES"].Rows[row];
      int theId = (int)theRow["ID"];
      int k = PinningRecords(theId);
      if(k==0)    return true;

      PromptSubstitute_AccountType psat = new
        PromptSubstitute_AccountType(theId, k, oleDbConnection1);
      DialogResult dr = psat.ShowDialog(this);
      if(dr==DialogResult.OK)
      {
        int theSubstitute = psat.ThePick;
        Substitute(theId, theSubstitute);
        return true;
      }
      return false;
    }

    private int PinningRecords(int theId)
    {
string pinningCommandString = "SELECT Count(*) FROM ACCOUNTS "+
    "WHERE TYPE="+(theId.ToString());
      OleDbCommand pinningCommand = new OleDbComman
        (pinningCommandString, oleDbConnection1);
      pinningCommand.Connection.Open();
      int r = (int)pinningCommand.ExecuteScalar();
      oleDbConnection1.Close();
      return r;
    }

    private void HandleDeleteRowRequest(int row)
    {
      string msg = "Do you want to delete row #";
      msg+=row.ToString();
      msg+=" from the database?\n";
msg+="(This change will be applied immediately, and cannot be rolled back)\n";
      DialogResult dr = MessageBox.Show(this, msg,
        "Deleting Data Record", MessageBoxButtons.YesNo);
      if(dr==DialogResult.Yes)
      {
        if(CanDelete(row))
            DeleteRecord(row);
      }
      //else do nothing: either in the prompt above, or
      //in CanDelete, the user picked to cancel the deletion.
    }

    private void dataGrid_MouseDown(object sender,
        System.Windows.Forms.MouseEventArgs e)
    {
      if(e.Button==MouseButtons.Right)
      {
        DataGrid myGrid = (DataGrid) sender;
        System.Windows.Forms.DataGrid.HitTestInfo hti;
        hti = myGrid.HitTest(e.X, e.Y);
        int row = hti.Row;

        switch (hti.Type) 
        {
        case System.Windows.Forms.DataGrid.HitTestType.RowHeader :
            HandleDeleteRowRequest(row);
            break;
        default:
            break;
        }
      }
    }

/* From the region generated by the Windows Form Designer: */
this.oleDbDataAdapter1 = new System.Data.OleDb.OleDbDataAdapter();
this.oleDbDeleteCommand1 = new System.Data.OleDb.OleDbCommand();
this.oleDbConnection1 = new System.Data.OleDb.OleDbConnection();
this.oleDbInsertCommand1 = new System.Data.OleDb.OleDbCommand();
this.oleDbSelectCommand1 = new System.Data.OleDb.OleDbCommand();
this.oleDbUpdateCommand1 = new System.Data.OleDb.OleDbCommand();
this.ds_AccountTypes1 = new myFinances.ds_AccountTypes();
this.dataGrid1 = new System.Windows.Forms.DataGrid();
this.dataGridTableStyle1 = new
    System.Windows.Forms.DataGridTableStyle();
this.dataGridTextBoxColumn1 = new
    System.Windows.Forms.DataGridTextBoxColumn();
this.dataGridTextBoxColumn2 = new
    System.Windows.Forms.DataGridTextBoxColumn();
this.dataGridTextBoxColumn3 = new
    System.Windows.Forms.DataGridTextBoxColumn();
((System.ComponentModel.ISupportInitialize)
    (this.ds_AccountTypes1)).BeginInit();
((System.ComponentModel.ISupportInitialize)
    (this.dataGrid1)).BeginInit();
this.SuspendLayout();
      // 
      // oleDbDataAdapter1
      // 
this.oleDbDataAdapter1.DeleteCommand = this.oleDbDeleteCommand1;
this.oleDbDataAdapter1.InsertCommand = this.oleDbInsertCommand1;
this.oleDbDataAdapter1.SelectCommand = this.oleDbSelectCommand1;
this.oleDbDataAdapter1.TableMappings.AddRange(new
    System.Data.Common.DataTableMapping[] {
new System.Data.Common.DataTableMapping("Table", "ACCOUNT_TYPES",
    new System.Data.Common.DataColumnMapping[] {
      new System.Data.Common.DataColumnMapping("ID", "ID"),
      new System.Data.Common.DataColumnMapping("NAME", "NAME"),
      new System.Data.Common.DataColumnMapping("DESC", "DESC")})});
this.oleDbDataAdapter1.UpdateCommand = this.oleDbUpdateCommand1;
      // 
      // oleDbDeleteCommand1
      // 
this.oleDbDeleteCommand1.CommandText =
    "DELETE FROM ACCOUNT_TYPES WHERE (ID = ?) AND ([DESC] = ? OR ? "+
    "IS NULL AND [DESC] IS NULL) AND (NAME = ?)";
this.oleDbDeleteCommand1.Connection = this.oleDbConnection1;
this.oleDbDeleteCommand1.Parameters.Add(new
    System.Data.OleDb.OleDbParameter(
        "Original_ID", System.Data.OleDb.OleDbType.Integer, 0,
        System.Data.ParameterDirection.Input, false,
        ((System.Byte)(10)), ((System.Byte)(0)), "ID",
        System.Data.DataRowVersion.Original, null));
this.oleDbDeleteCommand1.Parameters.Add(new
    System.Data.OleDb.OleDbParameter(
        "Original_DESC", System.Data.OleDb.OleDbType.VarWChar, 250,
        System.Data.ParameterDirection.Input, false,
        ((System.Byte)(0)), ((System.Byte)(0)), "DESC",
        System.Data.DataRowVersion.Original, null));
this.oleDbDeleteCommand1.Parameters.Add(new
    System.Data.OleDb.OleDbParameter(
        "Original_DESC1", System.Data.OleDb.OleDbType.VarWChar,250,
        System.Data.ParameterDirection.Input, false,
        ((System.Byte)(0)), ((System.Byte)(0)), "DESC",
        System.Data.DataRowVersion.Original, null));
this.oleDbDeleteCommand1.Parameters.Add(new
    System.Data.OleDb.OleDbParameter(
        "Original_NAME", System.Data.OleDb.OleDbType.VarWChar, 50,
        System.Data.ParameterDirection.Input, false,
        ((System.Byte)(0)), ((System.Byte)(0)), "NAME",
        System.Data.DataRowVersion.Original, null));
      // 
      // oleDbConnection1
      // 
this.oleDbConnection1.ConnectionString = @"Provider=Microsoft.Jet.OLEDB.4.0;Password="""";User ID=Admin;Data Source=myFinancesDB.mdb;Mode=Share Deny None;Extended Properties="""";Jet OLEDB:System database="""";Jet OLEDB:Registry Path="""";Jet OLEDB:Database Password="""";Jet OLEDB:Engine Type=5;Jet OLEDB:Database Locking Mode=1;Jet OLEDB:Global Partial Bulk Ops=2;Jet OLEDB:Global Bulk Transactions=1;Jet OLEDB:New Database Password="""";Jet OLEDB:Create System Database=False;Jet OLEDB:Encrypt Database=False;Jet OLEDB:Don't Copy Locale on Compact=False;Jet OLEDB:Compact Without Replica Repair=False;Jet OLEDB:SFP=False";
      // 
      // oleDbUpdateCommand1
      // 
this.oleDbUpdateCommand1.CommandText = "UPDATE ACCOUNT_TYPES SET "+
    "[DESC] = ?, ID = ?, NAME = ? WHERE (ID = ?) AND ([DESC] " +
    "= ? OR ? IS NULL AND [DESC] IS NULL) AND (NAME = ?)";
this.oleDbUpdateCommand1.Connection = this.oleDbConnection1;
this.oleDbUpdateCommand1.Parameters.Add(new
    System.Data.OleDb.OleDbParameter(
        "DESC", System.Data.OleDb.OleDbType.VarWChar, 250,"DESC"));
this.oleDbUpdateCommand1.Parameters.Add(new
    System.Data.OleDb.OleDbParameter(
        "ID", System.Data.OleDb.OleDbType.Integer, 0,
        System.Data.ParameterDirection.Input, false,
        ((System.Byte)(10)), ((System.Byte)(0)), "ID",
        System.Data.DataRowVersion.Current, null));
this.oleDbUpdateCommand1.Parameters.Add(new
    System.Data.OleDb.OleDbParameter(
        "NAME", System.Data.OleDb.OleDbType.VarWChar, 50, "NAME"));
this.oleDbUpdateCommand1.Parameters.Add(new
    System.Data.OleDb.OleDbParameter(
        "Original_ID", System.Data.OleDb.OleDbType.Integer, 0,
        System.Data.ParameterDirection.Input, false,
        ((System.Byte)(10)), ((System.Byte)(0)), "ID",
        System.Data.DataRowVersion.Original, null));
this.oleDbUpdateCommand1.Parameters.Add(new
    System.Data.OleDb.OleDbParameter(
        "Original_DESC", System.Data.OleDb.OleDbType.VarWChar, 250,
        System.Data.ParameterDirection.Input, false,
        ((System.Byte)(0)), ((System.Byte)(0)), "DESC",
        System.Data.DataRowVersion.Original, null));
this.oleDbUpdateCommand1.Parameters.Add(new
    System.Data.OleDb.OleDbParameter(
        "Original_DESC1", System.Data.OleDb.OleDbType.VarWChar,250,
        System.Data.ParameterDirection.Input, false,
        ((System.Byte)(0)), ((System.Byte)(0)), "DESC",
        System.Data.DataRowVersion.Original, null));
this.oleDbUpdateCommand1.Parameters.Add(new
    System.Data.OleDb.OleDbParameter(
        "Original_NAME", System.Data.OleDb.OleDbType.VarWChar, 50,
        System.Data.ParameterDirection.Input, false,
        ((System.Byte)(0)), ((System.Byte)(0)), "NAME",
        System.Data.DataRowVersion.Original, null));
      // 
      // ds_AccountTypes1
      // 
this.ds_AccountTypes1.DataSetName = "ds_AccountTypes";
this.ds_AccountTypes1.Locale =
    new System.Globalization.CultureInfo("en-US");
this.ds_AccountTypes1.Namespace =
    "http://www.tempuri.org/ds_AccountTypes.xsd";
      // 
      // dataGrid1
      // 
/* stuff that is not related plus: */
this.dataGrid1.MouseDown +=
  new System.Windows.Forms.MouseEventHandler(this.dataGrid_MouseDown);

((System.ComponentModel.ISupportInitialize)
    (this.ds_AccountTypes1)).EndInit();
((System.ComponentModel.ISupportInitialize)(this.dataGrid1)).EndInit();
this.ResumeLayout(false);
In other words: the typical set-up of a data grid bound to a data set (with its data adapter, and its commands), plus the handler for a mouse down on the data grid. The handler determines if the click took place in a row-header. If so, it asks the user if (s)he wishes to delete the record in that row from the DB.
If the user says "Yes", then the handler checks with the CanDelete routine. This routine determines if the record that the user wants to delete is "pinned" (i.e. if its ID appears in any record in the ACCOUNTS table's TYPE field). If the record is not pinned, CanDelete returns true, and the handler will call DeleteRecord to delete the record. If, on the other hand, the record is pinned, the user is prompted with another dialog (an instance of the PromptSubstitute_AccountType class, not shown here) where the user can either Cancel the deletion, or specify a different Account Type to be used as "substitute" for the one to be deleted. In this case, before deleting the Account Type in question, the routine will call the Substitute method and ensure that the children (in the ACCOUNTS table) of the Account Type to be deleted will be updated to become children of the Account Type specified by the user.

That's a mouthfull ! But it works.

The Maintain_Accounts class:
============================
This one doesn't work!
It is in all respects very similar to the Maintain_AccountTypes class, but it displays the data from the ACCOUNTS table (instead of the ACCOUNT_TYPES table), and, when checking for a "pinned" record, looks into the TRANSACTIONS table.
Here's the code from the Maintain_Accounts class:

Code:
/* OleDbConnection1, oleDbDataAdapter1, and its commands were
  generated by using the VisualStudio's wizard */
    private System.Data.OleDb.OleDbDataAdapter oleDbDataAdapter1;
    private System.Data.OleDb.OleDbCommand oleDbSelectCommand1;
    private System.Data.OleDb.OleDbCommand oleDbInsertCommand1;
    private System.Data.OleDb.OleDbCommand oleDbUpdateCommand1;
    private System.Data.OleDb.OleDbCommand oleDbDeleteCommand1;
    private System.Data.OleDb.OleDbConnection oleDbConnection1;
/* ds_Accounts1, and dataGrid1 were generated using the
  Visual Studio's wizard; dataGridTableStyle1, and its column styles
  were created using the VisualStudio's Interface to the dataGrid1
  properties */
    private myFinances.ds_Accounts ds_Accounts1;
    private System.Windows.Forms.DataGrid dataGrid1;
    private System.Windows.Forms.DataGridTableStyle dataGridTableStyle1;
    private System.Windows.Forms.DataGridTextBoxColumn dataGridTextBoxColumn1;
    private System.Windows.Forms.DataGridTextBoxColumn dataGridTextBoxColumn2;
    private System.Windows.Forms.DataGridTextBoxColumn dataGridTextBoxColumn3;
    private System.Windows.Forms.DataGridTextBoxColumn dataGridTextBoxColumn4;
    private System.Windows.Forms.DataGridTextBoxColumn dataGridTextBoxColumn5;
    private System.Windows.Forms.DataGridTextBoxColumn dataGridTextBoxColumn6;

    public Maintain_Accounts()
    {
      //
      // Required for Windows Form Designer support
      InitializeComponent();

      PopulateDataGrid();
    }

    private void PopulateDataGrid()
    {
      ds_Accounts1.Clear();
      oleDbDataAdapter1.Fill(ds_Accounts1);
    }
    private void UpdateDB()
    {
      oleDbDataAdapter1.Update(ds_Accounts1);
    }

    private void DeleteRecord(int row)
    {
      this.ds_Accounts1.Tables["ACCOUNTS"].Rows[row].Delete();
      UpdateDB();
      PopulateDataGrid();
    }

    private void Substitute(int from, int to)
    {
string subCommandString = "UPDATE TRANSACTIONS SET ACCOUNT="+
    (to.ToString()) +" WHERE ACCOUNT="+(from.ToString());
OleDbCommand subCommand = new OleDbCommand(subCommandString,
    oleDbConnection1);
      subCommand.Connection.Open();
      subCommand.ExecuteNonQuery();
      oleDbConnection1.Close();
    }

    private bool CanDelete(int row)
    {
System.Data.DataRow theRow =
    this.ds_Accounts1.Tables["ACCOUNTS"].Rows[row];
      int theId = (int)theRow["ID"];
      int k = PinningRecords(theId);
      if(k==0)    return true;

PromptSubstitute_Account psa = new PromptSubstitute_Account(theId, k,
    oleDbConnection1);
      DialogResult dr = psa.ShowDialog(this);
      if(dr==DialogResult.OK)
      {
        int theSubstitute = psa.ThePick;
        Substitute(theId, theSubstitute);
        return true;
      }
      return false;
    }

    private int PinningRecords(int theId)
    {
string pinningCommandString = "SELECT Count(*) FROM TRANSACTIONS "+
    "WHERE ACCOUNT="+(theId.ToString());
OleDbCommand pinningCommand = new OleDbCommand(pinningCommandString,
    oleDbConnection1);
      pinningCommand.Connection.Open();
      int r = (int)pinningCommand.ExecuteScalar();
      oleDbConnection1.Close();
      return r;
    }

    private void HandleDeleteRowRequest(int row)
    {
      string msg = "Do you want to delete row #";
      msg+=row.ToString();
      msg+=" from the database?\n";
msg+="(This change will be applied immediately, and cannot be rolled back)\n";
DialogResult dr = MessageBox.Show(this, msg, "Deleting Data Record",
    MessageBoxButtons.YesNo);
      if(dr==DialogResult.Yes)
      {
        if(CanDelete(row))
          DeleteRecord(row);
      }
      //else do nothing: either in the prompt above, or
      //in CanDelete, the user picked to cancel the deletion.
    }

    private void dataGrid_MouseDown(object sender,
        System.Windows.Forms.MouseEventArgs e)
    {
        if(e.Button==MouseButtons.Right)
        {
          DataGrid myGrid = (DataGrid) sender;
          System.Windows.Forms.DataGrid.HitTestInfo hti;
          hti = myGrid.HitTest(e.X, e.Y);
          int row = hti.Row;

          switch (hti.Type) 
          {
        case System.Windows.Forms.DataGrid.HitTestType.RowHeader :
              HandleDeleteRowRequest(row);
              break;
        default:
              break;
          }
        }
    }

/* From the region generated by the Windows Form Designer: */
this.oleDbDataAdapter1 = new System.Data.OleDb.OleDbDataAdapter();
this.oleDbDeleteCommand1 = new System.Data.OleDb.OleDbCommand();
this.oleDbConnection1 = new System.Data.OleDb.OleDbConnection();
this.oleDbInsertCommand1 = new System.Data.OleDb.OleDbCommand();
this.oleDbSelectCommand1 = new System.Data.OleDb.OleDbCommand();
this.oleDbUpdateCommand1 = new System.Data.OleDb.OleDbCommand();
this.ds_Accounts1 = new myFinances.ds_Accounts();
this.dataGrid1 = new System.Windows.Forms.DataGrid();
this.dataGridTableStyle1 = new System.Windows.Forms.DataGridTableStyle();
this.dataGridTextBoxColumn1 =
    new System.Windows.Forms.DataGridTextBoxColumn();
this.dataGridTextBoxColumn2 =
    new System.Windows.Forms.DataGridTextBoxColumn();
this.dataGridTextBoxColumn3 =
    new System.Windows.Forms.DataGridTextBoxColumn();
this.dataGridTextBoxColumn4 =
    new System.Windows.Forms.DataGridTextBoxColumn();
this.dataGridTextBoxColumn5 =
    new System.Windows.Forms.DataGridTextBoxColumn();
this.dataGridTextBoxColumn6 =
    new System.Windows.Forms.DataGridTextBoxColumn();
((System.ComponentModel.ISupportInitialize)
    (this.ds_Accounts1)).BeginInit();
((System.ComponentModel.ISupportInitialize)
    (this.dataGrid1)).BeginInit();
this.SuspendLayout();
      // 
      // oleDbDataAdapter1
      // 
this.oleDbDataAdapter1.DeleteCommand = this.oleDbDeleteCommand1;
this.oleDbDataAdapter1.InsertCommand = this.oleDbInsertCommand1;
this.oleDbDataAdapter1.SelectCommand = this.oleDbSelectCommand1;
this.oleDbDataAdapter1.TableMappings.AddRange(
  new System.Data.Common.DataTableMapping[] {
    new System.Data.Common.DataTableMapping("Table", "ACCOUNTS",
      new System.Data.Common.DataColumnMapping[] {
        new System.Data.Common.DataColumnMapping("ID", "ID"),
        new System.Data.Common.DataColumnMapping("NAME", "NAME"),
        new System.Data.Common.DataColumnMapping("TYPE", "TYPE"),
        new System.Data.Common.DataColumnMapping("CREATED",
            "CREATED"),
        new System.Data.Common.DataColumnMapping("DESC", "DESC"),
        new System.Data.Common.DataColumnMapping("BALANCE",
            "BALANCE")})});
this.oleDbDataAdapter1.UpdateCommand = this.oleDbUpdateCommand1;
      // 
      // oleDbDeleteCommand1
      // 
this.oleDbDeleteCommand1.CommandText = "DELETE FROM ACCOUNTS WHERE "+
    "(ID = ?) AND (BALANCE = ?) AND (CREATED = ?) AND ([DESC] "+
    "= ? OR ? IS NULL AND [DESC] IS NULL) AND (NAME = ?)";
this.oleDbDeleteCommand1.Connection = this.oleDbConnection1;
this.oleDbDeleteCommand1.Parameters.Add(new
    System.Data.OleDb.OleDbParameter("Original_ID",
        System.Data.OleDb.OleDbType.Integer, 0,
        System.Data.ParameterDirection.Input, false,
        ((System.Byte)(10)), ((System.Byte)(0)), "ID",
        System.Data.DataRowVersion.Original, null));
this.oleDbDeleteCommand1.Parameters.Add(new
    System.Data.OleDb.OleDbParameter("Original_BALANCE",
        System.Data.OleDb.OleDbType.Double, 0,
        System.Data.ParameterDirection.Input, false,
        ((System.Byte)(15)), ((System.Byte)(0)), "BALANCE",
        System.Data.DataRowVersion.Original, null));
this.oleDbDeleteCommand1.Parameters.Add(new
    System.Data.OleDb.OleDbParameter("Original_CREATED",
        System.Data.OleDb.OleDbType.DBDate, 0,
        System.Data.ParameterDirection.Input, false,
        ((System.Byte)(0)), ((System.Byte)(0)), "CREATED",
        System.Data.DataRowVersion.Original, null));
this.oleDbDeleteCommand1.Parameters.Add(new
    System.Data.OleDb.OleDbParameter("Original_DESC",
        System.Data.OleDb.OleDbType.VarWChar, 250,
        System.Data.ParameterDirection.Input, false,
        ((System.Byte)(0)), ((System.Byte)(0)), "DESC",
        System.Data.DataRowVersion.Original, null));
this.oleDbDeleteCommand1.Parameters.Add(new
    System.Data.OleDb.OleDbParameter("Original_DESC1",
        System.Data.OleDb.OleDbType.VarWChar, 250,
        System.Data.ParameterDirection.Input, false,
        ((System.Byte)(0)), ((System.Byte)(0)), "DESC",
        System.Data.DataRowVersion.Original, null));
this.oleDbDeleteCommand1.Parameters.Add(new
    System.Data.OleDb.OleDbParameter("Original_NAME",
        System.Data.OleDb.OleDbType.VarWChar, 50,
        System.Data.ParameterDirection.Input, false,
        ((System.Byte)(0)), ((System.Byte)(0)), "NAME",
        System.Data.DataRowVersion.Original, null));
      // 
      // oleDbConnection1
      // 
      this.oleDbConnection1.ConnectionString = @"Provider=Microsoft.Jet.OLEDB.4.0;Password="""";User ID=Admin;Data Source=myFinancesDB.mdb;Mode=Share Deny None;Extended Properties="""";Jet OLEDB:System database="""";Jet OLEDB:Registry Path="""";Jet OLEDB:Database Password="""";Jet OLEDB:Engine Type=5;Jet OLEDB:Database Locking Mode=1;Jet OLEDB:Global Partial Bulk Ops=2;Jet OLEDB:Global Bulk Transactions=1;Jet OLEDB:New Database Password="""";Jet OLEDB:Create System Database=False;Jet OLEDB:Encrypt Database=False;Jet OLEDB:Don't Copy Locale on Compact=False;Jet OLEDB:Compact Without Replica Repair=False;Jet OLEDB:SFP=False";
      // 
      // oleDbUpdateCommand1
      // 
this.oleDbUpdateCommand1.CommandText = "UPDATE ACCOUNTS SET BALANCE "+
    "= ?, CREATED = ?, [DESC] = ?, ID = ?, NAME = ?, TYPE " +
    "= ? WHERE (ID = ?) AND (BALANCE = ?) AND (CREATED = ?) AND "+
    "([DESC] = ? OR ? IS NULL AND [DESC] IS NULL) AND (NAME = ?)";
this.oleDbUpdateCommand1.Connection = this.oleDbConnection1;
this.oleDbUpdateCommand1.Parameters.Add(new
    System.Data.OleDb.OleDbParameter("BALANCE",
        System.Data.OleDb.OleDbType.Double, 0,
        System.Data.ParameterDirection.Input, false,
        ((System.Byte)(15)), ((System.Byte)(0)), "BALANCE",
        System.Data.DataRowVersion.Current, null));
this.oleDbUpdateCommand1.Parameters.Add(new
    System.Data.OleDb.OleDbParameter("CREATED",
        System.Data.OleDb.OleDbType.DBDate, 0, "CREATED"));
this.oleDbUpdateCommand1.Parameters.Add(new
    System.Data.OleDb.OleDbParameter("DESC",
        System.Data.OleDb.OleDbType.VarWChar, 250, "DESC"));
this.oleDbUpdateCommand1.Parameters.Add(new
    System.Data.OleDb.OleDbParameter("ID",
        System.Data.OleDb.OleDbType.Integer, 0,
        System.Data.ParameterDirection.Input, false,
        ((System.Byte)(10)), ((System.Byte)(0)), "ID",
        System.Data.DataRowVersion.Current, null));
this.oleDbUpdateCommand1.Parameters.Add(new
    System.Data.OleDb.OleDbParameter("NAME",
        System.Data.OleDb.OleDbType.VarWChar, 50, "NAME"));
this.oleDbUpdateCommand1.Parameters.Add(new
    System.Data.OleDb.OleDbParameter("TYPE",
        System.Data.OleDb.OleDbType.Integer, 0,
        System.Data.ParameterDirection.Input, false,
        ((System.Byte)(10)), ((System.Byte)(0)), "TYPE",
        System.Data.DataRowVersion.Current, null));
this.oleDbUpdateCommand1.Parameters.Add(new
    System.Data.OleDb.OleDbParameter("Original_ID",
        System.Data.OleDb.OleDbType.Integer, 0,
        System.Data.ParameterDirection.Input, false,
        ((System.Byte)(10)), ((System.Byte)(0)), "ID",
        System.Data.DataRowVersion.Original, null));
this.oleDbUpdateCommand1.Parameters.Add(new
    System.Data.OleDb.OleDbParameter("Original_BALANCE",
        System.Data.OleDb.OleDbType.Double, 0,
        System.Data.ParameterDirection.Input, false,
        ((System.Byte)(15)), ((System.Byte)(0)), "BALANCE",
        System.Data.DataRowVersion.Original, null));
this.oleDbUpdateCommand1.Parameters.Add(new
    System.Data.OleDb.OleDbParameter("Original_CREATED",
        System.Data.OleDb.OleDbType.DBDate, 0,
        System.Data.ParameterDirection.Input, false,
        ((System.Byte)(0)), ((System.Byte)(0)), "CREATED",
        System.Data.DataRowVersion.Original, null));
this.oleDbUpdateCommand1.Parameters.Add(new
    System.Data.OleDb.OleDbParameter("Original_DESC",
        System.Data.OleDb.OleDbType.VarWChar, 250,
        System.Data.ParameterDirection.Input, false,
        ((System.Byte)(0)), ((System.Byte)(0)), "DESC",
        System.Data.DataRowVersion.Original, null));
this.oleDbUpdateCommand1.Parameters.Add(new
    System.Data.OleDb.OleDbParameter("Original_DESC1",
        System.Data.OleDb.OleDbType.VarWChar, 250,
        System.Data.ParameterDirection.Input, false,
        ((System.Byte)(0)), ((System.Byte)(0)), "DESC",
        System.Data.DataRowVersion.Original, null));
this.oleDbUpdateCommand1.Parameters.Add(new
    System.Data.OleDb.OleDbParameter("Original_NAME",
        System.Data.OleDb.OleDbType.VarWChar, 50,
        System.Data.ParameterDirection.Input, false,
        ((System.Byte)(0)), ((System.Byte)(0)), "NAME",
        System.Data.DataRowVersion.Original, null));
      // 
      // dataGrid1
      // 
/* stuff that is not related plus: */
this.dataGrid1.MouseDown +=
  new System.Windows.Forms.MouseEventHandler(this.dataGrid_MouseDown);

((System.ComponentModel.ISupportInitialize)
    (this.ds_Accounts1)).EndInit();
((System.ComponentModel.ISupportInitialize)(this.dataGrid1)).EndInit();
      this.ResumeLayout(false);
Reply With Quote
  #2 (permalink)  
Old June 13th, 2003, 07:29 PM
Authorized User
 
Join Date: Jun 2003
Location: Gainesville, FL, USA.
Posts: 25
Thanks: 0
Thanked 0 Times in 0 Posts
Default

... Anyone? ... Bueller? ... if this is not the most appropriate forum to post this, let me know... thanks
Reply With Quote
  #3 (permalink)  
Old June 13th, 2003, 09:53 PM
Friend of Wrox
Points: 3,489, Level: 24
Points: 3,489, Level: 24 Points: 3,489, Level: 24 Points: 3,489, Level: 24
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jun 2003
Location: Central, NJ, USA.
Posts: 1,102
Thanks: 0
Thanked 2 Times in 2 Posts
Default

I can tell you that this exception is thrown when there are no rows affected by the update.

I can't tell you why that's the case.

I would suggest you try and run a query in Query analyzer and see what happens in the SQL Server when it's run. Perhaps deleting the row would cause a problem with some kind of constraint or foreign key requirement?


Hal Levy
Daddyshome, LLC
Reply With Quote
  #4 (permalink)  
Old June 14th, 2003, 06:49 AM
Authorized User
 
Join Date: Jun 2003
Location: Gainesville, FL, USA.
Posts: 25
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Hal,
thanks for the suggestion.
The DB has no foreign key defined, but I don't have
an SQL server to try it on. (sigh.. else I'd be
using that instead of Access :-)

I'll try to pass the query through the analyzer and
see if that yields any clue.

Thanks again.
Reply With Quote
  #5 (permalink)  
Old June 14th, 2003, 11:08 AM
Authorized User
 
Join Date: Jun 2003
Location: Gainesville, FL, USA.
Posts: 25
Thanks: 0
Thanked 0 Times in 0 Posts
Default

EUREKA ! Well, not reallly... I'm not sure of the Why, but I got it to work!
I'll post my work-around just in case anyone else hits this issue.

I changes the SQL command associated with the Deletion.
In other words, I changed from
Code:
      // oleDbDeleteCommand1
      // 
this.oleDbDeleteCommand1.CommandText = "DELETE FROM ACCOUNTS WHERE "+
    "(ID = ?) AND (BALANCE = ?) AND (CREATED = ?) AND ([DESC] "+
    "= ? OR ? IS NULL AND [DESC] IS NULL) AND (NAME = ?)";
to
Code:
this.oleDbDeleteCommand1.CommandText = "DELETE FROM ACCOUNTS WHERE (ID = ?)";
Of course, I could do that because I know ID is the only PrimaryKey in that table.

Cheers!
Reply With Quote
  #6 (permalink)  
Old June 16th, 2003, 05:40 PM
Friend of Wrox
Points: 3,489, Level: 24
Points: 3,489, Level: 24 Points: 3,489, Level: 24 Points: 3,489, Level: 24
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jun 2003
Location: Central, NJ, USA.
Posts: 1,102
Thanks: 0
Thanked 2 Times in 2 Posts
Default

Well, it sounds like something was too specific in your SQL statement. If you have a Primary key, I don't know why you would use anything more in your Delete...


Hal Levy
Daddyshome, LLC
Reply With Quote
  #7 (permalink)  
Old June 17th, 2003, 05:40 PM
Authorized User
 
Join Date: Jun 2003
Location: Gainesville, FL, USA.
Posts: 25
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Hal,
I agree with you. Evidently the SQL statements produced by VisualStudio take the approach that
it is better to use all of the known parameters...
...which seems to me the "easy" way out to have a program generate the statements as needed.

Evidently, even MS found too cumbersome opening the DB and getting all of the Database schema
information :-)
Reply With Quote
  #8 (permalink)  
Old July 3rd, 2003, 03:14 AM
Registered User
 
Join Date: Jul 2003
Location: , , Chile.
Posts: 1
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Quote:
quote:Originally posted by Olorin
 This is giving me a head-ache. I hope someone will take pity and take a look. If you have any suggestion please, *please* let me know.
Thanks in advance for your time!
The heart of this problem lies in the field that has a data type of "double"

For this type of field, you cannot accurately test for equality. So ADO.NET's default "optimistic" concurrency handling doesn't work.

Your solution by using the PK in the WHERE clause is quite appropriate in this scenario, and I use it a lot too. This is the so-called "last-write-wins" concurrency handling.

CS One
Reply With Quote
  #9 (permalink)  
Old July 6th, 2003, 09:07 AM
Authorized User
 
Join Date: Jun 2003
Location: Gainesville, FL, USA.
Posts: 25
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Csharpone...
...you might just be right...

I'll have to check on that...
..if, however, double fields create this kind of problem,
how could one use a double field as a PK ?

Olorin

[added later on]
Granted... using a double as a PK is not a Very Bright Idea...
[/added later on]
Reply With Quote
  #10 (permalink)  
Old September 18th, 2003, 10:11 AM
Registered User
 
Join Date: Sep 2003
Location: Cincinnati, OH, USA.
Posts: 1
Thanks: 0
Thanked 0 Times in 0 Posts
Default

I think I found a better answer (I just ran into this same problem, also using an Access DB)
Instead of a DataSet I'm bringing in a single DataTable through my DataAdapter, but I believe the behavior is exactle the same.

Well, look what msdn says about the DataAdapter

"When the OleDbDataAdapter fills a DataSet, it will create the necessary tables and columns for the returned data if they do not already exist. However, primary key information will not be included in the implicitly created schema unless the MissingSchemaAction property is set to AddWithKey. You may also have the OleDbDataAdapter create the schema of the DataSet, including primary key information, before filling it with data using FillSchema()"

And I believe that is what is happening, the DataSet (or Table in my case) doesn't know what the Primary Key of your tables are, and then it generates that long DELETE command trying to match all of the fields.

In the end, all I had to do to solve this was to add one call to FillSchema before Filling the table, as follows:

oleConn.Open();
OleDbDataAdapter dataAdapt = new OleDbDataAdapter(strSqlSelect, oleConn);
dataAdapt.FillSchema(dataTable,SchemaType.Source); //<--THIS ONE
dataAdapt.Fill(dataTable);
oleConn.Close();

and after this, any of the Update actions (Delete,Update&Insert) are successful with the
sql statements auto-generated by the CommandBuilder.

Cheers,

=======================
Christian Amelinckx
.Net junkie
Reply With Quote
Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
concurrency violation updating child table karenai VB Databases Basics 1 January 27th, 2008 02:29 AM
Concurrency Violation Problem - Please HELP! Aaron Edwards ASP.NET 2.0 Basics 0 March 25th, 2007 08:17 PM
Concurrency Violation Problem - Please HELP! Aaron Edwards ADO.NET 1 March 25th, 2007 08:15 PM
Concurrency dextertoh Classic ASP Databases 2 May 17th, 2004 12:53 AM
Concurrency Violation performing Database updates LaFeverMF VB Databases Basics 1 February 15th, 2004 01:51 PM



All times are GMT -4. The time now is 07:52 AM.


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.