How to create multiple ControlParameter in FilterParameters for SqlDatasource in ASP.Net

Kibock

SOLVED CLOSED ACTIVE ANSWERED
User: Kibock
Posted: on Dec 05, 2016 02:06 AM
Forum: Data Controls
Answer: 1
Views: 8803
Sample Code: Download

Dear All,

I want to filter a Database GridView from 4 different Dropdownlist Selection:

HTML Code:

<table id="tableColor" runat="server" style="margin: 10px;">
    <tr>
        <td nowrap="nowrap" bgcolor="#90ee90" style="background-color: #808080; color: #FFFFFF">
            Course name:
        </td>
        <td>
            <asp:DropDownList ID="ddlSearchCourseName" Width="230px" runat="server" AutoPostBack="true"
                DataSourceID="dropDownSearchCourseName" DataTextField="CourseName" DataValueField="CourseName"
                AppendDataBoundItems="true">
                <asp:ListItem Text="Select Course Name" Value="" />
            </asp:DropDownList>
            <asp:SqlDataSource ID="dropDownSearchCourseName" runat="server" ConnectionString="<%$ ConnectionStrings:DatabaseVestConnectionString1 %>"
                SelectCommand="SELECT DISTINCT CourseName FROM vts_Register"></asp:SqlDataSource>
        </td>
        <td nowrap="nowrap" bgcolor="#90ee90" style="background-color: #808080; color: #FFFFFF">
            Course year:
        </td>
        <td>
            <asp:DropDownList ID="ddlSearchCourseYear" Width="230px" runat="server" AutoPostBack="true"
                DataSourceID="dropDownSearchCourseYear" DataTextField="CourseReferanseNumber"
                DataValueField="CourseReferanseNumber" AppendDataBoundItems="true">
                <asp:ListItem Text="Select Year" Value="" />
            </asp:DropDownList>
            <asp:SqlDataSource ID="dropDownSearchCourseYear" runat="server" ConnectionString="<%$ ConnectionStrings:DatabaseVestConnectionString1 %>"
                SelectCommand="SELECT DISTINCT CourseReferanseNumber FROM vts_Register"></asp:SqlDataSource>
        </td>
        <td rowspan="2">
        </td>
    </tr>
    <tr>
        <td nowrap="nowrap" bgcolor="#90ee90" style="background-color: #808080; color: #FFFFFF">
            Course type:
        </td>
        <td>
            <asp:DropDownList ID="ddlSearchCourseType" Width="230px" runat="server" AutoPostBack="true"
                DataSourceID="dropDownSearchCourseType" DataTextField="CourseType" DataValueField="CourseType"
                AppendDataBoundItems="true">
                <asp:ListItem Text="Select Course Type" Value="" />
            </asp:DropDownList>
            <asp:SqlDataSource ID="dropDownSearchCourseType" runat="server" ConnectionString="<%$ ConnectionStrings:DatabaseVestConnectionString1 %>"
                SelectCommand="SELECT DISTINCT CourseType FROM vts_Register"></asp:SqlDataSource>
        </td>
        <td nowrap="nowrap" bgcolor="#90ee90" style="background-color: #808080; color: #FFFFFF">
            Course week:
        </td>
        <td>
            <asp:DropDownList ID="ddlSearchCourseWeek" Width="230px" runat="server" AutoPostBack="true"
                DataSourceID="dropDownSearchCourseWeek" DataTextField="CourseUkeNummer" DataValueField="CourseUkeNummer"
                AppendDataBoundItems="true">
                <asp:ListItem Text="Select Course Week" Value="" />
            </asp:DropDownList>
            <asp:SqlDataSource ID="dropDownSearchCourseWeek" runat="server" ConnectionString="<%$ ConnectionStrings:DatabaseVestConnectionString1 %>"
                SelectCommand="SELECT DISTINCT CourseUkeNummer FROM vts_Register"></asp:SqlDataSource>
        </td>
    </tr>
