VB2010でAccess2003に接続後、データの更新
解決最近VB6からVisualBasic2010へ乗り換えました。
OSはXP で、Access2003を使用しています。
下記の構文で、Accessへ接続はできましたが、While 〜 End Whileの間で、
フィールドの値（整数型）を1行ずつ読み込む所まではできたのですが、読み込んだ値に数値を加えて更新したいと考えています。
dim cn As New System.Data.OleDb.OleDbConnection
dim cmd As System.Data.OleDb.OleDbCommand
dim rs As System.Data.OleDb.OleDbDataReader
cn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & **.mdb" & ";"
cmd = cn.CreateCommand
cmd.CommandText = SQL構文
cn.Open()
rs = cmd.ExecuteReader()
While rs.Read() = True
Z1 = rs("フィールド名").ToString() ←フィールドの値を取得
＜ここでZ1に数値を加えて、更新処理を行いたい＞
End While
初心者でADO と ADO.NETの違いに戸惑っています。よろしくお願いします。
> 初心者でADO と ADO.NETの違いに戸惑っています。よろしくお願いします。
従来の DAO、ADODB、RDO 等とは異なり、ADO.NET では
「スクロールと更新が可能なサーバー側カーソル」はサポートされていません。
.NET データプロバイダによっては、個別に実装されている場合もありますが、
少なくとも System.Data.OleDb 名前空間のものはそうではありません。
> フィールドの値（整数型）を1行ずつ読み込む所まではできたのですが、
> 読み込んだ値に数値を加えて更新したいと考えています。
数値を加えるだけならば、ループ処理せずとも UPDATE の SQL で
一括更新できそうですが、実際にはもう少し複雑な処理なのでしょうか。
> 下記の構文で、Accessへ接続はできましたが、While 〜 End Whileの間で、
OleDbDataReader として開いたカーソルは読み込み専用であり、
ADO の時のように行単位で更新処理していくようなことはできません。
一行ずつ更新結果を反映させていくのではなく、
下記のような流れで処理することを検討してみてください。
(1) DataAdapter の Fill メソッドを呼び出して、SELECT 結果の全件を DataSet に格納する。
(2) DataSet/DataTable の内容を編集する(行の追加、編集、削除)。
(3) それを DataAdapter の Update メソッドに渡して、データベースに反映させる。
大雑把な処理イメージを書いてみます。
'SELECT 句には主キーを含めておくこと
Dim da As New OleDbDataAdapter(strConn, SQL)
'下記により、INSERT/DELETE/UPDATE に必要な SQL が DataAdapter に埋め込まれる
Dim cb As New OleDbCommandBuilder(da)
' da.SelectCommand … SELECT クエリー
' da.InsertCommand … INSERT クエリー
' da.DeleteCommand … DELETE クエリー
' da.UpdateCommand … UPDATE クエリー
'データを取得
Dim table As New DataTable()
da.Fill(table)
'ループ処理等を用いて、行更新・行追加・行削除等を実施
table.Rows(行番号)(列名) = 新しい値
'編集結果を反映
da.Update(table)
------------
ちなみに、ADO.NET での更新処理手順を VB6 の ADODB で置き換えると
このような感じになります。
(1) adUseClient, adOpenStatic, adBatchOptimistic な Recordset で開きます。
これにより、SELECT 結果全件が Recordset 内にキャッシュされます。
(ADO.NET では、DataAdapter.Fill などがこれに相当します)
(2) Recordset の ActiveConnection に Nothing を Set します。
これにより、Recordset がオフラインモードとなります。
(ADO.NET の DataTable というのは、この状態の Recordset に近い存在です)
(3) データベースに接続されていない状態のまま、Recordset の内容を編集する
(ADO.NET では、DataSet / DataTable の内容を編集することに相当)
(4) Recordset の ActiveConnection を再度繋ぎなおし、UpdateBatch を呼び出して反映させる
(ADO.NET では、DataAdapter の Update メソッド呼び出しに相当します)
http://msdn.microsoft.com/ja-jp/library/dd297827.aspx
http://msdn.microsoft.com/ja-jp/library/aa302325.aspx
http://code.msdn.microsoft.com/DataAccess-howto-59dfd086
魔界の仮面弁士さんのアドバイスとネットなど調べて以下の構文を作成したのですが、「位置0には行がありません」とエラーになります。
Public OleConn As OleDb.OleDbConnection = New OleDb.OleDbConnection
Public OleDA As New OleDbDataAdapter(OleConn.ConnectionString, strSQL)
Public dtSet As DataSet = New DataSet("TABLE")
Public dtTable As DataTable
Public dtRow As DataRow
Public cb As New OleDbCommandBuilder(OleDA)
Public table As New DataTable()
OleConn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" &
"Data Source="D:\Kanri.mdb" & ";"
OleDA = New OleDb.OleDbDataAdapter(strSQL, OleConn)
OleDA.Fill(dtSet, "TABLE")
dtTable = dtSet.Tables("TABLE")
For i = 0 To 10 ’ループ処理
dtRow = dtTable.Rows(i)
table.Rows(i)("列名") = "○○" ←ここでエラーになっている
OleDA.Update(table)
Next i
いろんなサイトを参考に作ってみました。ループ処理の中で、
Z1(string変数）= dtRow("列名") としたところデータの取得はできて いたみたいです。
あと、ＳＱＬで得られたレコード数も取得したいのですが、どうすればいいのでしょうか？よろしくお願いいたします。
さきほどの投稿よりちょっと修正しました。
Public ConnectionString As String
Public strSQL As String 'SQL構文
Public OleConn As OleDb.OleDbConnection = New OleDb.OleDbConnection
Public OleDA As New OleDbDataAdapter(strSQL, ConnectionString)
Public dtSet As DataSet = New DataSet("TABLE")
Public cb As New OleDbCommandBuilder(OleDA)
Public table As New DataTable()
OleConn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & MainD + "Sien2\MASTER\Kanri.mdb" & ";"
OleDA = New OleDb.OleDbDataAdapter(strSQL, OleConn)
OleDA.Fill(table)
For i = 0 To 10
table.Rows(i)("列名") = "**"
OleDA.Update(table) ←エラー発生
Next (i)
以下のエラーになりました。
「更新には、変更された行を含むDataRow コレクションが渡されたとき、 有効なUpdate Commandが必要です。」
これはどういった意味のエラーなのでしょうか？
よろしくお願いいたします。
何度も連続書き込みすみません。
「更新には、変更された行を含むDataRow コレクションが渡されたとき、 有効なUpdate Commandが必要です。」
について調べてみると、まず主キーの設定が必要とのことで、設定していなかったので、主キーを設定しましたが、結果は同じでした。
参考までに、ADO.NETの場合主キー設定は必須なのでしょうか？ADOのときは主キーなしでプログラムを組んでいました。
>「位置0には行がありません」とエラーになります。
DataTable に取得した内容が期待通りになっているか
もう一度確認してみてください。
DataTable に対して Fill した後、処理を一時停止して
DataTable 変数の上にマウスカーソルを重ねてみましょう。
虫眼鏡のアイコンが表示されると思いますが、それを使うと、
DataSet/DataTable の内容をグリッド表示で閲覧する事ができます。
> ＳＱＬで得られたレコード数も取得したいのですが
ADO でいうところの (静的カーソルの)Recordset.RecourdCount と
同じもので良ければ、DataTable の .Rows.Count を利用できます。
> For i = 0 To 10
> table.Rows(i)("列名") = "**"
> OleDA.Update(table) ←エラー発生
> Next (i)
Update はループの外に出しましょう。
一行ずつ更新するのではなく、まとめて更新処理をかけます。
> 「更新には、変更された行を含むDataRow コレクションが渡されたとき、 有効なUpdate Commandが必要です。」
OleDbDataAdapter には、
SelectCommand
DeleteCommand
InsertCommand
UpdateCommand
というプロパティがあります。
それぞれ、取得・削除・追加・更新のための SQL を保持するものですが、
現在のコードでは、SELECT クエリーしか指定されていません。
つまり、UpdateCommand プロパティが空の状態で実行したため、
更新処理が失敗したという事です。なので、これを用意してやりましょう。
UpdateCommand プロパティは自分でセットしていくこともできますが、
簡単なのは「OleDbCommandBuilder」を使う事です。
確かに先のコードでも OleDbCommandBuilder は宣言されているのですが、
あれでは機能していません。ちゃまきさんのコードを解説つきで抜粋しますね。
------------------
'ここで、DataAdapter を生成しています。
'この状態では、SelectComand は作られますが、UpdateCommand はありません。
Public OleDA As New OleDbDataAdapter(strSQL, ConnectionString)
'ここで、UpdateCommand 等が作成されます。
Public cb As New OleDbCommandBuilder(OleDA)
'ここで何故か、接続文字列を再セットしています。
'最初に New した時に、ConnectionString はセットしておいたはずですよね?
OleConn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & MainD + "Sien2\MASTER\Kanri.mdb" & ";"
'そしてさらにここで、もう一度 DataAdapter を再生成しています。
'先ほど作成してあった UpdateCommand 入りの OleDA は破棄され、
'新たに、UpdateCommand の無い OleDA が再度作り直されたことになります。
OleDA = New OleDb.OleDbDataAdapter(strSQL, OleConn)
'そしてそのまま、UpdateCommand 無しに更新処理を実施しています。
OleDA.Fill(table)
--------------------
OleDbCommandBuilder を使った後、UpdateCommand プロパティの内容が
どのようになっているか、確認してみてください。
> 調べてみると、まず主キーの設定が必要とのことで
ですます。先にも書いていますね。
>> 'SELECT 句には主キーを含めておくこと
> ADOのときは主キーなしでプログラムを組んでいました。
たとえば、キーセットカーソル(adUseServer + adOpenKeyset)などでは
そういったことも可能でしょう。
しかし ADO.NET の DataAdatper 経由での更新作業は、
>> adUseClient, adOpenStatic, adBatchOptimistic な Recordset
に相当する動作です。(ADO に関する知識はあるものという前提で回答しています)
繰り返しになりますが、
>> 従来の DAO、ADODB、RDO 等とは異なり、ADO.NET では
>> 「スクロールと更新が可能なサーバー側カーソル」はサポートされていません。
であるということを覚えておいてください。
もしもサーバーカーソルとして開かれているのであれば、主キー無しでも
「現在の行を更新する」ことは可能でしょうが、DataTable 自体は
データベースとの接続を保持していないため、それはできません。
(DataTable とデータベースを繋ぐのは、OleDbCommand や OleDbDataAdapter の仕事です)
そのため更新時には、どの行を変更すれば良いのかを特定するために、
主キー／ユニークキー／行バージョン(TimeStamp/RowVersrion/ROWID等)が必要になります。
ピンと来ないと思うので、もう少し細かい話をすると…
DataTable の各行には、「編集前の値と編集後の値(DataRowVersion)」と、
「現在の行の状態(RowState)」が管理されています。RowState とは、
各行が 未編集の行/変更された行/新規追加行/削除された行の
いずれであるかを表しています。
(ちなみに Recordset も、これと同様に変更前後の値を保持しています)
DataAdapter は、それらの編集状況を察知し、各行のデータを一行分ずつ
UpdateCommand / DeleteCommand / InsertCommand の
パラメータクエリーに渡すという働きをします。
この時の UpdateCommand の内容は、たとえば以下のようになります。
(UserId が主キーです)
UPDATE UserList
Set UserId = [変更後のUserId]
, UserName = [変更後のUserName]
, EMail = [変更後のEMail]
, Birthday = [変更後のBirthday]
WHERE UserId = [取得時点のUserId]
AND UserName = [取得時点のUserName]
AND EMail = [取得時点のEMail]
AND Birthday = [取得時点のBirthday]
なお、同時更新を検出する必要が無く、主キー以外は
後書き優先で更新して良い場合には、下記のような
単純化した SQL を割り当てることもできます。
UPDATE UserList
Set UserName = [変更後のUserName]
, EMail = [変更後のEMail]
, Birthday = [変更後のBirthday]
WHERE UserId = [取得時点のUserId]
この行単位の更新の時に、主キー(あるいはそれに相当する情報)が
必要になるというわけです。
魔界の仮面弁士様、ありがとうございます。
指摘の箇所を修正し、無事更新できました。
感謝します。またいろいろ調べてもわからない問題があれば教えてください。
| ツイート |  |