none
SQLのLIKE句にバインド変数を指定すると、結果が取得できない。 RRS feed

  • 質問

  •     VBで以下のような処理を作成しました。

       ''' <summary>
        ''' DBロード処理
        ''' </summary>
        ''' <remarks>指定した条件でデータを取得する。</remarks>
        Private Sub LoadDatabase()

            Dim strWhere As String = String.Empty
            Dim strSQL As String

            'プロジェクト名の条件指定(部分一致)と条件式の生成
            If Not Me.txtProjectName.Text.Equals(String.Empty) Then
                strWhere = "tbl_project.project_name LIKE @PROJECT_NAME"
            End If

            'マネージャの条件指定と条件式の作成
            If Me.cmbManager.SelectedIndex > 0 Then
                If Not strWhere.Equals(String.Empty) Then
                    strWhere &= " AND "
                End If
                strWhere &= "tbl_project.staff_code = @MANAGER"
            End If

            '顧客の条件指定と条件式の生成
            If Me.cmbCustomer.SelectedIndex > 0 Then
                If Not strWhere.Equals(String.Empty) Then
                    strWhere &= " AND "
                End If
                strWhere &= "tbl_project.customer_code = @CUSTOMER"
            End If

            '完了フラグの条件指定と条件式の作成
            If Not Me.chkComplete.Checked Then
                If Not strWhere.Equals(String.Empty) Then
                    strWhere &= " AND "
                End If
                '未完了データのみにする
                strWhere &= "tbl_project.complete_flag = 0"
            End If

            'SQL文の生成
            strSQL = "SELECT tbl_project.*, tbl_customer.customer_name, tbl_staff.staff_name "
            strSQL &= "FROM tbl_project INNER JOIN tbl_staff ON tbl_project.staff_code = tbl_staff.staff_code "
            strSQL &= "INNER JOIN tbl_customer ON tbl_project.customer_code = tbl_customer.customer_code "

            '条件が指定されていれば、WHERE句に追加
            If Not strWhere.Equals(String.Empty) Then
                strSQL &= "WHERE " & strWhere
            End If

            'コネクションを指定
            Dim connection As New SqlClient.SqlConnection(My.Settings.project_jobConnectionString)
            Dim da As New SqlClient.SqlDataAdapter()

            da.SelectCommand = New SqlClient.SqlCommand(strSQL, connection)
            da.SelectCommand.Parameters.Add("@PROJECT_NAME", SqlDbType.Char, 50).Value = "'%" & Me.txtProjectName.Text & "%'"
            da.SelectCommand.Parameters.Add("@MANAGER", SqlDbType.Char, 6).Value = Strings.Left(Me.cmbManager.Text, 6)
            da.SelectCommand.Parameters.Add("@CUSTOMER", SqlDbType.Char, 7).Value = Strings.Left(Me.cmbCustomer.Text, 7)

            'データテーブルをクリア
            Me.Project_jobDataSet.dtProjectList.Clear()

            'データテーブルにデータアダプタを介してデータをセット
            da.Fill(Me.Project_jobDataSet.dtProjectList)

     End Sub

    この処理を動かすと、マネージャや顧客を指定したときは、結果が返ってくるのですが、

    プロジェクト名を指定しても結果が返ってきません。プロジェクト名は部分一致で検索します。

    他で調べてみたところ、バインド変数に %を含むと動くというようなことが書かれていたので、

    それを試してみましたが、それでも結果が返ってきません。どなたか情報をお持ちでしたら、

    よろしくお願いします。

    環境はWinVista, SqlServer2008です。

     

    2010年9月2日 13:17

回答

  • tbl_project.project_nameがChar(50)でなければ、確かにSqlDbType.Charであれば失敗しますね。SqlDbType.VarCharにすれば良いと思います。


    ★良い回答には回答済みマークを付けよう! わんくま同盟 MVP - Visual C# http://d.hatena.ne.jp/trapemiya/
    2010年9月2日 22:29
    モデレータ
  • %や_はバインド変数を通してもエスケープされず、like文ではそのまま特殊文字として扱われますので、掲載されたコードで問題ないように思えます。プロファイラで実際にどのようなSQL文が発行されているのか確認されてみてはいかがでしょうか?

    ★良い回答には回答済みマークを付けよう! わんくま同盟 MVP - Visual C# http://d.hatena.ne.jp/trapemiya/
    • 回答の候補に設定 山本春海 2010年9月9日 2:40
    • 回答としてマーク 山本春海 2010年9月13日 4:48
    2010年9月2日 16:17
    モデレータ
  • da.SelectCommand.Parameters.Add("@PROJECT_NAME", SqlDbType.Char, 50).Value = "'%" & Me.txtProjectName.Text & "%'"

    ここのところが引っかかります。
    Size を 50 にしているので、

      '%some-project-name%            '

    といった具合に、2 つめの % の後ろに余計なスペースがくっつくように思います。

    関連するかもしれないスレッド:
      クリエビルダによるSQL文のLIKEについて
      http://social.msdn.microsoft.com/Forums/ja-JP/vbexpressja/thread/2b89bee7-f567-4a76-aad6-cec2edabbbb3
    • 回答の候補に設定 山本春海 2010年9月9日 2:40
    • 回答としてマーク 山本春海 2010年9月13日 4:48
    2010年9月2日 18:09

