locked
Need help with adding sql select options to gridview select command RRS feed

  • Question

  • User615288127 posted

    I'm using Visual Studio 2015 with SQL Server 2014, VB.

    I have the command below and would like to add to it but don't know the correct syntax or even where to begin.  I would like to add the ability to sense the condition where if DD1 (dropdownlist1) specifies the text "User Activity" then the select will look for the column [CreatedBy] to be equal to DD4 (dropdownlist4).  If DD1 does not equal "User Activity" then this condition is to be disregarded.  What I have currently works fine as it stands.

            <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:CRAPConnectionString315 %>" SelectCommand="SELECT [PART_NUMBER], [Table], format([CreateDate],'MM/dd/yyyy') as [CreateDate], [CreatedBy], [Project] FROM [PTNOTAB] WHERE ((CAST([CreateDate] AS Date) &gt;= @CreateDate) AND ([Table] = 'Capacitors'and @Table2=1 AND @DD5=2)) ORDER BY [PART_NUMBER]">
                <SelectParameters>
                    <asp:ControlParameter ControlID="Calendar1" Name="CreateDate" PropertyName="SelectedDate" Type="DateTime" />
                    <asp:ControlParameter ControlID="RadioButton2" Name="Table2" PropertyName="Checked" Type="Boolean"/>
                    <asp:ControlParameter ControlID="RadioButton3" Name="Table3" PropertyName="Checked" Type="Boolean"/>
                    <asp:ControlParameter ControlID="RadioButton4" Name="Table4" PropertyName="Checked" Type="Boolean"/>
                    <asp:ControlParameter ControlID="RadioButton5" Name="Table5" PropertyName="Checked" Type="Boolean"/>
                    <asp:ControlParameter ControlID="RadioButton6" Name="Table6" PropertyName="Checked" Type="Boolean"/>
                    <asp:ControlParameter ControlID="RadioButton7" Name="Table7" PropertyName="Checked" Type="Boolean"/>
                    <asp:ControlParameter ControlID="RadioButton8" Name="Table8" PropertyName="Checked" Type="Boolean"/>
                    <asp:ControlParameter ControlID="RadioButton9" Name="Table9" PropertyName="Checked" Type="Boolean"/>
                    <asp:ControlParameter ControlID="RadioButton10" Name="Table10" PropertyName="Checked" Type="Boolean"/>
                    <asp:ControlParameter ControlID="RadioButton11" Name="Table11" PropertyName="Checked" Type="Boolean"/>
                    <asp:ControlParameter ControlID="RadioButton12" Name="Table12" PropertyName="Checked" Type="Boolean"/>
                    <asp:ControlParameter ControlID="RadioButton13" Name="Table13" PropertyName="Checked" Type="Boolean"/>
                    <asp:ControlParameter ControlID="Dropdownlist1" Name="DD1" PropertyName="SelectedValue" Type="String" />
                    <asp:ControlParameter ControlID="Dropdownlist4" Name="DD4" PropertyName="SelectedValue" Type="String" />
                    <asp:ControlParameter ControlID="Dropdownlist5" Name="DD5" PropertyName="SelectedIndex" Type="Int64" />
                </SelectParameters>
            </asp:SqlDataSource>

    Any help will be greatly appreciated by this novice user.  Thanks!

    Monday, January 16, 2017 3:41 PM

Answers

  • User615288127 posted

    I figured it out, the solution is below:

            <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:CRAPConnectionString315 %>" SelectCommand="SELECT [PART_NUMBER], [Table], format([CreateDate],'MM/dd/yyyy') as [CreateDate], [CreatedBy], [Project] FROM [PTNOTAB] WHERE ((CAST([CreateDate] AS Date) &gt;= @CreateDate) AND ([Table] = 'Capacitors'and @Table2=1 AND @DD5=2) AND ((@DD4 = [CreatedBy] AND @DD1 = 2) OR (@DD1 = 1))) ORDER BY [PART_NUMBER]">
                <SelectParameters>
                    <asp:ControlParameter ControlID="Calendar1" Name="CreateDate" PropertyName="SelectedDate" Type="DateTime" />
                    <asp:ControlParameter ControlID="RadioButton2" Name="Table2" PropertyName="Checked" Type="Boolean"/>
                    <asp:ControlParameter ControlID="RadioButton3" Name="Table3" PropertyName="Checked" Type="Boolean"/>
                    <asp:ControlParameter ControlID="RadioButton4" Name="Table4" PropertyName="Checked" Type="Boolean"/>
                    <asp:ControlParameter ControlID="RadioButton5" Name="Table5" PropertyName="Checked" Type="Boolean"/>
                    <asp:ControlParameter ControlID="RadioButton6" Name="Table6" PropertyName="Checked" Type="Boolean"/>
                    <asp:ControlParameter ControlID="RadioButton7" Name="Table7" PropertyName="Checked" Type="Boolean"/>
                    <asp:ControlParameter ControlID="RadioButton8" Name="Table8" PropertyName="Checked" Type="Boolean"/>
                    <asp:ControlParameter ControlID="RadioButton9" Name="Table9" PropertyName="Checked" Type="Boolean"/>
                    <asp:ControlParameter ControlID="RadioButton10" Name="Table10" PropertyName="Checked" Type="Boolean"/>
                    <asp:ControlParameter ControlID="RadioButton11" Name="Table11" PropertyName="Checked" Type="Boolean"/>
                    <asp:ControlParameter ControlID="RadioButton12" Name="Table12" PropertyName="Checked" Type="Boolean"/>
                    <asp:ControlParameter ControlID="RadioButton13" Name="Table13" PropertyName="Checked" Type="Boolean"/>
                    <asp:ControlParameter ControlID="Dropdownlist1" Name="DD1" PropertyName="SelectedIndex" Type="Int64"/>
                    <asp:ControlParameter ControlID="Dropdownlist4" Name="DD4" PropertyName="SelectedValue" Type="String"/>
                    <asp:ControlParameter ControlID="Dropdownlist5" Name="DD5" PropertyName="SelectedIndex" Type="Int64"/>
                </SelectParameters>
            </asp:SqlDataSource>
    

    Thanks all!

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, January 17, 2017 12:43 AM