データベース千夜一夜第17回

プログラミングとSQL(5)~販売データの入力処理(後編) 長谷川裕行
有限会社 手國堂

3.1件の追加

商品を特定して数量を入力したら、[追加]ボタンをクリックして、品名と数量、合計金額をテーブル「売上明細」に1件のレコードとして追加します。その後、画面下部のグリッドに受注した商品の明細を表示します。


- 1件分の情報をグリッドに表示~btnAdd_Click -

1件分の受注情報(商品と数量の特定→合計額の表示)を入力し終えたら、[追加]ボタン(btnAdd)をクリックしてこの受注情報(売上明細)をテーブル「売上明細」に1件のレコードとして追加します。さらに、フォーム下部のグリッド(DataGrid)にも同じ情報表示します。

レコードの追加はAddDetail、グリッドへの表示はUpdateGridという下請けプロシージャを呼び出して実行します。

最後に、次の受注情報を入力するため、前回紹介した下請けのSubプロシージャ“ClearSlipData”を呼び出します。

  Private Sub btnAdd_Click _
   (ByVal sender As System.Object, ByVal e As System.EventArgs) _
   Handles btnAdd.Click
    ↓テーブルに1件分の受注情報(売上明細)を追加
    AddDetail()
    ↓受注情報をグリッドに表示
    UpdateGrid()
    ↓販売情報を初期化
    ClearSlipData()
  End Sub


- 明細をテーブルに追加~AddDetail -

[追加]ボタンをクリックしたときに呼び出されるプロシージャ“btnAdd_Click”から呼び出され、1件分の明細(受注情報)を1件のレコードとしてテーブル「売上明細」に追加します。

このプロシージャはテーブルへの書き込みを行うため、万一に備えて書き込みに成功したらTrue、失敗すればFalseを返すFunctionプロシージャとします。戻り値の型はBooleanです。

レコードを新規に追加するので、SQL文にはINSERT INTO命令を用います。VALUES句では、

  strSlipNumber(伝票番号)
  txtItemId.Text(商品ID)
  SlipData.intNumItems(数量)
  SlipData.intItemValue(販売単価)
  SlipData.lngTotalValue(合計額=数量×販売単価)

の各値を列挙します。

INSERT INTO命令などデータベースに変更を与えるSQLを実行するには、CommandオブジェクトのExecuteNonQueryメソッドを使います。メソッドが成功すれば変更された(この例では追加された)レコードの件数が戻ってくるので、その値が1以上あれば処理に成功したと見なしてTrueを、そうでなければ(0以下なら)失敗と見なしてFalseを返すようにします。

  Private Function AddDetail() As Boolean
    Dim objConnect As New SqlConnection
    Dim objCommand As New SqlCommand
    Dim strSql As String

    ↓レコード追加用のSQL
    strSql = _
    "INSERT INTO 売上明細 " & _
    "(伝票番号, 商品ID, 数量, 単価, 金額) " & _
    "VALUES(" & _
    strSlipNumber & ", " & _
    txtItemId.Text & ", " & _
    SlipData.intNumItems & ", " & _
    SlipData.intItemValue & ", " & _
    SlipData.lngTotalValue & _
    ")"

    Try
          : (接続の設定などは省略)
      objConnect.Open()
      objCommand.CommandText = strSql
      ↓レコードを1件追加
      If objCommand.ExecuteNonQuery >= 1 Then
        AddDetail = True
      Else ---- 失敗したらFaleseを返す
        AddDetail = False
      End If
          : (例外処理などは省略)
    End Try
  End Function


- DataGridに売上明細を表示~UpdateGrid -

[追加]ボタンをクリックしたときに呼び出されるプロシージャ“btnAdd_Click”から呼び出され、1件分の明細(受注情報)をDataGrid(データグリッド)“grdSlipData”の1行に表示します。

DataGridは単純な表のインターフェイスではなく、テーブルと結びついてそのレコード群をグリッドに表示します。ここでグリッドに表示するべきデータは、テーブル「売上明細」に記録されたレコードです。が、「売上明細」には現在処理している顧客から受注したデータだけではなく、それまでに受注したデータもレコードとして記録されています。