すべての返信

  • %や_はバインド変数を通してもエスケープされず、like文ではそのまま特殊文字として扱われますので、掲載されたコードで問題ないように思えます。プロファイラで実際にどのようなSQL文が発行されているのか確認されてみてはいかがでしょうか?

    ★良い回答には回答済みマークを付けよう! わんくま同盟 MVP - Visual C# http://d.hatena.ne.jp/trapemiya/
    • 回答の候補に設定 山本春海 2010年9月9日 2:40
    • 回答としてマーク 山本春海 2010年9月13日 4:48
    2010年9月2日 16:17
    モデレータ
  • da.SelectCommand.Parameters.Add("@PROJECT_NAME", SqlDbType.Char, 50).Value = "'%" & Me.txtProjectName.Text & "%'"

    ここのところが引っかかります。
    Size を 50 にしているので、

      '%some-project-name%            '

    といった具合に、2 つめの % の後ろに余計なスペースがくっつくように思います。

    関連するかもしれないスレッド:
      クリエビルダによるSQL文のLIKEについて
      http://social.msdn.microsoft.com/Forums/ja-JP/vbexpressja/thread/2b89bee7-f567-4a76-aad6-cec2edabbbb3
    • 回答の候補に設定 山本春海 2010年9月9日 2:40
    • 回答としてマーク 山本春海 2010年9月13日 4:48
    2010年9月2日 18:09
  • tbl_project.project_nameがChar(50)でなければ、確かにSqlDbType.Charであれば失敗しますね。SqlDbType.VarCharにすれば良いと思います。


    ★良い回答には回答済みマークを付けよう! わんくま同盟 MVP - Visual C# http://d.hatena.ne.jp/trapemiya/
    2010年9月2日 22:29
    モデレータ
  • SqlDbType.VarCharにしてみましたが、やはり結果が返ってきません。project_nameのサイズはVarchar(50)です。
    2010年9月3日 10:32
  • SqlDbType.VarCharに変更し、%の前後の'(シングルクォーテーション)をとったら、期待した結果が返ってきました。

    ありがとうございました。

    2010年9月3日 10:49
  • プロファイラを眺めていたとき、どのようなクエリが飛んできていますか?

    ■起動方法
    「スタート」ボタン⇒「SQL Server 2008」⇒「パフォーマンスツール」⇒「SQL Server Profiler」

    実行ボタンを押すと、実行されているクエリがトレースできます。
    実行ボタン押下後、%が含まれるクエリを実行してみてください。

     

    2010年9月3日 11:00
  • 今さらながらのレスですが・・・

    > SqlDbType.VarCharに変更し、%の前後の'(シングルクォーテーショ
    > ン)をとったら、期待した結果が返ってきました。

    パラメータ設定の型指定を SqlDbType.Char のままにしておいても、
    SqlParameter.Size を指定しない、もしくは 0 にすれば期待通りマ
    ッチするはずです。具体的には、

    SqlParameterCollection.Add("@PROJECT_NAME", SqlDbType.Char)

    または

    SqlParameterCollection.Add("@PROJECT_NAME", SqlDbType.Char, 0)

    パターン文字列の生成に使う型(パラメータ設定のデータ型)と比較
    対象文字列の型(テーブル定義のデータ型)を合わせる必要はなさそ
    うなので、SqlDbType.VarChar に変更する方法もあり・・・というよ
    り、たぶんそれが正解とは思いますが。

    2010年9月4日 3:41
  • 個人的には、.NET Framework 2.0 以上だったら、迷わず AddWithValue するところです。
    2010年9月4日 9:14
  • >SqlDbType.VarCharにしてみましたが、やはり結果が返ってきません。project_nameのサイズはVarchar(50)です。
    をみて、どのようなクエリがサーバ側に上がってるのか確認するために、プロファイラを使えばよいのでは、と回答したのですが、すでに対処できていたのですね。
    前後してしまい申し訳ございませんでした。

    2010年9月4日 9:21
  • > 個人的には、.NET Framework 2.0 以上だったら、迷わず AddWithValue
    > するところです。

    そうですね、それが正解かもしれません。

    ただ、今回のケースとは関係ないですが、Visual Studio のウィザードで
    TableAdapter を作る場合の自動生成されるコードが問題ですね。

    2010年9月4日 12:00
  • ちなみに%などはバインド変数を使ってもエスケープされませんから、自分でエスケープする必要があります。もし未対応であれば、以下を参考にして対応されることをお勧めします。

    LIKE (Transact-SQL)
    http://msdn.microsoft.com/ja-jp/library/ms179859.aspx


    ★良い回答には回答済みマークを付けよう! わんくま同盟 MVP - Visual C# http://d.hatena.ne.jp/trapemiya/
    2010年9月5日 11:16
    モデレータ
  • こんにちは、h.katou さん。

    フォーラム オペレーターの山本です。MSDN フォーラムのご利用ありがとうございます。

    trapemiya さんや、totojo さんからの情報が、解決に役立ったようでしたので、勝手ながら私のほうで回答としてマークさせていただきました。
    回答いただきましたみなさん、情報ありがとうございます。

    今後同じ問題でこのスレッドを参照される方も、有効な情報を見つけやすくなるかと思いますので、いただいた回答の中で、解決に役立った情報や、参考になった情報には、回答としてマークすることをお願いしています。
    お手数ですが、次回から h.katou さんも、回答としてマークをしてみてくださいね。

    今後とも、MSDN フォーラムをよろしくお願いいたします。それでは。
                                        
    マイクロソフト株式会社 フォーラム オペレーター 山本 春海

    2010年9月13日 4:52