VB.NETでストアドプロシージャの使い方

こんにちは
Visual studioを使って
VB.NETで商品検索機能を作成することにしたのですが
データベースからストアドプロシージャを使って検索しましたが
うまく検索結果を表示されることができません。
おそらくストアドプロシージャを呼び出せてないかと思います。

流れは検索条件として商品コードと商品名をテキストボックスで取得して
ボタンをクリックするとdtoクラスのlistItemメソッドでSQLの処理をし
結果をDataSet型で返して、GridViewに結果を入れる

VB
1   '商品検索メソッド
2    Public Function listItem(name As String, code As Integer) As DataSet
3        Dim setting As ConnectionStringSettings =ConfigurationManager.ConnectionStrings("SelfAsp")
4
5        Using db As New SqlConnection(setting.ConnectionString)
6            db.Open()
7            Dim comm As SqlCommand = db.CreateCommand
8            comm.CommandType = CommandType.StoredProcedure
9            comm.CommandText = "DrinklistItem"
10            Dim dataSet As DataSet = New DataSet()
11            comm.Parameters.Clear()
12            'ストアドプロシージャに引数を渡す
13            comm.Parameters.Add("@Namelist", SqlDbType.Text).Value = name
14            comm.Parameters.Add("@Codelist", SqlDbType.Int).Value = code
15            '検索結果をDataSet型に渡す
16            Using adapter As SqlDataAdapter = New SqlDataAdapter(comm)
17                adapter.Fill(dataSet, "drink")
18            End Using
19            comm.ExecuteNonQuery()
20
21            Return dataSet
22            db.Close()
23        End Using
24    End Function

vb
1   Protected Sub listbtn_Click(sender As Object, e As EventArgs) Handles listbtn.Click
2        Dim code As Integer = TextCode.Text
3        Dim name As String = TextName.Text
4        Dim dataSet As DataSet
5        Dim dto As New dto
6        dataSet = dto.listItem(name, code)
7        Me.Gridlist.DataSource = dataSet
8
9    End Sub

ストアドプロシージャです。

sql
1CREATE PROCEDURE [dbo].[DrinklistItem](
2@Namelist nvarchar(200),
3@Codelist int)
4
5AS
6begin
7
8SELECT  [code],[name],[price],[count],[favorite]
9FROM [drink]
10WHERE name LIKE '%@Namelist%' OR code LIKE '%@Codelist%'
11
12END

退会済みユーザー

2018/06/26 12:54

何を作っているかと開発環境ぐらいは書けませんか？

行動規範の内容に同意します

回答3件

質問に対する私のコメントへの返答をもらってませんので、作っているのは ASP.NET Web Forms アプリで DB は SQL Server であろうと勝手に想像してレスを書いておきます。質問者さんの実際の環境とは違うかもしれませんが。

まずストアドの WHERE 句の部分が間違っているようです。

code は文字型ではないですよね？ int 型ではないかと想像していますが、そうだとすると LIKE 句は使えません。

name の方は文字型のようですが LIKE '%@Namelist%' ではうまくいかないかと思われます。

あと、OR ではなくて AND の間違いだと想像してます。

そこを直して、あとは自力でコードを書かないで GridView + SqlDataSource を使ってデザイナでアプリを作れば多分自力では一行もコードを書かなくても望むことができると思います。

質問者さんの DB では試せませんので、Microsoft が提供するサンプルデータベースの Ptoducts テーブルを使って検証してみましたが、ストアドがきちんとできていれば、それをベースにデザイナで作れば簡単に完成します。

ご参考に検証に使ったコードを以下にアップしておきます。（.aspx.cs はデフォルトで生成されたものに手を加えてないので省略）

ストアドプロシージャ（SSMS で作成）