従って、「受注明細」に記録されているレコード群の中から「現在処理している顧客から受注したデータ」だけを抜き出してこなければなりません。そのためには、伝票番号をキーにしてWHERE句でレコードを抽出する必要があります。

現在処理中の伝票番号は、グローバル変数strSlipNumberに記録されているので、SQL文は以下のような形になります。

  "SELECT
  売上明細.商品ID, 商品_mr.品名, 売上明細.数量,
  売上明細.単価, 売上明細.金額
  FROM 商品_mr INNER JOIN 売上明細 ON 商品_mr.商品ID = 売上明細.商品ID
  WHERE 売上明細.伝票番号 =" & strSlipNumber

DataGridに値を表示させる方法は何通りかありますが、ここではDataAdapterオブジェクトとDataSetオブジェクトを使った一般的な手法を採用します。

DataAdpterオブジェクトはConnectionオブジェクトと同じように、対象となるデータベース・エンジンごとに用意されています。ここではSQL Server用に特化されたSqlDataAdapterを使います。

DataSetオブジェクトは、旧ADOのRecordsetオブジェクトを機能拡張したもので、指定したSQLの処理結果となるデータセット(レコードセット)を保持します。この処理には、DataAdapterのFillメソッドを使います。

DataSetオブジェクトの保持するデータセットは、DataGridオブジェクトのSetDataBindingメソッドでDataGridに値として表示されます。

ここまでの手順を整理しておきましょう。

(1)DataAdapterに接続文字列を設定

DataAdapterのコンストラクタ(インスタンス生成時に呼び出されるPrivateプロシージャ)を、SQL文字列と接続文字列を引数にして呼び出します。コンストラクタの呼び出しにはNew演算子を用います。

SQL文字列は先に紹介した「伝票番号をキーにレコードを抽出する」もので、接続文字列はConnectionオブジェクトに設定するものとほとんど同じです。ここでは、オブジェクト変数objSqlAdptにその結果(生成されたSqlDataAdapterオブジェクトへの参照)を代入します。

  objSqlAdpt = New SqlDataAdapter(strSql, _
   "Persist Security Info=False;Integrated Security=SSPI;database=db1001ya;server=localhost")

(2)DataAdapterのFillメソッドを実行

DataSetオブジェクトと生成されるデータセットの識別名(任意の文字列)を引数に与えてFillメソッドを呼び出すと、DataSetに保持されているデータセット(SQLの処理結果)を識別名で操作できるようになります。

ここでは、DataSetオブジェクトを“dsSlipData”、識別名を“SlipData”としています。

  objSqlAdpt.Fill(dsSlipData, "SlipData")

(3)データセットをグリッドに連結

こうして生成されたデータセットをDataGridに表示するには、DataGridオブジェクトのSetDataBindingメソッドを実行します。引数にはDataSetオブジェクトと先にデータセットに与えた識別名を設定します。

DataGridは“grdSlipData”という名前なので、処理は以下のようになります。

  grdSlipData.SetDataBinding(dsSlipData, "SlipData")

DataGridのCaptionTextプロパティには、グリッド上部の説明文を設定できます。

  grdSlipData.CaptionText = Customer.strName & "様の売上明細"

グリッドに処理中の受注情報(テーブル「売上明細」に記録された情報)を表示したら、これまでの総合計額(現在処理している受注データの総合計金額)を計算して表示します。この処理は“DispTotal”という下請けプロシジャに委ねます。

これで、ソースコードは以下のようになります。

  Private Sub UpdateGrid()
    Dim objSqlAdpt As SqlDataAdapter
    Dim dsSlipData As New DataSet
    Dim strSql As String

    ↓「売上明細」から処理中の伝票番号のレコードだけを抽出
    strSql =
     "SELECT 売上明細.商品ID, 商品_mr.品名, " & _
     "売上明細.数量, 売上明細.単価, 売上明細.金額 " & _
     "FROM 商品_mr INNER JOIN 売上明細 ON " & _
     "商品_mr.商品ID = 売上明細.商品ID " & _
     "WHERE 売上明細.伝票番号 = " & strSlipNumber

    Try
      objSqlAdpt = New SqlDataAdapter(strSql, _
      "Persist Security Info=False;Integrated Security=SSPI;database=db1001ya;server=localhost")
      objSqlAdpt.Fill(dsSlipData, "SlipData")
      ↓グリッドにデータセットを連結
      grdSlipData.SetDataBinding(dsSlipData, "SlipData")
      ↓グリッドのキャプションを表示
      grdSlipData.CaptionText = Customer.strName & "様の売上明細"
      ' 総合計金額を表示
      DispTotal()
         : 例外処理などは省略
    End Try
  End Sub


