同一IDでの行の削除・追加でOleDbException

Question

同一IDでの行削除・追加でOleDbExceptionが発生します。
どのようなコードを書けばこれを回避できるのかご教授ください。

再現手順は次の通りです。
Accessのmdbで主キーを設定したテーブルを作り、
それをもとにDataSetを作り、それをC#のフォームに
ドラッグアンドドロップでDataGridViewを貼りつけただけのプログラムです。
（１）１行追加（ID=1）
（２）BindingNavigatorの保存ボタンで保存
（３）BindingNavigatorの削除ボタンで削除
（４）再度同じIDで１行追加（ID=1）
（５）BindingNavigatorの保存ボタンで保存
これだけで、次のエラーが発生します。

System.Data.OleDb.OleDbException はハンドルされませんでした。   Message=インデックス、主キー、またはリレーションシップで値が重複しているので、テーブルを変更できませんでした。重複する値のあるフィールドの値を変更するか、インデックスを削除してください。または重複する値を使用できるように再定義してください。   Source=Microsoft JET Database Engine   ErrorCode=-2147467259

TableAdapterManagerのUpdateOrderの設定は、「InsertUpdateDelete」になっています。
このため、おそらく、内部的にDeleteの前にInsertを実行して上記のエラーになっているような気がしますが詳細はわかりません。
どのようなコーディングをすればいいでしょうか。

C#のコードは次の通りです。

        private void testTableBindingNavigatorSaveItem_Click(object sender, EventArgs e)
        {
            this.Validate();
            this.testTableBindingSource.EndEdit();
            this.tableAdapterManager.UpdateAll(this.db1DataSet);

        }

        private void Form1_Load(object sender, EventArgs e)
        {
            this.testTableTableAdapter.Fill(this.db1DataSet.TestTable);

        }

以上、よろしくお願いいたします。

Answer 1

試したわけではなく想像で言ってるだけなので違うかもしれませんが、ID 列をオートナンバーにしているということはありませんか？

Access でその .mdb ファイルを開いて同等のことを行った場合は期待通りいくのでしょうか？

> TableAdapterManagerのUpdateOrderの設定は、「InsertUpdateDelete」になっています。

それは DataTable の各列の更新をどの順序で行うかということを指定するもので、今回の話とは関係ないと思われます。

以下の記事の図１と図２を見てください。TableAdapterManager がないと上から順番になってしまいますが、TableAdapterManager によって Insert => Update => Delete の順で更新が行われるということです。

DB 設計者のための明解 ADO.NET 第 1 回
https://msdn.microsoft.com/ja-jp/library/cc482903.aspx

Answer 2

TableAdapterManagerを使っている限り、おそらく不可能でしょう。自分でDeleteが先になるようにコーディングするしかありません。
以下の例のように、GetChangesメソッドで、Delete分、Insert分を取り出して、順に更新します。

方法 : データセットの変更をデータベースに保存する
https://msdn.microsoft.com/ja-jp/library/xzb1zw3x.aspx

ところで、そもそもこのような問題が発生するのは、ID値を人が入力できるようにしているからです。人が入力せずに、システムが自動的に振るキーを主キーにすれば、このようなことはなくなります。このようなキーをサロゲートキーと呼びます。

---

★良い回答には回答済みマークを付けよう！ MVP - .NET 　http://d.hatena.ne.jp/trapemiya/

Answer 3

> TableAdapterManagerを使っている限り、おそらく不可能でしょう。

質問者さんの手順を見ておられるでしょうか？　質問者さんの手順で行って「不可能」ということはないと思いますけど。

Answer 4

回答ありがとうございます。
オートナンバーにはしておりません。数値型（長整数型）で主キーの設定にしています。

Access で.mdb ファイルを開いて同様の手順を行った場合は期待通りの動きになっています。エラーにもなりません。

Answer 5

> Access で.mdb ファイルを開いて同様の手順を行った場合は期待通りの動きになっています。

というのが解せないんですが、以下のような話は関係ないのでしょうか？

インデックス、主キー、またはリレーションシップで値が重複
http://psp8155.blog13.fc2.com/blog-entry-366.html

同じ問題で悩んだ人は多いようで、エラーメッセージでググると他にもいろいろ出てきますので、一度検索してみてはいかがですか。

その上で先の私のレス（TableAdapterManager による更新順序は関係なさそう）をあわせて考えると別の展開が見えてくるのではないかと思います。

Answer 6

＞質問者さんの手順を見ておられるでしょうか？　質問者さんの手順で行って「不可能」ということはないと思いますけど。

もちろん見ていますよ。私が勘違いしていなければ、手順３と４の間で保存ボタンを押せば問題が発生しないはずです。押さない場合は、５の手順で保存ボタンを押した際に、３と４をまとめてmdbに反映させようとしますが、deleteの前にinsertを行うので、ID値が重複してしまい、エラーになるのでしょう。

---

★良い回答には回答済みマークを付けよう！ MVP - .NET 　http://d.hatena.ne.jp/trapemiya/

Answer 7

＞Access で.mdb ファイルを開いて同様の手順を行った場合は期待通りの動きになっています。エラーにもなりません。

