データベース操作プログラム Ⅱ VB 2005 9 プログラムの概要 オブジェクト型の変数宣言を行い コード上で データベースに接続し SQL を発行する 今回は オブジェクト型の変数宣言に依り データベースと接続する為のオブジェクト 及び 検索文を発行した際にデータを格納するオブジェクトを コード上で生成してデータベースに接続し 各レコードのフィールド情報を取得して 其れをコンボボックスの項目に追加する 此れに依り 選択項目を 手入力では無く コンボボックスのリストより選択する事が出来る様に成り 操作性が格段に向上する 亦 データ入力に依り 自動的に更新される可きデータを自動で更新する事が出来る様にする 即ち 此処では カテゴリ別のデータ数を 自動的に更新するを付加する 今回の課題項目 オブジェクト変数の宣言 (Private New) プロパティの利用 (CursorLocation ConnectionString RecordCount Fields EOF) プロパティの利用 (DataSource Tag Width Height ItemData) メソッドの利用 (Open Execute Close MoveNext Items.Add SubString) ステートメントの利用 (Call Structure) 組込関数の利用 (Val Space) 制御構造構文 ( ループ Do ~ Loop 条件分岐 Select Case End Select) サブルーチン ( 新規プロシージャ ) 今回の重点項目 オブジェクト型変数の宣言 (Private New) ADODB.Connection(Open Execute Close) ADODB.Recordset(Open Close MoveNext RecordCount Fields EOF) SQL 文 (SELECT 文 : レコードを検索する文 UPDATE 文 : レコードを更新する文 ) 今回の応用項目 様々な SQL 文を試してみる -1-
オブジェクト プロパティ一覧 ラベル1 ラベル2 ラベル3 ラベル4 ラベル5 テキストボックス1 テキストボックス2 テキストボックス3 コンボボックス1 コンボボックス2 ボタン 1 ボタン 3 ボタン 2 前回作成したプログラムを基に追加する コントロールの種類 プロパティ プロパティの設定値 ラベル1 Name lblid ID ラベル2 Name lbltitle タイトル ラベル3 Name lblprice 価格 ラベル4 Name lblcategory カテゴリ ラベル5 Name lblwriter 著者 テキストボックス1 Name txtid BackColor White ReadOnly True 空白 テキストボックス2 Name txttitle 空白 テキストボックス3 Name txtprice 空白 コンボボックス1 Name cbocategory 空白 -2-
コントロールの種類 プロパティ プロパティの設定値 コンボボックス2 Name cbowriter 空白 ボタン1 Name btninsert 追加 Tag 0 ボタン2 Name btnupdate 変更 Tag 1 ボタン3 Name btndelete 削除 Tag 2 ラベルテキストを表示するコントロール 此のテキストは ユーザーが直接入力したり変更したりする事は出来ない テキストボックステキストの入力や表示を行うコントロール 此のテキストは ユーザーが直接入力したり変更したりする事が出来る コンボボックス選択肢の一覧を表示するコントロール 此の一覧より値を選択する事と テキストボックスに直接値を入力する事が出来る ボタンユーザーが選択したコマンド ( 命令 ) や操作を実行するコントロール OK ボタンや実行ボタン等 処理の開始や中断 終了の合図に用いられ 最も良く利用されるコントロールです コントロールの Anchor プロパティを利用するとフォームのサイズ変更に合わせて コントロールの大きさを変える事が出来る フォームとの距離を一定に保つ方向を クリックして設定する 初期状態では 上方向と左方向が設定されて居るが 此れに 下方向と右方向を加えると フォームのサイズ変更に合わせて コントロールも大きさを変える事に成る VB6 でのコンボボックスの ItemData プロパティは廃止された VB6 での cbocategory.itemdata(i) = Val(Rs1.Fields("ID") & "") と同等の事を行うには Microsoft Visual Basic.NET Compatibility Runtime を参照設定した上で 下記の様に記述する Microsoft.VisualBasic.Compatibility.VB6.SetItemData(cboCategory, I, Val(Rs1.Fields("ID").Value & "")) -3-
Public Class bookshelf プログラムリスト ( 追加分 ) ' 構造体の宣言 追加 Private Structure Category Public ID As Integer Public Total As Integer End Structure ' 構造体型の動的配列の宣言 追加 Private Cate( ) As Category ' フォームレベルでグローバルな変数の宣言 Private Cn As ADODB.Connection Private Rs As ADODB.Recordset Structure ステートメントは 1 個又は複数の要素を持つデータ型を宣言する 通常 レコード内のフィールド定義に用いられる New キーワードを指定せずにオブジェクト変数を宣言した場合は宣言時に オブジェクトの新しいインスタンスは作成されない ' フォームが読み込まれた時の処理 Private Sub bookshelf_load( ByVal sender As Object, ByVal e As System.EventArgs ) _ Handles Me.Load ' コネクションとレコードセットのインスタンス生成 Cn = New ADODB.Connection Rs = New ADODB.Recordset ' コネクションの確立 ( データベースへの接続 ) Cn.CursorLocation = ADODB.CursorLocationEnum.adUseClient Cn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=data.mdb;" Cn.Open( ) ' 選択クエリに依るレコードセットの取得 Rs.Open( "SELECT 本.*, 著者. 著者名 FROM 本, 著者 WHERE 本. 著者 ID= 著者.ID;", Cn ) ' データグリッドへの連結 Da = New OleDb.OleDbDataAdapter() Ds = New DataSet() Da.Fill(Ds, Rs, "book") dgvbookshelf.datasource = Ds dgvbookshelf.datamember = "book" New キーワードを指定せずにオ ブジェクト変数を宣言した場合は 実行時に 必ずインスタンスは作 成する必要が有る ' ラジオボタンの設定 radbook.checked = True AddHandler radbook.click, AddressOf radbook_click AddHandler radcategory.click, AddressOf radbook_click AddHandler radwriter.click, AddressOf radbook_click ' サブルーチンコール 追加 Call CboAddItem( ) Call CateUpdate( ) End Sub 頻繁に行う処理は 名前を付け 一纏めに仕て置き 其の処理を必要とする箇所よりコール ( 呼出 ) する事で 対象のコードを実行する事が出来る -4-
' コンボボックスにアイテムを追加するジェネラルプロシージャ 追加 Private Sub CboAddItem( ) Dim Rs1 As New ADODB.Recordset( ) Dim SQL As String = "" Dim S As String = "" ' カテゴリ項目を取得しコンボボックスに設定データベースから Null を取得し SQL = "SELECT * FROM カテゴリ ;" た場合 文字列変数に代入する事 Rs1.Open( SQL, Cn ) は出来ないが 文字列と結合する If Rs1.RecordCount > 0 Then 事で文字列にキャストされ 文字列変数に代入する事が出来る Do Until Rs1.EOF S = Space( 3 ) & Rs1.Fields( "ID" ).Value S = S.Substring( S.Length 3 ) & ":" & Rs1.Fields( " カテゴリ名 " ).Value cbocategory.items.add( S ) Rs1.MoveNext( ) コンボボックスにリストを追加す Loop るには Items コレクションに対 End If して Add メソッドを使用する Rs1.Close( ) ' 著者項目を取得しコンボボックスに設定 SQL = "SELECT * FROM 著者 ;" 従来の様にコンボボックスに Rs1.Open( SQL, Cn ) ItemData に数値を格納する事が出来ない為 ID フィールドの値を If Rs1.RecordCount > 0 Then リストに付加して居る Do Until Rs1.EOF S = Space( 3 ) & Rs1.Fields( "ID" ).Value S = S.Substring( S.Length 3 ) & ":" & Rs1.Fields( " 著者名 " ).Value cbowriter.items.add( S ) Rs1.MoveNext( ) Loop データベースのレコードを取得し End If 格納する変数の様に配列の要素数 Rs1.Close( ) を事前に決定出来ない場合は 動 ' オブジェクトとの関連付けの解除 Rs1 = Nothing End Sub オブジェクト変数を宣言する時に New キーワードを指定して 宣言時に オブジェクトの新しいインスタンスが作成する事も出来る 的配列を使用し 要素数が判明し た時点で ReDim を使用して要素 数を指定する -5-
' カテゴリ別データ数を更新するジェネラルプロシージャ 追加 Private Sub CateUpdate( ) Dim Rs1 As New ADODB.Recordset Dim Rs2 As New ADODB.Recordset Dim SQL As String = "" Dim I As Integer オブジェクト変数を宣言する時に New キーワードを指定すると 実行時に オブジェクトへの参照を代入する必要は無い ' カテゴリ別データ数の更新数値型のフィールドデータを取得 SQL = "SELECT ID FROM カテゴリ ;" する際には Val 関数を使用し Rs1.Open( SQL, Cn ) 数値以外の不正なデータを少なく If Rs1.RecordCount > 0 Then ともゼロにする事が望ましい ReDim Cate( Rs1.RecordCount ) For I = 1 To Rs1.RecordCount Cate( I ).ID = Val( Rs1.Fields( "ID" ).Value & "" ) SQL = "SELECT COUNT( ID ) AS 合計 FROM 本 " & _ " WHERE カテゴリ ID=" & Cate( I ).ID & ";" rs2.open( SQL, Cn ) If rs2.recordcount > 0 Then Cate( I ).Total = Val( rs2.fields( " 合計 " ).Value & "" ) SQL = "UPDATE カテゴリ SET トータル =" & Cate(I).Total & _ " WHERE ID=" & Cate( I ).ID & ";" Cn.Execute( SQL ) 1 行分のレコードを処理しても自 End If 動的に次のレコードに移動する訳 rs2.close( ) では無い MoveNext メソッドで Rs1.MoveNext( ) 次のレコードに移動する Next End If Rs1.Close( ) オブジェクト型変数は使用終了し ' オブジェクトとの関連付けの解除 rs2 = Nothing Rs1 = Nothing End Sub た時点で Nothing を代入して関連 付けを解除しないと 使用して居 るメモリが開放されない -6-
VB 2005 9 64bit 環境用 VB 2005 基礎 Ⅱ noa 構造体とは ユーザー ( プログラマー ) が任意に定義するデータ型 Visual Basic がサポートする複数のデータ型を組み合わせて 独自のデータ型を作成する事が出来る Structure ユーザー定義型名適用範囲要素名 1 As データ型適用範囲要素名 2 As データ型 End Type 構造体は データ型の異なる多数の関連する要素で構成されるデータレコードを定義する場合に 良く使用される 適用範囲には Dim Event Friend Private Public の孰れかを指定する 猶 Dim キーワードで宣言されたメンバは 既定では Public アクセスを行う 宣言した構造体は Visual Basic の他のデータ型と粗同様に使用する事が出来る 構造体で宣言された変数は 複数の要素 ( フィールド ) を持つ事に成り 各要素の参照は 変数名の後ろにピリオドに続き要素名を記述する 構造体変数の宣言 :Dim 変数名 As ユーザー定義型 (Public Private でも宣言出来る ) 構造体変数の参照 : 代入変数 / プロパティ = ユーザー定義型変数名. 要素名 オブジェクト型変数の宣言 オブジェクトクラスに対して変数を宣言する Dim 変数名 As [New] オブジェクトクラス名 オブジェクトをコードで使用する為に 対象のタイプライブラリに依り提供されるクラス名を変数宣言の中で指定し オブジェクトを変数に格納する 宣言ステートメントには Dim の他にも Public Protected Friend Protected Friend Private Shared Static も使用する事が出来る データ型には オブジェクトクラス ( オブジェクトのインスタンスが生成されるクラス ) を指定するがプロシージャが実行される迄 オブジェクトクラスが解らない場合等は オブジェクト型 (Object) を使用してオブジェクト変数を宣言する事も出来る オブジェクト型 (Object) の変数は オブジェクトを参照する 32 ビット (4 バイト ) のアドレスと仕て格納される ( 即ち オブジェクト変数に格納した値は メモリ内の他の場所に保持され 変数自体はデータへのポインタを保持する ) オブジェクト型と仕て宣言した変数には 任意の参照型 ( 文字列 配列 クラス インターフェイス ) を割り当てる事が出来る New キーワードを使用してオブジェクト変数を宣言すると クラスのインスタンスが宣言時に作成されるが 整数型や倍精度浮動小数点型等の値型とは異なり オブジェクトは参照型で有り 使用する前に明示的にインスタンスを作成して置く必要が有る -7-
ADODB.Connection オブジェクト データベースと接続する為のオブジェクト Connection オブジェクトは データソースへの接続を表し データソースとの固有のセッションを表す クライアントサーバーデータベースシステムの場合 此のオブジェクトはサーバーへの実際のネットワークに対応する ADODB.Connection の Open メソッド データソースへの接続を開くメソッド Connection.Open( 引数 1, 引数 2, 引数 3, 引数 4 ) データソースへの物理的な接続を設定する 此のメソッドが完了すると接続状態が確立し コマンドを発行して結果を処理する事が出来る 引数 1 には 接続情報 (ConnectionString) を指定し 省略する事が出来る 引数 2 には 接続を確立する際に必要なユーザー名を指定し 省略する事が出来る 引数 3 には 接続を確立する際に必要なパスワードを指定し 省略する事が出来る 引数 4 には 同期接続 非同期接続の孰れかを示す定数を指定し 省略する事が出来る ADODB.Connection の Execute メソッド 指定されたクエリ SQL ステートメント ストアドプロシージャ等を実行するメソッド Connection.Execute( 引数 1, 引数 2, 引数 3 ) 指定した接続上で 引数 1 で指定されたメソッドに渡すクエリが実行される 引数 1 には 実行する SQL ステートメント テーブル名等を指定し 必ず指定する 引数 2 には プロバイダが返す操作の結果を反映するレコード数を指定し 省略する事が出来る 引数 3 には プロバイダが引数 1 を評価する方法を示す定数を指定し 省略する事が出来る ADODB.Connection の Close メソッド 開いて居るオブジェクト 及び 依存する総てのオブジェクトを閉じるメソッド Connection.Close( ) Connection オブジェクト 又は Recordset オブジェクトを閉じて 関連するシステムリソースを解放する オブジェクトを閉じてもメモリからは削除されず プロパティ設定を変更してもう一度開く事が出来る メモリからオブジェクトを完全に削除するには オブジェクト変数を Nothing に設定する -8-
ADODB.Recordset オブジェクト 検索文を発行した際にデータを格納する為のオブジェクト Recordset オブジェクトは 基に成るテーブルからのレコードセット全体 又は コマンドの実行に依り返された結果のレコードを表し 常にレコードセット内の1つのレコード而巳をカレントレコードと仕て参照する 亦 Recordset オブジェクトは レコード ( 行 ) とフィールド ( 列 ) で構成される Recordset オブジェクトは必要な丈 作成する事が出来る ADODB.Recordset の Open メソッド カーソルを開くメソッド Recordset.Open( 引数 1, 引数 2, 引数 3, 引数 4, 引数 5 ) ベーステーブルからのレコード クエリ結果 又は 以前に保存された Recordset を表すカーソルを開く事が出来る 引数 1 には 実行する SQL ステートメント テーブル名等を指定し 省略する事が出来る 引数 2 には 有効な Connection オブジェクト変数名を指定し 省略する事が出来る 引数 3 には Recordset を開く際にプロバイダが使うカーソルタイプを指定し 省略する事が出来る 引数 4 には Recordset を開く際にプロバイダが使うロックの種類を指定し 省略する事が出来る 引数 5 には プロバイダが引数 1 を評価する方法を示す定数を指定し 省略する事が出来る ADODB.Recordset の MoveNext メソッド 指定の Recordset オブジェクトの次のレコードに移動して 其のレコードをカレントレコードにするメソッド Recordset.MoveNext( ) カレントレコードの位置を Recordset の末尾に向かい 1 レコード進める カレントレコードが最後のレコードの場合に MoveNext メソッドを呼び出すと カレントレコードが Recordset の最後のレコードの後に設定され EOF が True に成る EOF プロパティが既に True の場合 前方へ移動しようとするとエラーが発生する カレントレコードの位置を変更するには 他に下記のメソッドが有る MoveFirst Recordset の最初のレコードに移動する MoveLast Recordset の最後のレコードに移動する MovePrevious Recordset の先頭に向かい1レコード戻る 前方スクロール而巳可能なレコードセットで両方向スクロールのサポートが必要な場合 CacheSize プロパティを使用してカーソルの後方スクロールをサポートするレコードキャッシュを作成し Move メソッドを使用して移動する事が出来る キャッシュされたレコードはメモリにロードされる為 必要以上のレコードのキャッシュは避ける可きで有る 前方スクロール而巳可能なレコードセットで MoveFirst メソッドを呼び出す事は出来るが 其の結果 Recordset オブジェクトを生成するコマンドをプロバイダが再度実行する可能性が有る -9-
ADODB.Recordset の Close メソッド 開いて居るオブジェクト 及び 依存する総てのオブジェクトを閉じるメソッド Recordset.Close( ) Connection オブジェクト 又は Recordset オブジェクトを閉じて 関連するシステムリソースを解放する オブジェクトを閉じてもメモリからは削除されず プロパティ設定を変更してもう一度開く事が出来る メモリからオブジェクトを完全に削除するには オブジェクト変数を Nothing に設定する ADODB.Recordset の RecordCount プロパティ Recordset オブジェクトに存在する現在のレコード数を示すプロパティ Recordset.RecordCount Recordset オブジェクトに有るレコード数を調べる為に使用する レコード数が解ら無い場合 又は プロバイダやカーソルの種類が RecordCount をサポートして居ない場合は プロパティは-1を返す 閉じて居る Recordset 上で RecordCount プロパティを読み込むと エラーが発生する ADODB.Recordset の Fields プロパティ フィールドデータを示すプロパティ ( コレクション ) Recordset.Fields( 引数 ) 引数で指定したフィールドを示す 当該フィールドデータの値は Value プロパティで参照する事が出来る 引数には フィールド名を示す文字列 又は 序数を指定し 必ず指定する ADODB.Recordset の EOF プロパティ カレントレコードの位置が最後のレコードより後に有る事を示すプロパティ Recordset.EOF カレントレコードの位置が最後のレコードより後に有る場合は True を返し カレントレコードの位置が最後のレコード上 又は 其れ以前に有る場合は False を返す 猶 BOF プロパティは カレントレコードの位置が最初のレコードより前に有る場合は True を返し カレントレコードの位置が最初のレコード上 又は 其れ以降にある場合は False を返す BOF 及び EOF プロパティは Recordset オブジェクトがレコードを含むか何うか 又は 他のレコードに移動した時に Recordset オブジェクトの限界を超えて居ないか何うかを調べる為に使用する -10-
ADO では次の 4 種類のカーソルが定義されて居る 1. 動的カーソル : 他のユーザーに依る追加 変更 及び 削除を見る事が出来 ブックマークに依存しない Recordset での所有る種類の移動を行う事が出来る プロバイダがブックマークをサポートする場合は ブックマークを使用する事が出来る 2. キーセットカーソル : 動的カーソルと同じ様な属性を持つが 他のユーザーが追加したレコードを表示せず 他のユーザーが削除したレコードへのアクセスを許可し無い 他のユーザーが変更したデータは表示する事が出来る 常にブックマークが使用出来る為 Recordset での所有る種類の移動が可能で有る 3. 静的カーソル : データの検索 又は レポートの作成に使用する為の レコードセットの静的コピーを作成する 常にブックマークが使用出来る為 Recordset での所有る種類の移動が可能で有る 他のユーザーに依る追加 変更 又は 削除を見る事は出来ない クライアント側 (ADOR) の Recordset オブジェクトを開く場合に許可される唯一のカーソルタイプで有る 4. 前方スクロールタイプカーソル : レコードの前方スクロールのみ可能で有る点を除き 静的カーソルと同じ属性を持つ レコードセットを一度だけスクロールする必要が有る場合は 此のカーソルに依り性能が向上する Recordset オブジェクトのカーソルタイプを選択するには Recordset オブジェクトのカーソルタイプを選択するには Recordset オブジェクトを開く前に CursorType プロパティを設定する 亦 Open メソッドを使って CursorType 引数を引き渡す事も出来る プロバイダの中には 総てのカーソルタイプをサポートして居ない物も有る プロバイダのドキュメントを参照して確認する必要が有る カーソルタイプを指定し無い場合 既定値に依り前方スクロールタイプカーソルが開く Recordset オブジェクトのカーソルロケーションに付いて Recordset オブジェクトを開く為に CursorLocation プロパティを aduseclient に設定すると Field オブジェクト上の UnderlyingValue プロパティは 返された Recordset オブジェクトでは無効に成る Microsoft SQL Server と連動する Microsoft ODBC Provider for OLE DB 等のプロバイダを利用する場合 Open メソッドを使用して接続文字列を引き渡す事に依り 定義済みの Connection オブジェクトから独立した Recordset オブジェクトを作成する事が出来る 此の場合も Connection オブジェクトが作成されるが オブジェクトの値は変数に代入され無い 但し 同じ接続で複数の Recordset オブジェクトを開く場合は Connection オブジェクトを作成して開く必要が有る 此の操作に依り Connection オブジェクトの値がオブジェクト変数に代入される Recordset オブジェクトを開く時に此のオブジェクト変数を使用しない場合は ADO は 同じ接続文字列を引き渡しても 新規 Recordset オブジェクトの夫々れに対して新規 Connection オブジェクトを作成する Recordset オブジェクトを開くと カレントレコードが最初のレコードの位置に移動して BOF と EOF プロパティの設定値が False に成る レコードが存在しない場合は BOF と EOF プロパティの設定値が True に成る ConnectionString プロパティ (ADODC) 直訳すると 接続文字列 ダイアログボックスにて視覚的に設定する事が多いが 1 字 1 句間違わなければ 直接入力しても設定する事が出来る 要は 何のデータベースと接続するのかを指定するプロパティで有る 若し フォルダの名称を変更したり ファイル名を変更したり 保存場所を変更する等して 設定した時と異なる場合は 再度設定し直す必要が有る RecordSource プロパティ (ADODC) 指定した検索文を元に ADODC オブジェクトは検索結果データを取得する データグリッドは ADODC オブジェクト内に取得したデータを表示する Refresh メソッド (ADODC) RecordSource プロパティの指定のみでは 検索結果データは ADODC に取得され無い Refresh メソッドを実行する事に依り データベース側の最新データを取得する事が出来る VB での SQL 文の扱い VB にて SQL 文を記述する際には 文字列 ( ダブルクオーテーション区切り ) と成る データベースソフトに送られた時点で 文字列は SQL 文で有ると看做され 実行される -11-
コード上で SQL 文を実行するには コード上で SQL 文を実行するには コード上で 以下のオブジェクトを使用する ADODB.Connection オブジェクト ( データベースと接続する為のオブジェクト ) ADODB.Recordset オブジェクト ( 検索文を発行した際にデータを格納するオブジェクト ) オブジェクトをコードで使用するには 変数に格納して使用する必要が有る 此れをオブジェクト型の変数宣言と謂う Dim 変数名 1 As New ADODB.Connection Dim 変数名 2 As New ADODB.Recordset (SELECT 文で無い場合は不要 ) INSERT UPDATE DELETE 文を実行する場合は ADODB.Connection オブジェクトの Open( 開く ) Execute( 実行 ) Close( 閉じる ) メソッドを使用する 変数名 1.Open( データベースの種類 データベースの場所を示す接続文字列 ) 変数名 1.Execute( SQL 文 ) 変数名 1.Close( ) SELECT 文の場合は検索結果を取得するので ADODB.Recordset オブジェクトに SQL 文を指定する 変数名 2.Open( SQL 文, 変数名 1 ) RecordCount プロパティにて検索結果の件数を取得可 Fields コレクションの Value プロパティにて指定したフィールドデータを取得可 MoveNext メソッドにて検索結果のレコードカーソルを移動可 変数名 2.Close( ) ADODB.Recordset の EOF プロパティ レコードデータを参照する際には カレントレコードを移動する必要が有る Open メソッド実行直後は検索結果の 1 番目のレコードがカレントレコードと成る MoveNext メソッドを実行する事に依り 1 件ずつカーソルを移動する事が出来る EOF プロパティは最終レコードの下の意味で 此れ以上はデータが無い 状況を謂う 実行直後は 1 件目にカーソルが有る イメージ ( 検索結果データ ) ID タイトル 1 ACCESS テクニック集 3000 2 EXCEL テクニック集 2500 MoveNext メソッドを実行するとカーソルが 1 行宛下へ移動する カーソルが 1 番下のレコードより下に移動すると EOF=True と成る 8 実用 WORD 1300-12-