none
動的に作成するGridViewとDropDownListを連動させる方法 RRS feed

  • 質問

  •  

    たびたびお世話になります。

    先日教えていただいた以下のGridViewを作成するSQLコマンドを応用してDropDownListから会社名を選ぶと、該当するデータを下にGridViewを作成するように書き換えようとしているのですが、理解が足りずぜんぜんうまくいきません。

    どこに WHERE (([Company] = @Company) を追加すればよいのか教えていただけないでしょうか?

     

    よろしくお願いします。

     

     

    <aspTongue TiedqlDataSource ID="SqlDataSource1" runat="server"
                ConnectionString="<%$ ConnectionStrings:usagelogConnectionString1 %>"
               
               
            SelectCommand="BEGIN DECLARE @sqlstr varchar(max), @sqldatas varchar(max), @targetKyoku varchar(50); DECLARE cur CURSOR FOR SELECT DISTINCT _15 FROM UsageLog_past12Ms WHERE _15 IS NOT NULL ORDER BY _15 ASC; SET @sqldatas = ''; SET @sqlstr = 'SELECT tuki'; OPEN cur; FETCH next FROM cur INTO @targetKyoku; WHILE (@@fetch_status &lt;&gt; - 1) BEGIN IF len(@sqldatas) &gt; 0 BEGIN SET @sqldatas = @sqldatas + ',' END; SET @sqldatas = @sqldatas + '[' + @targetKyoku + ']' SET @sqlstr = @sqlstr + ',[' + @targetKyoku + '] as [' + @targetKyoku + ']'; FETCH next FROM cur INTO @targetKyoku; END; CLOSE cur; DEALLOCATE cur; SET @sqlstr = @sqlstr + ' FROM (select  _11 as tuki, _15 FROM UsageLog_past12Ms) T PIVOT (Count([_15]) FOR [_15] IN '; SET @sqlstr = @sqlstr + '(' + @sqldatas + ')) AS PIVOT_TABLE'; EXECUTE (@sqlstr); END;">

     <SelectParameters>
                        <asp:ControlParameter ControlID="DropDownList1" Name="Company"
                            PropertyName="SelectedValue" Type="String" />
                                        </SelectParameters>
     
            </aspTongue TiedqlDataSource>

    2008年6月26日 13:31

回答

  • 例です。

     

    CREATE PROCEDURE [test].[PivotTest]

    (

            @company    nvarchar(10) = ''

    )

    as

    begin

        declare

        @sqlstr nvarchar(max),

        @sqldatas nvarchar(max),

        @targetKyoku nvarchar(10)

     

        declare cur cursor for

            SELECT DISTINCT _15 FROM UsageLog_past12Ms where _15 is not null and company = case when @company != '' then @company else _15 end

     

        set @sqldatas = ''

        set @sqlstr = 'SELECT tuki'

     

        open cur

     

    if @@CURSOR_ROWS = 0

    begin

        close cur

            deallocate cur

        return

    end

     

        fetch next from cur into @targetKyoku

        while (@@fetch_status <> -1 )

        begin

            if len(@sqldatas) > 0

            begin

                set @sqldatas = @sqldatas + ','

            end

           

          

            set @sqldatas = @sqldatas + '[' + @targetKyoku + ']'

     

            set @sqlstr = @sqlstr + ',[' + @targetKyoku + '] as [' + @targetKyoku + ']'

            fetch next from cur into @targetKyoku

        end

     

        close cur

        deallocate cur

     

        set @sqlstr = @sqlstr + ' FROM (select datepart(mm, _1) as tuki, _15 FROM UsageLog_past12Ms) T PIVOT (Count([_15]) FOR [_15] IN '

        set @sqlstr = @sqlstr + '(' + @sqldatas + ')) AS PIVOT_TABLE'

     

        exec sp_executesql @sqlstr, N'@company nvarchar(10)', @company

     

    end

    2008年7月2日 1:07
    モデレータ

すべての返信

  • SELECT DISTINCT _15 FROM UsageLog_past12Ms WHERE _15 IS NOT NULL ORDER BY _15 ASC;

     

     

    SELECT DISTINCT _15 FROM UsageLog_past12Ms WHERE _15 IS NOT NULL and _15 = @Company ORDER BY _15 ASC;

     

    になります。

    2008年6月27日 1:17
    モデレータ
  • 早速ありがとうございます。

     

    _15 = @Companyを足して実行するとオブジェクト参照がオブジェクト インスタンスに設定されていませんとなりGridViewが失敗します。_15という列とは別にCompanyという列が元にしているテーブルにあって選んだ会社名分のデータを下に集計結果をGridVIewに表示させたいのです。よくわからないのですが、Company=@Company

    としても同様にエラーになってしまいます。

    どうすればよいのでしょうか?教えてください。

    2008年6月27日 1:44
  • SQLはCompany=@Companyで問題ありません。問題はGridViewなのかSQLなのかを切り分けるためにも、SQLの部分を可能であればストアドプロシージャの形にしてしまうのが良いと思います。

     

    今回はオブジェクトのインスタンスの問題ですので、たぶんSQL以外の問題だと思います。もう少し詳しいエラーメッセージを教えて下さい。

    2008年6月27日 5:11
    モデレータ
  • ありがとうございます。

    GridViewが正しく作成されずHeaderRow.Cells.Countが0なのでエラーになっているようですが、

    なぜそうなるの以下から読み取れますでしょうか?全部張ったところ書き込みに失敗するので重要そうなところのみ貼り付けます。

    よろしくお願いします。

     

    2008年6月27日 6:13
  •  

    行 60:         Dim myStyle As Style = New Style
    行 61:         myStyle.CssClass = "Tategaki"
    行 62:         For z = 1 To GridView1.HeaderRow.Cells.Count - 1 Step 1
    行 63:             Me.GridView1.HeaderRow.Cells(z).ApplyStyle(myStyle)
    行 64:         Next
    2008年6月27日 6:15
  • > GridViewが正しく作成されずHeaderRow.Cells.Countが0なのでエラーになっているようですが、

     

    抽出されたデータが0件だったりしませんか?

    2008年6月27日 6:39
  • お世話になります。

    それがDropDownListと連動しないようにして表示させるとちゃんと5列で数十ページのデータが表示されます。

    DrowDownListの規定値がNULLなのかもしれないと思いDefaultVlaueを以下のように設定してみたのですがやはり同じです。

     

    <asp:ControlParameter ControlID="DropDownList1"
                         DefaultValue="Existing Company Name Inc." Name="Company" PropertyName="SelectedValue"
                         Type="String" />

    ちょっと長いので心配ですがあとでソースコードを貼り付けますので引き続き、

    よろしくお願いします。

     

    2008年6月27日 6:54
  • <%@ Page validaterequest="false" Language="VB" MasterPageFile="~/Pages/MasterPage.master" AutoEventWireup="false" CodeFile="report.aspx.vb" Inherits="Pages_Statistics" title="無題のページ" %>

    <asp:Content ID="Content1" runat="server"
        contentplaceholderid="ContentPlaceHolder2">

        <SCRIPT LANGUAGE="Javascript" SRC="../FusionCharts/FusionCharts.js"></SCRIPT>
     <head>
        <style type ="text/css">
        th.Tategaki  { writing-mode: tb-rl; }
    </style>
        </head>

     <aspBig SmileropDownList ID="DropDownList1" runat="server"
                    DataSourceID="SqlDataSource3" DataTextField="Company"
            DataValueField="Company" >
                </aspBig SmileropDownList>
       期間:<aspBig SmileropDownList ID="DropDownList2" runat="server"
                    DataSourceID="SqlDataSource5" DataTextField="_1"
                    DataTextFormatString="{0:yyyy年M月}" DataValueField="_1"
            AutoPostBack="False">
                </aspBig SmileropDownList>から<aspBig SmileropDownList ID="DropDownList3" runat="server"
                    DataSourceID="SqlDataSource6" DataTextField="_1"
                    DataTextFormatString="{0:yyyy年M月}" DataValueField="_1"
            AutoPostBack="False">
                </aspBig SmileropDownList>         
                    <br />
                    <br />
                    <br />
                     
        <%=CreateChart()%> &nbsp;<br />
    <asp:GridView ID="GridView1" runat="server"
            DataSourceID="SqlDataSource1" PageSize="20" style="text-align: center"
                            Width="80%" AllowSorting="True"
            EnableSortingAndPagingCallbacks="True" AllowPaging="True"
            HorizontalAlign="Center">
            <HeaderStyle
                BorderColor="#666699" />
                        </asp:GridView>
                        <asp:Label ID="Label1" runat="server" Text="Label" Width="100%"></asp:Label>
                     
        <br />
        <br />
            <aspTongue TiedqlDataSource ID="SqlDataSource1" runat="server"
                ConnectionString="<%$ ConnectionStrings:usagelogConnectionString11 %>"
               
               
            SelectCommand="BEGIN DECLARE @sqlstr varchar(max), @sqldatas varchar(max), @targetKyoku varchar(50); DECLARE cur CURSOR FOR SELECT DISTINCT _15 FROM UsageLog_past12Ms WHERE _15 IS NOT NULL AND [Company] =@Company ORDER BY _15 ASC; SET @sqldatas = ''; SET @sqlstr = 'SELECT tuki'; OPEN cur; FETCH next FROM cur INTO @targetKyoku; WHILE (@@fetch_status &lt;&gt; - 1) BEGIN IF len(@sqldatas) &gt; 0 BEGIN SET @sqldatas = @sqldatas + ',' END; SET @sqldatas = @sqldatas + '[' + @targetKyoku + ']' SET @sqlstr = @sqlstr + ',[' + @targetKyoku + '] as [' + @targetKyoku + ']'; FETCH next FROM cur INTO @targetKyoku; END; CLOSE cur; DEALLOCATE cur; SET @sqlstr = @sqlstr + ' FROM (select  _11 as tuki, _15 FROM UsageLog_past12Ms) T PIVOT (Count([_15]) FOR [_15] IN '; SET @sqlstr = @sqlstr + '(' + @sqldatas + ')) AS PIVOT_TABLE'; EXECUTE (@sqlstr); END;">
            <SelectParameters>
                        <asp:ControlParameter ControlID="DropDownList1" Name="Company"
                            PropertyName="SelectedValue" Type="String" />
                    </SelectParameters>
            </aspTongue TiedqlDataSource>
           
             <aspTongue TiedqlDataSource ID="SqlDataSource3" runat="server"
            ConnectionString="<%$ ConnectionStrings:usagelogConnectionString13 %>"
           
            SelectCommand="SELECT [Company] FROM [UsageLog_past12Ms] WHERE ([Company] = @Company)">
                 <SelectParameters>
                     <asp:ControlParameter ControlID="DropDownList1"
                          Name="Company" PropertyName="SelectedValue"
                         Type="String" />
                 </SelectParameters>
        </aspTongue TiedqlDataSource>
        <aspTongue TiedqlDataSource ID="SqlDataSource5" runat="server"
            ConnectionString="<%$ ConnectionStrings:usagelogConnectionString13 %>"
            SelectCommand="SELECT DISTINCT _1 FROM UsageLog_past12Ms ORDER BY _1 ASC">
        </aspTongue TiedqlDataSource>
        <aspTongue TiedqlDataSource ID="SqlDataSource6" runat="server"
            ConnectionString="<%$ ConnectionStrings:usagelogConnectionString13 %>"
            SelectCommand="SELECT DISTINCT _1 FROM UsageLog_past12Ms ORDER BY _1">
        </aspTongue TiedqlDataSource>
        <p>
     
        </p>

    </asp:Content>

    2008年6月27日 7:09
  •  


    Imports System
    Imports System.IO
    Imports InfoSoftGlobal


    Partial Class Pages_Statistics
        Inherits System.Web.UI.Page
        Public Function CreateChart() As String

            Dim strXML As String
            Dim StartRow As Integer
            Dim EndRow As Integer


            StartRow = 1
            EndRow = GridView1.Rows.Count - 1
           
            strXML = ""
            strXML = strXML & "<graph caption='' subCaption='' borderColor = 'E7E7EF' bgColor ='E7E7EF' bgAlpha='0' canvasBorderColor='E7E7EF' numdivlines='10' lineThickness='1' showValues='0' numVDivLines='11' formatNmberScale='0' labelDisplay='ROTATE' slantLabels='1' anchorRadius='2' anchorBgAlpha='0' showAlternateVGridColor='1' anchorAlpha='100' animation='1' limitsDecimalPrecision='0' divLineDecimalPrecision='1'>" & "<categories>"

            For i = 1 To 5 Step 1
                strXML = strXML & "<category label='"
                If Me.GridView1.HeaderRow.Cells(i).HasControls Then
                    strXML = strXML & " " & Left(DirectCast(Me.GridView1.HeaderRow.Cells(i).Controls(0), Web.UI.WebControls.LinkButton).Text, 10)
                Else
                    strXML = strXML & " " & Me.GridView1.HeaderRow.Cells(i).Text
                End If
                strXML = strXML & "'" & "/>"

            Next
            strXML = strXML & "</categories>"

            For j = StartRow To EndRow Step 1
                strXML = strXML & "<dataset seriesName= '" & GridView1.Rows.Item(j).Cells(0).Text & j & "'>"

                For k = 1 To GridView1.HeaderRow.Cells.Count - 1 Step 1
                    strXML = strXML & "<set value='" & GridView1.Rows.Item(j).Cells(k).Text & "'/>"
                Next
                strXML = strXML & "</dataset>"
            Next
            strXML = strXML & "</graph>"


            Return FusionCharts.RenderChart("../FusionCharts/MSArea.swf", "", strXML, "myNext", "900", "400", False, True)


        End Function

     

     


        Protected Sub Page_LoadComplete(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.LoadComplete
            Label1.Text = GridView1.Rows.Count
        End Sub

        Protected Sub GridView1_PreRender(ByVal sender As Object, ByVal e As System.EventArgs) Handles GridView1.PreRender
            Dim myStyle As Style = New Style
            myStyle.CssClass = "Tategaki"
            For z = 1 To GridView1.HeaderRow.Cells.Count - 1 Step 1
                Me.GridView1.HeaderRow.Cells(z).ApplyStyle(myStyle)
            Next
        End Sub

    End Class

    2008年6月27日 7:13
  • とりあえず気がつくのは、DropDownList1のAutoPostBackがtrueになっていませんね。

    2008年6月27日 7:24
    モデレータ
  • ありがとうございます。

    AutoPostBackをTrueにしても同じで失敗してしまいます。どうやら別の原因があるようです。

    引き続きよろしくお願いします。

     

     <aspBig SmileropDownList ID="DropDownList1" runat="server"
                    DataSourceID="SqlDataSource3" DataTextField="Company"
            DataValueField="Company" AutoPostBack="True" >

    2008年6月27日 7:38
  • 問題とは関係ないかもしれませんが、このあたり、どう考えても変なんですけど・・・

    DropDownList にはちゃんと表示がされるのでしょうか?

     

    Code Snippet

    <asp:DropDownList
        ID="DropDownList1"
        runat="server"
        DataSourceID="SqlDataSource3"
        DataTextField="Company"
        DataValueField="Company" >
    </asp:DropDownList>

    <asp:SqlDataSource
        ID="SqlDataSource3"
        runat="server"
        ConnectionString="<%$ ConnectionStrings:usagelogConnectionString13 %>"
        SelectCommand="SELECT [Company] FROM [UsageLog_past12Ms] WHERE ([Company] = @Company)">
        <SelectParameters>
             <asp:ControlParameter
                 ControlID="DropDownList1"
                 Name="Company"
                 PropertyName="SelectedValue"
                 Type="String" />
        </SelectParameters>
    </asp:SqlDataSource>

     

     

    2008年6月27日 8:12
  • こんな感じじゃないですか?

     

    Code Snippet

     <asp:sqlDataSource ID="SqlDataSource3" runat="server"

            ConnectionString="<%$ ConnectionStrings:usagelogConnectionString13 %>"
           
            SelectCommand="SELECT DISTINCT [Company] FROM [UsageLog_past12Ms] ORDER BY [Company]">
      </asp:sqlDataSource>

     

     

     

    #SurferOnWwwさんとかぶりました。同じようなことですね。

    2008年6月27日 8:17
    モデレータ
  • ありがとうございます。

    テーブル名以下のところが明らかにおかしいですね。気づきませんでした。

    ちょっと試してみます。

        SelectCommand="SELECT DISTINCT [Company] FROM [UsageLog_past12Ms] ORDER BY [Company]">
    2008年6月27日 8:36
  • すみません。

    いろいろと試行錯誤中に混乱して書き込んでしまっていたことがわかりました。

    この部分を正しく直してもやはりずっと一貫して同じエラーがでますね。

    よろしくお願いします。

     

    <aspTongue TiedqlDataSource ID="SqlDataSource3" runat="server"

            ConnectionString="<%$ ConnectionStrings:usagelogConnectionString13 %>"
           
            SelectCommand="SELECT DISTINCT [Company] FROM [UsageLog_past12Ms] ORDER BY [Company]">
      </aspTongue TiedqlDataSource>

    2008年6月27日 8:42
  • すみません。試行錯誤中に勘違いして書き込んでしまったもので、

    そこを元に戻しても同じ問題が発生することがわかりました。

    引き続きよろしくお願いします。

     

    2008年6月27日 8:45
  • そうするのであれば、以下は削除すべきかと・・・

     

    Code Snippet

        <SelectParameters>
             <asp:ControlParameter
                 ControlID="DropDownList1"
                 Name="Company"
                 PropertyName="SelectedValue"
                 Type="String" />
        </SelectParameters>

     

     

    2008年6月27日 8:46
  • すみません、上記のレスは行き違いで変なタイミングになってしまいました。無視してください。

    2008年6月27日 8:49
  •  tosaito さんからの引用

    すみません。試行錯誤中に勘違いして書き込んでしまったもので、

    そこを元に戻しても同じ問題が発生することがわかりました。

     

    そのエラーの詳しい内容を教えていただけませんか?

    2008年6月27日 9:00
    モデレータ
  • 「一貫して同じエラー」、「同じ問題」というのは、

     

    > 行 62:         For z = 1 To GridView1.HeaderRow.Cells.Count - 1 Step 1

     

    のところで、

     

    > オブジェクト参照がオブジェクト インスタンスに設定されていません

     

    というエラーメッセージが出るということだと思いますが、とすると、
    どっとねっとふぁんさんの指摘、

     

    > 抽出されたデータが0件だったりしませんか?

     

    が当たっているような気がします。その点はチェックされたでしょうか?

     

    DropDownList で選んだ Company は SqlDataSource1 の SelectParameters
    として渡されているが、クエリに問題があって何も抽出されないというのが
    怪しいと思います。クエリを見せてもらっても自分には分からないので根拠
    なしですけど・・・

     

    でも、_15 と Company は同じ内容とのことで、とすると少なくとも以下は
    変だと思うのですが・・・

     

    SELECT DISTINCT _15 FROM UsageLog_past12Ms WHERE _15 IS NOT NULL AND Company=@Company ORDER BY _15 ASC

    2008年6月27日 10:18
  • 失礼しました。いろいろと試行錯誤をして適当に書き換えているときに誤って書き加えてしまったとことでした。

    直してもやはり同じエラーです。

    なお、同じようなページを軸を変えたりしていくつも作ったのでそのページでWHERE句のところにいろいろ足して試したところ、

     

    WHERE _1 IS NOT NULL  AND WHERE [_15] <> 'NTT'  NTTが実際にデータに存在するとエラーになる。

    WHERE _1 IS NOT NULL  AND WHERE [_15] <> 'NTO'  ちょっと変えて存在しない文字に置き換えると問題なく表示される。

    WHERE _1 IS NOT NULL  AND COMPANY <> 'NTT DATA CO LITD'  実在するが成功する。

    WHERE _1 IS NOT NULL  AND COMPANY = 'NTT DATA CO LITD'  =に変更すると失敗する。

     

    こんな感じなのですがデータがおかしいのでしょうか?

    あるいはExpress Editionだからでしょうか?

     

    よろしくお願いします。

    2008年6月27日 13:35
  • ありがとうございます。

     

    次のページがあるのに気づかず何度も投稿してしまいました。

    このテーブルはアクセスログでどの会社のどのユーザーがどのジャンルのなんというレポートにいつアクセスしたかが記録されています。

    ジャンルが_15という列でCompanyが会社です。Companyがプルダウンから選べるということは一度でもレポートにアクセスしたということで、0というのはないと思います。表示してみても目で何行もあるのを確認できますので間違いないのです。

     

    よろしくお願いします。

     

    2008年6月27日 14:14
  •  tosaito さんからの引用

    WHERE _1 IS NOT NULL  AND WHERE [_15] <> 'NTT'  NTTが実際にデータに存在するとエラーになる。

    WHERE _1 IS NOT NULL  AND WHERE [_15] <> 'NTO'  ちょっと変えて存在しない文字に置き換えると問題なく表示される。

    WHERE _1 IS NOT NULL  AND COMPANY <> 'NTT DATA CO LITD'  実在するが成功する。

    WHERE _1 IS NOT NULL  AND COMPANY = 'NTT DATA CO LITD'  =に変更すると失敗する。

     

    WHERE _1 は _15の誤りですよね?

     

    確かに0件の時にこけるので、open cur;の後に、以下の文を入れてみて下さい。

     

     if @@CURSOR_ROWS = 0 return;

    2008年6月28日 0:30
    モデレータ
  • ありがとうございます。

    _15の間違いでした。if @@CURSOR_ROWS = 0 return;を追加してもDropDownListと連動しているほうはだめで、オブジェクト参照がオブジェクト インスタンスに設定されていませんとなりますが、WHERE _15 <> 'NTT' のほうはエラーは発生せず一応ページが表示されました。しかし、除外したはずのNTTの行が残っており、逆に列の年月の方が1列だけになりました。

     

    tsuki    4 2008     5 2008  ←(このヘッダー部分が_1)

    NTT             3             4

    KDDI           2             3

     

    ↑(この列が_15)

     

    WHEREを入れる場所が違うのでしょうか?

     

    よろしくお願いします。

    2008年6月28日 3:12
  • 最新のクエリ(SqlDataSource の SelectCommand)と UsageLog_past12Ms

    テーブル/データの構造を教えてもらえませんか?

     

    問題は一つだけでなく、いろいろあるようです。DropDownList と GridView

    のみのシンプルなコードで試してみることをお勧めします(ヌル参照エラー

    を出しているハンドラはとりあえず削除して試してみるとか)。

     

    > WHEREを入れる場所が違うのでしょうか?

     

    問題とは関係ないかもしれませんが、DropDownList で選んだ Company

    のみがアクセスした各ジャンルの月別のアクセス回数を表示するということ

    であれば、EXECUTE (@sqlstr) の @sqlstr の中にも WHERE の条件を

    入れる必要があるはずです。(テーブル/データ構造が分からないのではっき

    りしたことは言えませんが)

    2008年6月28日 5:26
  •  tosaito さんからの引用

    tsuki    4 2008     5 2008  ←(このヘッダー部分が_1)

    NTT             3             4

    KDDI           2             3

     

    いつの間にか行と列が入れ替わっていませんか?

     

    ところでSQL Serverをお使いだと思いますので、問題を切り分けるためにもストアドプロシージャ化しませんか? そうすればまずストアドプロシージャが正しく動くかどうかを確かめられます。ストアドプロシージャが正しく動けばGridViewの表示に問題があることになります。プログラムが複雑化するほど一気に正しく動かすのは難しくなりますので、どこまでが正しく動くのかを切り分けながら進むことがとても大事です。

    SQL Server Management Studioで問題のSQL文を実行し、正しく動作するかを確かめた方が良いと思います。私も複雑なSQL文の場合は、それが正しく動作することを確認してからアプリケーションを組み込みます。

    2008年6月28日 15:19
    モデレータ
  • ちょっと返信が遅くなってしまいまして申し訳ありません。

    SQL Server 2005 Express Editionなのですが、調べてみてストアドプロシージャにできるようであれば来週いろいろと調べて挑戦してみようと思います。行と列が入れ替わっているのは、同じようなページをいくつも作っていて、そのうちのひとつでした。まぎらわしくてすみません。

    なお、この例ではためしにWhereでPullDownListの値で抽出せず、シンプルにたての項目の中でひとつだけ除外 Where _15 <>"XXX" としようとしてもエラーになってしまい、if @@CURSOR_ROWS = 0 returnを追加するとエラーは発生しないのですが、こんどはWhere _15<>"XXX"としたはずなのに除外されていないという結果でした。

     

    ちょっともう一度情報を整理してみます。

    引き続きよろしくお願いします。

     

    2008年6月29日 13:33
  •  tosaito さんからの引用

    SQL Server 2005 Express Editionなのですが、調べてみてストアドプロシージャにできるようであれば来週いろいろと調べて挑戦してみようと思います。

     

    SQL Server 2005 Express Editionはストアドプロシージャをサポートしています。SQL Serever Management Studio Express Editionをお持ちでなければ、これも使われると良いでしょう。

    さらにプロファイラも以下にフリーであります。プロファイラは実際にどのようなSQL文が投げられているのか確認する時に便利です。

     

    Profiler for Microsoft SQL Server 2005 Express Edition
    http://sqlprofiler.googlepages.com/

     

    (参考)

    SQL Server 2005 Express Editionに発行されたSQL文をトレースするには?

    http://www.atmarkit.co.jp/fdotnet/dotnettips/744sqlexpressprofiler/sqlexpressprofiler.html

    2008年6月29日 14:45
    モデレータ
  • お返事が遅くなりまして申し訳ありません。

    テーブル構造とSQLコマンドはこのような感じになっていますが、名前がわかりにくいのでわかりやすく書き換えてシンプルなサンプルページを作成してできれば今週中に投稿しようと思います。

     

     

    _1(datetime) 年月 (例:2008年1月)

    Company(nvarchar50) 会社名(NTTデータ株式会社)

    Recipient(nvarchar50) 契約者(山田太郎) 

    _7(datetime)アクセス日時(2008/1/1 11:22:11)

    DocTitle(nvarchar50) アクセスしたレポートのタイトル

    _15(nvarchar50)  レポートのカテゴリー

    PUBLICATIONDATE(datetime) レポートの発行日

     

    SelectCommand="BEGIN DECLARE @sqlstr varchar(max), @sqldatas varchar(max), @targetKyoku varchar(50); DECLARE cur CURSOR FOR SELECT DISTINCT _1 FROM UsageLog_past12Ms  WHERE _15 <> 'XXX' ORDER BY _1 ASC; SET @sqldatas = ''; SET @sqlstr = 'SELECT tuki'; OPEN cur; if @@CURSOR_ROWS = 0 return; FETCH next FROM cur INTO @targetKyoku; WHILE (@@fetch_status &lt;&gt; - 1) BEGIN IF len(@sqldatas) &gt; 0 BEGIN SET @sqldatas = @sqldatas + ',' END; SET @sqldatas = @sqldatas + '[' + @targetKyoku + ']' SET @sqlstr = @sqlstr + ',[' + @targetKyoku + '] as [' + @targetKyoku + ']'; FETCH next FROM cur INTO @targetKyoku; END; CLOSE cur; DEALLOCATE cur; SET @sqlstr = @sqlstr + ' FROM (select  _15 as tuki, _1 FROM UsageLog_past12Ms) T PIVOT (Count([_1]) FOR [_1] IN '; SET @sqlstr = @sqlstr + '(' + @sqldatas + ')) AS PIVOT_TABLE'; EXECUTE (@sqlstr); END;">

    この場合、_1(年月)がHeaderRowに順番に入り_15(カテゴリー)が縦になります。

    ぜんぜんSQL文の内容は理解できていないのですが、列と行の部分だけを差し替えるだけで簡単に集計データが作成できて非常に便利なのでコピペして乱用しています。 WHEREで対象になるデータの絞込みができるようになればさらに便利になると思います。

    2008年6月30日 12:47
  • ご親切にありがとうございます。

    ストアドプロシージャにすると処理速度などいろいろとメリットがあるようですね。勉強になります。Expressでもできるとはありがたいです。

     

    引き続きよろしくお願いします。

     

    2008年6月30日 12:50
  •  tosaito さんからの引用

    テーブル構造とSQLコマンドはこのような感じになっていますが、名前がわかりにくいのでわかりやすく書き換えてシンプルなサンプルページを作成してできれば今週中に投稿しようと思います。

     

    _1(datetime) 年月 (例:2008年1月)

    Company(nvarchar50) 会社名(NTTデータ株式会社)

    Recipient(nvarchar50) 契約者(山田太郎) 

    _7(datetime)アクセス日時(2008/1/1 11:22:11)

    DocTitle(nvarchar50) アクセスしたレポートのタイトル

    _15(nvarchar50)  レポートのカテゴリー

    PUBLICATIONDATE(datetime) レポートの発行日

     

    回答者が知らないところでいろいろ変わっているようですが、とりあえず上記のテ

    ーブル/データ構造はこのスレッドでは変えないと言うことにできますか?

     

    それで、このデータから、どの項目を、どのような条件で抽出して、GridView にど

    のように表示したいかを明確に書いていただけませんか?

     

    上記の両方とも、このスレッドでは変えないということで話をしないと、回答者はつ

    いていけません。

     

    [qoute user="tosaito"]

    ぜんぜんSQL文の内容は理解できていないのですが、列と行の部分だけを差し替えるだけで簡単に集計データが作成できて非常に便利なのでコピペして乱用しています。

     

    はっきり言ってそれではダメです。 tosaito さんも回答者も混乱するばかりで

    いつまでたってもラチがあきません。回答者はエスパーではないのですから、

    質問者しか知りえない情報は分かりませんし、回答者の知らないところで以前

    の条件等を変えられても分かりません。

     

    2008年7月1日 6:22
  • いろいろと混乱を生じさせてしまいまして申し訳ありません。このテーブル・データ構造はこれで固定にします。

    カテゴリーにNHKなどと会社名が入っているのでそれも混乱の原因ではないかと思いますが、実はテーブル構造は一切変更していません。WHEREの条件のところを当初はWHERE Company=@Company としてDropDownlistの値と連動させていたのですが、WHERE _15<> "XXX"とシンプルな条件にしてみても同様に失敗するので、DropDownListとの連携以前にそのシンプルなケースを調べれば原因がわかるのではないかと考えていました。

     

    ということで、上記テーブル構造で固定ということにして何をしたいか・何がわからないかをなるべくシンプルにわかりやすく書いてみます。

     

    まず、すべてのデータからWHERE _15 <> "INF"の条件に当てはまる分だけ抽出したい。

    それからその抽出したデータに対して例の以下のPIVOTのSQL文を実行したい。

    つまりはPIVOTを実行する対象を絞り込むWHERE句を書き込む場所がわからない。

     

    SelectCommand="BEGIN DECLARE @sqlstr varchar(max), @sqldatas varchar(max), @targetKyoku varchar(50); DECLARE cur CURSOR FOR SELECT DISTINCT _1 FROM UsageLog_past12Ms  ORDER BY _1 ASC; SET @sqldatas = ''; SET @sqlstr = 'SELECT tuki'; OPEN cur; if @@CURSOR_ROWS = 0 return; FETCH next FROM cur INTO @targetKyoku; WHILE (@@fetch_status &lt;&gt; - 1) BEGIN IF len(@sqldatas) &gt; 0 BEGIN SET @sqldatas = @sqldatas + ',' END; SET @sqldatas = @sqldatas + '[' + @targetKyoku + ']' SET @sqlstr = @sqlstr + ',[' + @targetKyoku + '] as [' + @targetKyoku + ']'; FETCH next FROM cur INTO @targetKyoku; END; CLOSE cur; DEALLOCATE cur; SET @sqlstr = @sqlstr + ' FROM (select  _15 as tuki, _1 FROM UsageLog_past12Ms) T PIVOT (Count([_1]) FOR [_1] IN '; SET @sqlstr = @sqlstr + '(' + @sqldatas + ')) AS PIVOT_TABLE'; EXECUTE (@sqlstr); END;">

     

    GridVIewにどのように表示させたいかですが以下のような集計結果から1行目のINFを除いた分が表示されれば成功です。

    tuki 01 1 2008 12:00AM 02 1 2008 12:00AM 03 1 2008 12:00AM 04 1 2008 12:00AM 05 1 2008 12:00AM
     INF 106 120 159 177 3
    AD 411 170 100 196 9
    AIWS 351 59 77 130 3
    AIWS;SEI;STOR 3 0 0 0 0
    APP 3527 4531 4370 3455 86
    BAPP 366 80 185 127 2
    BIP 82 7 40 37 0

     

    せっかくなのでストアドプロシージャ化には挑戦しますが、このデータ構造と条件を前提にサポートいただければと思います。

     

    よろしくお願いします。

    2008年7月1日 13:43
  • 例です。

     

    CREATE PROCEDURE [test].[PivotTest]

    (

            @company    nvarchar(10) = ''

    )

    as

    begin

        declare

        @sqlstr nvarchar(max),

        @sqldatas nvarchar(max),

        @targetKyoku nvarchar(10)

     

        declare cur cursor for

            SELECT DISTINCT _15 FROM UsageLog_past12Ms where _15 is not null and company = case when @company != '' then @company else _15 end

     

        set @sqldatas = ''

        set @sqlstr = 'SELECT tuki'

     

        open cur

     

    if @@CURSOR_ROWS = 0

    begin

        close cur

            deallocate cur

        return

    end

     

        fetch next from cur into @targetKyoku

        while (@@fetch_status <> -1 )

        begin

            if len(@sqldatas) > 0

            begin

                set @sqldatas = @sqldatas + ','

            end

           

          

            set @sqldatas = @sqldatas + '[' + @targetKyoku + ']'

     

            set @sqlstr = @sqlstr + ',[' + @targetKyoku + '] as [' + @targetKyoku + ']'

            fetch next from cur into @targetKyoku

        end

     

        close cur

        deallocate cur

     

        set @sqlstr = @sqlstr + ' FROM (select datepart(mm, _1) as tuki, _15 FROM UsageLog_past12Ms) T PIVOT (Count([_15]) FOR [_15] IN '

        set @sqlstr = @sqlstr + '(' + @sqldatas + ')) AS PIVOT_TABLE'

     

        exec sp_executesql @sqlstr, N'@company nvarchar(10)', @company

     

    end

    2008年7月2日 1:07
    モデレータ
  • ありがとうございます!

    一応なんとかストアドプロシージャーの作成ができました。スキーマのところだけdboと書き換えましたがそれ以外はそのままです。

    これからVisual Web Developerの方で書き換えてみてご報告します。

     

    引き続きよろしくおねがいします。

     

    2008年7月2日 10:51
  • 早速以下のようにシンプルなサンプルページを作って実行したところ会社をDropDownListから選んでもGridViewは何も表示されないという感じになりました。これから教えていただいたプロファイラーを使ってどういうやり取りが行われてそうなってしまうのかを調べようと思います。

     

    よろしくお願いします。

     

     

    <%@ Page Language="VB" AutoEventWireup="false" CodeFile="Default3.aspx.vb" Inherits="Default3" %>

    <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

    <html xmlns="http://www.w3.org/1999/xhtml">
    <head runat="server">
        <title>無題のページ</title>
    </head>
    <body>
        <form id="form1" runat="server">
        <div>
       
            <aspBig SmileropDownList ID="DropDownList1" runat="server"
                DataSourceID="SqlDataSource1" DataTextField="Company"
                DataValueField="Company" AutoPostBack="True">
            </aspBig SmileropDownList>
            <aspTongue TiedqlDataSource ID="SqlDataSource1" runat="server"
                ConnectionString="<%$ ConnectionStrings:usagelogConnectionString14 %>"
                SelectCommand="SELECT DISTINCT [Company] FROM [UsageLog_past12Ms]"> 
            </aspTongue TiedqlDataSource>
       
        </div>
        <asp:GridView ID="GridView1" runat="server" DataSourceID="SqlDataSource2">
        </asp:GridView>
        <aspTongue TiedqlDataSource ID="SqlDataSource2" runat="server"
            ConnectionString="<%$ ConnectionStrings:usagelogConnectionString14 %>"
            SelectCommand="PivotTest" SelectCommandType="StoredProcedure">
            <SelectParameters>
                <asp:ControlParameter ControlID="DropDownList1" Name="company"
                    PropertyName="SelectedValue" Type="String" />
            </SelectParameters>
        </aspTongue TiedqlDataSource>
        </form>
    </body>
    </html>

    2008年7月2日 12:53
  • プロファイラを実行してトレースしたところおおよそ以下のようになりました。

    どうやらSQLの実行は問題なくできていてそれ以外に原因があってひょうじされないということでしょうか?

     

    よろしくお願いします。

     

    exec PivotTest @company=N'DropDownListで選んだ実際の会社名'

    DECLARE cur CURSOR FOR SELECT DISTINCT _15 FROM  UsageLog_past12Ms  WHERE _15 IS NOT NULL AND
    company = CASE WHEN @company != '' THEN @company ELSE _15 END

    SET @sqldatas = ''

    SET @sqlstr = 'SELECT tuki'

    OPEN cur

    1.2122

    1.2122

    1.4194

        ↓約300行ほど

    1.6910

    IF @@CURSOR_ROWS = 0

    CLOSE cur

    DEALLOCATE cur

    RETURN

    2008年7月2日 13:29
  • SQL Server Management Studio Express Editionで、そのストアドプロシージャを右クリックして実行することができると思います。たぶん。(SQL Server Management Studio Express Editionを使える環境ではないので確認できません)

    もし実行できるのあれば、そこでいろいろテストし、うまく実行されることを確認してからGridViewでの表示に移って下さい。

    2008年7月2日 15:05
    モデレータ
  •  

    ありがとうございます。

    早速SQL Server Management Studio Expressのほうで実行してみたところReturn Value が0という結果になりました。

    ちょっといろいろ試してみようと思います。

     

    USE [usagelog]
    GO

    DECLARE @return_value int

    EXEC @return_value = [dbo].[PivotTest]
      @company = N'会社名'

    SELECT 'Return Value' = @return_value

    GO

     

    よろしくお願いします。

     

     

     

     

    2008年7月3日 0:52
  • SQL Server Management Studio Expressで当該のデータベース -> プログラミング -> ストアドプロシージャ -> 当該のストアドプロシージャを右クリックして「ストアドプロシージャの実行」 とかできませんか?

    2008年7月3日 1:07
    モデレータ
  • 手順はあっていたのですがうまくいかないのでストアドプロシージャのnvarchar(10)のところをnvarchar(50)に全部変えてストアドプロシージャを作り直してみたところちゃんと結果が表で表示されました!

    これをテストで作ったDropDownListとストアドプロシージャとリンクしたGridViewで試したところこちらもちゃんと表示され、

    しかも会社を選ぶと集計結果がすぐに正しく表示されるようになりました!

     

    これで当初の目的を達成したので非常にすっきりしました。

    本当にいろいろとありがとうございました。

    2008年7月4日 0:37
  • > ストアドプロシージャのnvarchar(10)のところをnvarchar(50)に全部変えてストアドプロシージャを作り直してみたところ

     

    テーブルの定義に合わせて正しいストアドプロシージャを作成しましょう。

    2008年7月4日 4:32
  •  tosaito さんからの引用

    これをテストで作ったDropDownListとストアドプロシージャとリンクしたGridViewで試したところこちらもちゃんと表示され、

    しかも会社を選ぶと集計結果がすぐに正しく表示されるようになりました!

     

    既に解決済みのようで、今頃になってレスするのもなんですが、一つ気になることがあり

    ましたのでレスさせていただきました。

     

    DropDownList で会社(テーブル上は Company ですよね?)を選んで GridView に

    その会社のみのデータを抽出して表示させるということと思いますが、その場合もう一

    箇所で抽出条件が必要ではないでしょうか?

     

    つまり、

     

    FROM (select datepart(mm, _1) as tuki, _15 FROM UsageLog_past12Ms)

     

    というところで、

     

    FROM (select datepart(mm, _1) as tuki, _15 FROM UsageLog_past12Ms WHERE Company=@Company)

     

    とするとか。

     

    間違っていたら失礼しました。

    2008年7月7日 14:41
  •  SurferOnWww さんからの引用

    FROM (select datepart(mm, _1) as tuki, _15 FROM UsageLog_past12Ms)

     

    というところで、

     

    FROM (select datepart(mm, _1) as tuki, _15 FROM UsageLog_past12Ms WHERE Company=@Company)

     

    とするとか。


    するどいですね。本当はtosaitoさんから言われるかな?と思っていたのですが、言われなかったのでこのままの仕様で良いのか、もしくはtosaitoさんの方で修正されたのかと思っています。

     

    結論から言えば

     

    FROM (select datepart(mm, _1) as tuki, _15 FROM UsageLog_past12Ms)

     

    でもかまいません。この場合、他のcompanyのデータが存在する月で、かつ、当該companyのデータが無い月は0と表示されます。

     

    SurferOnWwwさんが指摘されたように、

     

    FROM (select datepart(mm, _1) as tuki, _15 FROM UsageLog_past12Ms WHERE Company=@Company)

     

    にすると、当該companyのデータが存在する月のみが表示されます。

    後者の方が一般的に素直だと思いますが、仕様がはっきりわからなかったため、SQL文がより簡単な方をとりあえず載せました。

    ちなみに、私が提示したストアドプロシージャに合わせるのであれば、以下のようになります。

     

    FROM (select datepart(mm, _1) as tuki, _15 FROM UsageLog_past12Ms WHERE Company = case when @Company != '''' then @Company else Company end

    2008年7月8日 2:04
    モデレータ