Accessの編集画面では操作結果を即時に反映させるモードとそうじゃないモードがあったと思います。（あいまいでごめんなさい）
通常は、即時に反映させるモードになっているので、何かやる度にmdbを更新しにいきますから、deleteとinsertをまとめて行うことはありません。つまり、１行ずつその度にmdbを更新します。よって、エラーになりません。
TableAdapterManagerの場合は、ご質問文に書かれている通り、deleteとinsertをまとめて行おうとしているのですが、その順序が指定できずエラーになっているのだと思います。よって、順序が指定できないというのが、私が不可能と言った根拠になっています。

---

★良い回答には回答済みマークを付けよう！ MVP - .NET 　http://d.hatena.ne.jp/trapemiya/

Answer 8

> 手順３と４の間で保存ボタンを押せば問題が発生しないはずです。

そうですね、それがないですね。自分のほうが良く見てなくて、失礼しました。

Answer 9

質問者さん＞

先の私のレスで「TableAdapterManager による更新順序は関係なさそう」と書きましたけど、手順 (3) と (4) の間に「BindingNavigatorの保存ボタンで保存」の手順がない場合は関係おおありでした。勘違いレスですみませんでした。

Answer 10

>人が入力せずに、システムが自動的に振るキーを主キーにすれば、このようなことはなくなります。このようなキーをサロゲートキーと呼びます。

ありがとうございます。勉強になります。

列名はIDですが、最終的に行いたいのは人が入力可能なユニークキーで前述のようなエラーがでないようにしたかったのです。

おっしゃるとおり、サロゲートキーとしてオートナンバー列を追加して主キーに設定し自動付番にするとエラーは出ませんが、別の列にユニークキーを追加して同様に「行の削除・同じユニークキーで再度追加」のセットを２回実行したらエラーになりました。

System.Data.DBConcurrencyException はハンドルされませんでした。
  Message=同時実行違反 : DeleteCommand によって、処理予定の 1 レコードのうち 0 件が処理されました。

このエラーが出る理由はよくわかりません。

TableAdapterManagerを使って極力コーディングレスにして工数を少なくしようと思いましたが難しそうですね。

Answer 11

ありがとうございます。

再現用にID（重複不可）とDATA（重複可）二つの列だけの単純な表のmdbファイルを作って試していますので、インデックスやリレーションは関係なさそうです。

Answer 12

一番簡単な解決策は、手順 (3) と (4) の間に「BindingNavigatorの保存ボタンで保存」の手順を入れることですが、それではダメなんでしょうか？

Answer 13

＞一番簡単な解決策は、手順 (3) と (4) の間に「BindingNavigatorの保存ボタンで保存」の手順を入れることですが、それではダメなんでしょうか？

ありがとうございます。

バッチ処理ではなく、人が操作する画面ですので、難しいですね。

行削除のたびにコミット・保存することは避けたいです。削除はしたけど保存したくないケースもありえるためです。

Answer 14

手順 (3) と (4) の間に「BindingNavigatorの保存ボタンで保存」の手順を入れるのは NG、オートナンバーも NG ということですと、TableAdapterManager の使用は諦めて、更新順序を自力でコーディングするほかなさそうですね。

Answer 15

【追伸】

更新順序を自力でコーディングする方法ですが、ゼロから自分でコードを書かなくても、自動生成された TableAdapterManager は partial class として定義されているので、それを拡張する方向で検討した方がよさそうです。

自分の環境　(VS2010) では自動生成された UpdateAll メソッドには以下のように更新順序がコーディングされています。（質問者さんのケースではどうか分かりませんが同様では）

public virtual int UpdateAll(DataSet1 dataSet) {

    //・・・中略・・・

    // 
    //---- Perform updates -----------
    //
    if ((this.UpdateOrder == UpdateOrderOption.UpdateInsertDelete)) {
        result = (result + this.UpdateUpdatedRows(dataSet, allChangedRows, allAddedRows));
        result = (result + this.UpdateInsertedRows(dataSet, allAddedRows));
    }
    else {
        result = (result + this.UpdateInsertedRows(dataSet, allAddedRows));
        result = (result + this.UpdateUpdatedRows(dataSet, allChangedRows, allAddedRows));
    }
    result = (result + this.UpdateDeletedRows(dataSet, allChangedRows));

    //・・・中略・・・
}

もちろんそれを書き換えるのはダメですが、別にクラスファイルを追加して、そこで TableAdapterManager を拡張し、そこに自動生成された UpdateAll メソッドをコピペして、メドッソ名を例えば UpdateAll2 とかに変更し、上の更新順序の部分を自分の希望にあわせて書き換え、それを testTableBindingNavigatorSaveItem_Click ハンドラで UpdateAll に替えて使うようにしてはいかがでしょう？

（注）上記は案というレベルで未検証です。

Answer 16

Insert => Update => Delete または Update => Insert => Delete のいずれかしか更新順序を選べない理由は、Delete => Insert などという乱暴なことを許すと、ユーザーのミスで問題（DB の不整合など）が出る可能性が高くなるからではないかと思い初めています。

そもそも Update すればすむ話ですから。

質問者さんのケース「「行の削除・同じユニークキーで再度追加」でも Update ですむはずですよね？　Delete => Insert を許すと「同じ」にしたつもりがユーザーのミスで同じにならなかったということがありそうです。

という訳で、先の私のレスの「更新順序を自力でコーディング」は取り消させてください。