HiI need help on ldquoSelect Allrdquo When I select each of the department and its return the data but then when I do the ldquoSelect Allrdquo itrsquos return nothing but without error So I donrsquot know what is going onHere is html code ltaspPanel ID34AdminPanel34 runat34server34 Visible34False34gt ltpgt Search by Dept ltaspDropDownList ID34DropDownList134 runat34server34 AutoPostBack34Tr

Hello phonghuePlease refer the below codesDatabase1For this example I have used of Northwind database that you can download using the link given belowDownload Northwind DatabaseHTMLltaspPanel ID34AdminPanel34 runat34server34 Visible34True34gt ltpgtSearch by Title ltaspDropDownList ID34DropDownList134 runat34server34 AutoPostBack34True34 OnSelectedIndexChanged34DropDownList1SelectedIndexChanged34gt ltaspListItem Value3434gtSelect AllltaspListItemgt ltaspListItem Value34Sales Representative34gtSales RepresentativeltaspListItemgt ltaspListItem Value34Vice President Sales34gtVice PresidentltaspListItemgt ltaspListItem Value34Sales Manager34gtSales ManagerltaspListItemgt ltaspListItem Value34Inside Sales Coordinator34gtInside Sales CoordinatorltaspListItemgt ltaspDropDownListgt ampnbsp

Filter records using ASP.Net SqlDataSource using C# and VB.Net

phonghue

SOLVED CLOSED ACTIVE ANSWERED
User: phonghue
Posted: on Aug 15, 2024 10:48 PM
Forum: Data Controls
Answer: 1
Views: 1610
Sample Code: Download

Hi,

I need help on “Select All”. When I select each of the department and it's return the data, but then when I do the “Select All”, it’s return nothing, but without error. So I don’t know what is going on.

Here is html code:

    <asp:Panel ID="AdminPanel" runat="server" Visible="False">
        <p>
            Search by Dept:
            <asp:DropDownList ID="DropDownList1" runat="server" AutoPostBack="True">
                <asp:ListItem Value="">--Select All--</asp:ListItem>
                <asp:ListItem>CAS</asp:ListItem>
                <asp:ListItem Value="HESC">HESC/PUBH</asp:ListItem>
                <asp:ListItem>HUSR</asp:ListItem>
                <asp:ListItem>KNES</asp:ListItem>
                <asp:ListItem>NURS</asp:ListItem>
            </asp:DropDownList>
            &nbsp;</p>
    </asp:Panel>
    <asp:ListView ID="ListView1" runat="server" DataKeyNames="MentorProfileId" DataSourceID="SqlDataSource1">
        <EmptyDataTemplate>
            <span>No mentor is available at this time.</span>
        </EmptyDataTemplate>
        <ItemTemplate>
            <div class="row border">
                <div class="col-md-4">
                    <p>
                        <span class="lead font-weight-bold">Name:
                        <asp:Label ID="Fullname" runat="server" Text='<%# Eval("Fullname")%>' /></span> 
                        <br />
                        <u>Dept:</u>
                        <asp:Label ID="DeptLabel" runat="server" Text='<%# Eval("Dept") %>' />
                        <br />
                        <u>Concentration:</u>
                        <asp:Label ID="DeptConcentrationLabel" runat="server" Text='<%# Eval("DeptConcentration") %>' />
                    </p>
                    <p>
                        <asp:Image ID="Image1" runat="server" CssClass="img-thumbnail" /><%--Width="200px" Height="266px"--%>
                    </p>
                </div>
                <div class="col-md-8">
                    <strong>Profile Statement:</strong>
                    <br />
                    <asp:Label ID="MentorInterestLabel" runat="server" Text='<%# Eval("MentorInterest") %>' />
                    <br />
                    <br />
                    <strong>Weekly available schedule:</strong>
                    <br />
                    <br />
                    <strong>Monday: </strong>
                    <asp:Label ID="MonScheduleLabel" runat="server" Text='<%# Eval("MonSchedule") %>' />
                    <br />
                    <strong>Tuesday: </strong>
                    <asp:Label ID="TueScheduleLabel" runat="server" Text='<%# Eval("TueSchedule") %>' />
                    <br />
                    <strong>Wednesday:</strong>
                    <asp:Label ID="WedScheduleLabel" runat="server" Text='<%# Eval("WedSchedule") %>' />
                    <br />
                    <strong>Thursday:</strong>
                    <asp:Label ID="ThurScheduleLabel" runat="server" Text='<%# Eval("ThurSchedule") %>' />
                    <br />
                    <strong>Friday:</strong>
                    <asp:Label ID="FriScheduleLabel" runat="server" Text='<%# Eval("FriSchedule") %>' />
                    <br />
                    <strong>Saturday:</strong>
                    <asp:Label ID="SatScheduleLabel" runat="server" Text='<%# Eval("SatSchedule") %>' />
                    <br />
                    <strong>Sunday:</strong>
                    <asp:Label ID="SunScheduleLabel" runat="server" Text='<%# Eval("SunSchedule") %>' />
                    <br />
                    <br />
                    <asp:Literal ID="Literal1" runat="server"></asp:Literal>
                    <br />
                    <br />
                </div>
            </div>
            <p class="clearfloat">&nbsp;</p>
        </ItemTemplate>
        <LayoutTemplate>
            <div id="itemPlaceholderContainer" runat="server" style="">
                <span runat="server" id="itemPlaceholder" style="border:1px solid #CCC; padding:3px;" />
            </div>
            <div style="">
            </div>
        </LayoutTemplate>
    </asp:ListView>
    <asp:SqlDataSource ID="SqlDataSource1" runat="server" 
        ConnectionString="<%$ ConnectionStrings:CHHDPMPConnectionString %>" 
        SelectCommand="SELECT [mFirstN]+' '+[mLastN] As Fullname, * FROM [vwMentorProfileCount] WHERE ([SemYear] = @SemYear) AND ([Dept] = @Dept) ORDER BY [Dept], [menteecount]">
        <SelectParameters>
            <asp:Parameter DefaultValue="Fall2024" Name="SemYear" Type="String" />
            <%--<asp:Parameter DefaultValue="HURS" Name="Dept" Type="String" />--%>
           <%-- <asp:ControlParameter ControlID="DeptLabel" Name="Dept" PropertyName="Text" Type="String" />--%>
        </SelectParameters>
    </asp:SqlDataSource>