- 売上の総合計金額を表示~DispTotal -

DataGridに受注情報を表示するプロシージャ“UpdateGrid”の処理の最後で呼び出され、現在処理中の受注情報の合計金額を計算して、ラベル“lblGndTotal”に表示します。

「現在処理中の受注情報の合計」とは、グリッドに表示されている受注情報の「合計額」を合算した総合計で、先ほどの処理と同様テーブル「売上明細」から伝票番号をキーにレコードを抽出し、「金額」フィールドの値を合算すれば取得できます。

この処理のためには、SUM関数を使ったSELECT命令が必要です。SUM関数は集計関数と呼ばれる特別な機能のひとつで、まだ詳しく紹介していません。そのため、ここでは

  SUM(<フィールド名1>) AS <フィールド名2>

とすれば、<フィールド名1>で示したフィールドの値を合算し、その結果に<フィールド名2>というフィールド名が与えられる――ということだけ知っておいてください。集計関数については、追って詳しく紹介します。

ここで必要な

  テーブル「売上明細」から伝票番号をキーにレコードを抽出し
  「金額」フィールドの値を合算して
  フィールド名を「総合計」する

という処理では、以下のようなSQLを記述します。

  "SELECT SUM(金額) AS 総合計 " & _
  "FROM 売上明細 WHERE 伝票番号 = " & strSlipNumber

集計関数を使った場合も基本命令はSELECTなので、CommandオブジェクトのExecuteReaderメソッドで実行し、結果をDataReaderオブジェクトに受け取ります。結果は「総合計」フィールドの値として受け取られるため、DataReaderにフィールド名を与えて読み出せます。

このとき、受注情報がまだ1件も入力されていない状態だと、「合計」フィールドの値がNULLとなっているため集計結果もNULL(DBNull)が返ってきます。よって、既に紹介したDBNull対策を施しておきます。

DataReaderオブジェクトを“objDataReader”という名前とすると、この部分の処理は以下のようになります。

  If objDataReader.Read() Then
   If Not IsDBNull(objDataReader("総合計")) Then
    lblGndTotal.Text = Format(objDataReader("総合計"), "#,##0")
   Else
    lblGndTotal.Text = "0"
   End If
  End If

  Private Sub DispTotal()
    Dim objConnect As New SqlConnection
    Dim objCommand As New SqlCommand
    Dim objDataReader As SqlDataReader
    Dim strSql As String

    ↓集計用のSQL
    strSql = "SELECT SUM(金額) AS 総合計 " & _
         "FROM 売上明細 WHERE 伝票番号 = " & _
         strSlipNumber

    Try
      ↓接続文字列の設定~データベースのオープン
      objConnect.ConnectionString = _
      "Persist Security Info=False;Integrated Security=SSPI;database=db1001ya;server=localhost"
      objCommand.Connection = objConnect
      objConnect.Open()
      ↓SQLを設定
      objCommand.CommandText = strSql
      ↓SQLを実行
      objDataReader = objCommand.ExecuteReader()
      
      If objDataReader.Read() Then
        If Not IsDBNull(objDataReader("総合計")) Then
          lblGndTotal.Text = Format(objDataReader("総合計"), "#,##0")
        Else ' 失敗時には0とする
          lblGndTotal.Text = "0"
        End If
      End If
         : (例外処理などは省略)
    End Try
  End Sub



トップページ
後半部分の概要
1.商品の特定
2.数量の入力~txtNumItems_LostFocus
3.1件の追加
1件分の情報をグリッドに表示~btnAdd_Click
明細をテーブルに追加~AddDetail
DataGridに売上明細を表示~UpdateGrid
売上の総合計金額を表示~DispTotal
その他の処理
補足
あとがき
Copyright © GrapeCity inc. All rights reserved.