</table>

DataSource:

<asp:SqlDataSource ID="vts_RegisterDataSource" runat="server" ConnectionString="<%$ ConnectionStrings:DatabaseVestConnectionString1 %>"
    DeleteCommand="DELETE FROM [vts_Register] WHERE [CourseID] = @CourseID" InsertCommand="INSERT INTO [vts_Register] ([CourseStatus], [CourseArrangeur], [CourseName], [CourseType], [CourseFagOmrade], [CourseLokalAdresse], [CourseReferanseNumber], [CourseStartDato], [CourseSluttDato], [CourseTotalDager], [CourseUkeNummer], [CourseTotalPlasse], [CourseAntallLedigPlasse], [CoursePameldingFraDato], [CourseDescription], [AdminDocumentName], [AdminOfficerOfCaseName], [AdminOfficerOfCaseEmail], [CourseRegistreringDato]) VALUES (@CourseStatus, @CourseArrangeur, @CourseName, @CourseType, @CourseFagOmrade, @CourseLokalAdresse, @CourseReferanseNumber, @CourseStartDato, @CourseSluttDato, @CourseTotalDager, @CourseUkeNummer, @CourseTotalPlasse, @CourseAntallLedigPlasse, @CoursePameldingFraDato, @CourseDescription, @AdminDocumentName, @AdminOfficerOfCaseName, @AdminOfficerOfCaseEmail, @CourseRegistreringDato)"
    UpdateCommand="UPDATE [vts_Register] SET [CourseStatus] = @CourseStatus, [CourseArrangeur] = @CourseArrangeur, [CourseName] = @CourseName, [CourseType] = @CourseType, [CourseFagOmrade] = @CourseFagOmrade, [CourseLokalAdresse] = @CourseLokalAdresse, [CourseReferanseNumber] = @CourseReferanseNumber, [CourseStartDato] = @CourseStartDato, [CourseSluttDato] = @CourseSluttDato, [CourseTotalDager] = @CourseTotalDager, [CourseUkeNummer] = @CourseUkeNummer, [CourseTotalPlasse] = @CourseTotalPlasse, [CourseAntallLedigPlasse] = @CourseAntallLedigPlasse, [CoursePameldingFraDato] = @CoursePameldingFraDato, [CourseDescription] = @CourseDescription, [AdminDocumentName] = @AdminDocumentName, [AdminOfficerOfCaseName] = @AdminOfficerOfCaseName, [AdminOfficerOfCaseEmail] = @AdminOfficerOfCaseEmail, [CourseRegistreringDato] = @CourseRegistreringDato WHERE [CourseID] = @CourseID"
    SelectCommand="SELECT * FROM [vts_Register]" FilterExpression="CourseStatus = '{0}' AND CourseName LIKE '%{1}%'">
    <FilterParameters>
        <asp:ControlParameter Name="CourseStatus" ControlID="ddlCourseStatus" PropertyName="SelectedValue"
            Type="String" />
        <asp:ControlParameter Name="CourseName" ControlID="ddlSearchCourseName" PropertyName="SelectedValue"
            Type="String" />
    </FilterParameters>
</asp:SqlDataSource>

How to configure these parameters to respond from the 4 Dropdownlist Selections ?

Many thanks in advance

Indresh

0 1

Reply
User: Indresh
Replied: on Dec 05, 2016 09:59 AM
Report

Hi Kibock,

I will get back to you tomorrow.

Indresh

0 2

Reply
ANSWER
User: Indresh
Replied: on Dec 06, 2016 02:57 AM
Report

Hi Kibock,

I have created sample code which fullfill your requirement.

HTML