here is VB Code:

Protected Sub DropDownList1_SelectedIndexChanged(sender As Object, e As EventArgs) Handles DropDownList1.SelectedIndexChanged
    If DropDownList1.SelectedValue <> "" Then
        'All
        SqlDataSource1.SelectCommand = "SELECT * FROM vwMentorProfileCount WHERE SemYear='Fall2024' ORDER BY Dept, mLastN"
    End If
    SqlDataSource1.SelectCommand = "SELECT [mFirstN]+' '+[mLastN] As Fullname, * FROM [vwMentorProfileCount] WHERE ([SemYear] ='Fall2024') AND ([Dept] = '" & DropDownList1.SelectedValue & "') ORDER BY menteecount"
    ListView1.DataBind()

Download FREE API for Word, Excel and PDF in ASP.Net

Jeff

0 2

Reply
ANSWER
User: Jeff
Replied: on Aug 16, 2024 09:03 AM
Report

Hello phonghue,

Please refer the below codes.

Database

1For this example I have used of Northwind database that you can download using the link given below.

Download Northwind Database

HTML

<asp:Panel ID="AdminPanel" runat="server" Visible="True">
  <p>Search by Title:
  <asp:DropDownList ID="DropDownList1" runat="server" AutoPostBack="True" OnSelectedIndexChanged="DropDownList1_SelectedIndexChanged">
      <asp:ListItem Value="">--Select All--</asp:ListItem>
      <asp:ListItem Value="Sales Representative">Sales Representative</asp:ListItem>
      <asp:ListItem Value="Vice President, Sales">Vice President</asp:ListItem>
      <asp:ListItem Value="Sales Manager">Sales Manager</asp:ListItem>
      <asp:ListItem Value="Inside Sales Coordinator">Inside Sales Coordinator</asp:ListItem>
  </asp:DropDownList>
      &nbsp;
  </p>