USE [NORTHWIND]
GO
/****** Object:  StoredProcedure [dbo].[ProductList]    Script Date: 06/27/2018 11:48:07 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
ALTER PROCEDURE [dbo].[ProductList]
(
	@name nvarchar(250),
	@catId int
)
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

    -- Insert statements for procedure here
    SELECT [ProductID],[ProductName],[SupplierID],[CategoryID],[UnitPrice]
    FROM [Products]
    WHERE ([ProductName] LIKE N'%' + @name + N'%') AND ([CategoryID] = @catId)
END

.aspx

<%@ Page Language="C#" AutoEventWireup="true" 
    CodeFile="0041-StoredProcAndGridView.aspx.cs" 
    Inherits="_0041_StoredProcAndGridView" %>

<!DOCTYPE html>

<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<meta http-equiv="Content-Type" content="text/html; charset=utf-8"/>
    <title></title>
</head>
<body>
    <form id="form1" runat="server">
        ProductName: <asp:TextBox ID="TextBox1" runat="server"></asp:TextBox>
        <br />
        CategoryID: <asp:TextBox ID="TextBox2" runat="server"></asp:TextBox>
        <br />
        <asp:Button ID="Button1" runat="server" Text="PostBack" />
        <asp:SqlDataSource ID="SqlDataSource1" runat="server" 
            ConnectionString="<%$ ConnectionStrings:NORTHWINDConnectionString %>" 
            SelectCommand="ProductList" SelectCommandType="StoredProcedure">
            <SelectParameters>
                <asp:ControlParameter ControlID="TextBox1" 
                    Name="name" PropertyName="Text" Type="String" />
                <asp:ControlParameter ControlID="TextBox2"
                     Name="catId" PropertyName="Text" Type="Int32" />
            </SelectParameters>
        </asp:SqlDataSource>
        <asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" 
            DataKeyNames="ProductID" DataSourceID="SqlDataSource1">
            <Columns>
                <asp:BoundField DataField="ProductID" HeaderText="ProductID" 
                    InsertVisible="False" ReadOnly="True" SortExpression="ProductID" />
                <asp:BoundField DataField="ProductName" HeaderText="ProductName" 
                    SortExpression="ProductName" />
                <asp:BoundField DataField="SupplierID" HeaderText="SupplierID" 
                    SortExpression="SupplierID" />
                <asp:BoundField DataField="CategoryID" HeaderText="CategoryID" 
                    SortExpression="CategoryID" />
                <asp:BoundField DataField="UnitPrice" HeaderText="UnitPrice" 
                    SortExpression="UnitPrice" />
            </Columns>
        </asp:GridView>
    </form>
</body>
</html>

投稿2018/06/27 03:09

退会済みユーザー

総合スコア0

erumoa

2018/06/27 08:10

コメント遅くなり申し訳ございませんでした。解決しました。回答ありがとうございました。

退会済みユーザー

2018/06/27 09:20

どのように解決したのか書いていただけませんか？　ここのようなサイトは情報共有を目的としているので、質問者さんがどのように解決したかの情報も役に立ちますし、情報提供は義務とまではいわないもののマナーではあると思いますので。自分としては、上に指摘した「code は文字型ではないですよね？ int 型ではないかと想像していますが、そうだとすると LIKE 句は使えません」というところをどう解決したのかお聞きしたいです。

行動規範の内容に同意します

ベストアンサー

1,商品検索メソッド側のストアドプロシージャに渡す型の定義が違います。

diff
1-comm.Parameters.Add("@Namelist", SqlDbType.Text).Value = name
2+comm.Parameters.Add("@Namelist", SqlDbType.NVarChar, 200).Value = name

2,adapter.Fill(dataSet, "drink")を行っているので、comm.ExecuteNonQueryは不要です。

VB.NET
1comm.ExecuteNonQuery()

3,Return後にdb.Close()を行っているため、Close行は実行されません。

VB.NET
1Return dataSet
2db.Close()

ストアドプロシージャ側
4,渡されたパラメータではなくシングルクォートで囲った固定文字列を条件式としています。

SQL
1WHERE name LIKE '%@Namelist%' OR code LIKE '%@Codelist%'

SQL
1WHERE name LIKE '%' + @Namelist + '%' OR code LIKE '%' + @Codelist + '%'

投稿2018/06/27 02:47

umyu

総合スコア5846

erumoa

2018/06/27 08:10

おかげさまで、解決できました。ありがとうございました。

行動規範の内容に同意します

GridのDataSourceにDataSetを設定しているのであれば、DataMemberでテーブル名（"drink"かな？）を設定しなくてはいけないのではないでしょうか。
System.Windows.Forms.DataGridViewの話なので、タグでASP.NETがあるので外しているかもしれませんが・・・

何にしろ、ストアドを実行した後、それが成功しているのか、データが取得できているのかデバッガで確認するとかログで件数を出してみるとか別の手段で確認すべきかと思います。

投稿2018/06/26 23:15