<form id="form1" runat="server">
<table border="0" cellpadding="0" cellspacing="0">
    <tr>
        <td>
            CompanyName:
            <asp:DropDownList ID="ddlCompanyName" runat="server" AutoPostBack="true" DataSourceID="GetCompanyName"
                DataTextField="CompanyName" DataValueField="CompanyName" AppendDataBoundItems="true">
                <asp:ListItem Text="Please select" Value="" />
            </asp:DropDownList>
            <asp:SqlDataSource ID="GetCompanyName" runat="server" ConnectionString="<%$ ConnectionStrings:Constring %>"
                SelectCommand="SELECT CompanyName FROM Customers"></asp:SqlDataSource>
        </td>
        <td>
            ContactName :<asp:DropDownList ID="ddlContactName" runat="server" AutoPostBack="true"
                DataSourceID="GetContactName" DataTextField="ContactName" DataValueField="ContactName"
                AppendDataBoundItems="true">
                <asp:ListItem Text="Select Contact Name" Value="" />
            </asp:DropDownList>
            <asp:SqlDataSource ID="GetContactName" runat="server" ConnectionString="<%$ ConnectionStrings:Constring %>"
                SelectCommand="SELECT ContactName FROM Customers"></asp:SqlDataSource>
        </td>
        <td>
            City:
            <asp:DropDownList ID="ddlCity" runat="server" AutoPostBack="true" DataSourceID="GetCityName"
                DataTextField="City" DataValueField="City" AppendDataBoundItems="true">
                <asp:ListItem Text="Select City Name" Value="" />
            </asp:DropDownList>
            <asp:SqlDataSource ID="GetCityName" runat="server" ConnectionString="<%$ ConnectionStrings:Constring %>"
                SelectCommand="SELECT City FROM Customers"></asp:SqlDataSource>
        </td>
        <td>
            Country:
            <asp:DropDownList ID="ddlCountry" runat="server" AutoPostBack="true" DataSourceID="GetCountryName"
                DataTextField="Country" DataValueField="Country" AppendDataBoundItems="true">
                <asp:ListItem Text="Select Country Name" Value="" />
            </asp:DropDownList>
            <asp:SqlDataSource ID="GetCountryName" runat="server" ConnectionString="<%$ ConnectionStrings:Constring %>"
                SelectCommand="SELECT Country FROM Customers"></asp:SqlDataSource>
        </td>
    </tr>
</table>
<br />
<asp:GridView ID="gvCustomers" DataSourceID="vts_RegisterDataSource" AutoGenerateColumns="false"
    runat="server">
    <Columns>
        <asp:BoundField DataField="CustomerId" HeaderText="CustomerId" />
        <asp:BoundField DataField="CompanyName" HeaderText="CompanyName" />
        <asp:BoundField DataField="ContactName" HeaderText="ContactName" />
        <asp:BoundField DataField="City" HeaderText="City" />
        <asp:BoundField DataField="Country" HeaderText="Country" />
        <asp:BoundField DataField="Address" HeaderText="Address" />
    </Columns>
</asp:GridView>
<asp:SqlDataSource ID="vts_RegisterDataSource" runat="server" ConnectionString="<%$ ConnectionStrings:Constring %>"
    SelectCommand="SELECT TOp 15 * FROM Customers" FilterExpression="CompanyName='{0}' AND ContactName='{1}' AND City='{2}' And Country='{3}'">
    <FilterParameters>
        <asp:ControlParameter Name="CompanyName" ControlID="ddlCompanyName" PropertyName="SelectedValue" />
        <asp:ControlParameter Name="ContactName" ControlID="ddlContactName" PropertyName="SelectedValue" />
        <asp:ControlParameter Name="City" ControlID="ddlCity" PropertyName="SelectedValue" />
        <asp:ControlParameter Name="Country" ControlID="ddlCountry" PropertyName="SelectedValue" />
    </FilterParameters>
</asp:SqlDataSource>
</form>

Screenshot

How to create multiple ControlParameter in FilterParameters for SqlDatasource in ASP.Net

Company

Explore

Follow us

Contact Us