</asp:Panel>
<asp:ListView ID="ListView1" runat="server" DataKeyNames="EmployeeId" DataSourceID="SqlDataSource1">
    <EmptyDataTemplate>
          <span>No mentor is available at this time.</span>
    </EmptyDataTemplate>
    <ItemTemplate>
          <div class="row border">
              <div class="col-md-4">
                  <p>
                      <span class="lead font-weight-bold">Name:
              <asp:Label ID="Fullname" runat="server" Text='<%# Eval("FirstName")%>' /></span>
                      <br />
                      <u>Title:</u>
                      <asp:Label ID="TitleLabel" runat="server" Text='<%# Eval("Title") %>' />
                      <br />
                      <u>Country</u>
                      <asp:Label ID="CountryonLabel" runat="server" Text='<%# Eval("Country") %>' />
                  </p>
              </div>
              <%--<div class="col-md-8">
                  <strong>Profile Statement:</strong>
                  <br />
                  <asp:Label ID="MentorInterestLabel" runat="server" Text='<%# Eval("MentorInterest") %>' />
                  <br />
                  <br />
                  <strong>Weekly available schedule:</strong>
                  <br />
                  <br />
                  <strong>Monday: </strong>
                  <asp:Label ID="MonScheduleLabel" runat="server" Text='<%# Eval("MonSchedule") %>' />
                  <br />
                  <strong>Tuesday: </strong>
                  <asp:Label ID="TueScheduleLabel" runat="server" Text='<%# Eval("TueSchedule") %>' />
                  <br />
                  <strong>Wednesday:</strong>
                  <asp:Label ID="WedScheduleLabel" runat="server" Text='<%# Eval("WedSchedule") %>' />
                  <br />
                  <strong>Thursday:</strong>
                  <asp:Label ID="ThurScheduleLabel" runat="server" Text='<%# Eval("ThurSchedule") %>' />
                  <br />
                  <strong>Friday:</strong>
                  <asp:Label ID="FriScheduleLabel" runat="server" Text='<%# Eval("FriSchedule") %>' />
                  <br />
                  <strong>Saturday:</strong>
                  <asp:Label ID="SatScheduleLabel" runat="server" Text='<%# Eval("SatSchedule") %>' />
                  <br />
                  <strong>Sunday:</strong>
                  <asp:Label ID="SunScheduleLabel" runat="server" Text='<%# Eval("SunSchedule") %>' />
                  <br />
                  <br />
                  <asp:Literal ID="Literal1" runat="server"></asp:Literal>
                  <br />
                  <br />
              </div>--%>
          </div>
          <p class="clearfloat">&nbsp;</p>
    </ItemTemplate>
    <LayoutTemplate>
          <div id="itemPlaceholderContainer" runat="server" style="">
              <span runat="server" id="itemPlaceholder" style="border: 1px solid #CCC; padding: 3px;" />
          </div>
          <div style="">
          </div>
    </LayoutTemplate>
</asp:ListView>
<asp:SqlDataSource ID="SqlDataSource1" runat="server"
      ConnectionString="<%$ ConnectionStrings:constr %>"
      SelectCommand="SELECT FirstName As Fullname, * FROM [EMPLOYEES] WHERE ([Country] = @Country) AND ([Title] = @Title) ORDER BY [EmployeeId]">
      <SelectParameters>
          <asp:Parameter DefaultValue="USA" Name="Country" Type="String" />
          <asp:Parameter DefaultValue="Sales Representative" Name="Title" Type="String" />
      </SelectParameters>
</asp:SqlDataSource>

Code

protected void DropDownList1_SelectedIndexChanged(object sender, EventArgs e)
{
      if (string.IsNullOrEmpty(DropDownList1.SelectedValue))
      {
          // All
          SqlDataSource1.SelectCommand = "SELECT FirstName As Fullname, * FROM Employees ORDER BY EmployeeId";
      }
      else
      {
          SqlDataSource1.SelectCommand = "SELECT FirstName As Fullname, * FROM Employees WHERE ([Country] ='USA') AND (Title = '" + DropDownList1.SelectedValue + "') ORDER BY EmployeeId";
      }
      ListView1.DataBind();
}

VB.Net

Protected Sub DropDownList1_SelectedIndexChanged(ByVal sender As Object, ByVal e As EventArgs)
     If String.IsNullOrEmpty(DropDownList1.SelectedValue) Then
         SqlDataSource1.SelectCommand = "SELECT FirstName As Fullname, * FROM Employees ORDER BY EmployeeId"
     Else
         SqlDataSource1.SelectCommand = "SELECT FirstName As Fullname, * FROM Employees WHERE ([Country] ='USA') AND (Title = '" & DropDownList1.SelectedValue & "') ORDER BY EmployeeId"
     End If

     ListView1.DataBind()
End Sub

Screenshot

I agree, here is the link: https://www.e-iceblue.com/Introduce/spire-office-for-net-free.html

Filter records using ASP.Net SqlDataSource using C# and VB.Net

Company

Explore

Follow us

Contact Us