Solved

Gridview Filtering from a DropDown Box....  What to do?!?!?!?!

Posted on 2014-11-20
4
284 Views
Last Modified: 2014-12-02
Here is my quandary....

I have a project with a GridView that is filtering records based on a selected Team in a DropDownList having entries for TeamA, TeamB, TeamC... which looks for a list of Distinct teams in the ContactUs table I want to display.  I defined a SqlDataSource (named SqlDataSource1) that has "WHERE" specifications based on the DDL Selected value.  All works great, but I have another requirement now to be able to display ALL records in the table.   So, I decided on testing this approach, and not sure if it is right.

My thought is,

1)  Add a ListItem ALL option to the DDL control
2) Define a new SqlDataSource (named SqlDataSourceAllTeams) that does not use a WHERE condition.
   
Is this logical?    I have listed my datasource definitions and sample code-behind, (best guess)

Filtering Teams definition
<asp:SqlDataSource ID="SqlDataSource1" runat="server"
                    ConnectionString="<%$ ConnectionStrings:dbConnectionString %>"
                    SelectCommand="SELECT [ContactUs_key], [ContactUs_intended_for], [ContactUs_status], [ContactUs_UID], [ContactUs_first_name], [ContactUs_last_name], [ContactUs_subject], [ContactUs_date_entered], [ContactUs_updatedDate], [ContactUs_updatedBy], 
CASE [ContactUs_status] WHEN 'N' THEN 'New' WHEN 'I' THEN 'In Process' WHEN 'C' THEN 'Complete' END AS ContactUs_status_phrase, 
CASE [ContactUs_status] WHEN 'C' THEN 1 ELSE 0 END AS Status_Sort FROM [ContactUs] 
WHERE [ContactUs_intended_for] = @ContactUs_intended_for 
ORDER BY [Status_Sort] ASC, [ContactUs_date_entered] DESC"

                <SelectParameters>
                        <asp:ControlParameter ControlID="ddlContactUs" Name="ContactUs_intended_for" PropertyName="SelectedValue" Type="String" />
                </SelectParameters>

Open in new window



ALL Teams definition
                <asp:SqlDataSource ID="SqlDataSourceAllTeams" runat="server" 
                    ConnectionString="<%$ ConnectionStrings:dbConnectionString %>" 
                    SelectCommand="SELECT [ContactUs_key], [ContactUs_intended_for], [ContactUs_status], [ContactUs_UID], [ContactUs_first_name], [ContactUs_last_name], [ContactUs_subject], [ContactUs_date_entered], [ContactUs_updatedDate], [ContactUs_updatedBy], 
CASE [ContactUs_status] WHEN 'N' THEN 'New' WHEN 'I' THEN 'In Process' WHEN 'C' THEN 'Complete' END AS ContactUs_status_phrase, 
CASE [ContactUs_status] WHEN 'C' THEN 1 ELSE 0 END AS Status_Sort FROM [ContactUs] 
ORDER BY [Status_Sort] ASC, [ContactUs_date_entered] DESC" >
                </asp:SqlDataSource>

Open in new window


Can I check the Drop down for ALL and change the DataSource definition for the GridView?  

Something like....

code-behind

if (option == 'ALL") 
{
GridView1.DataSourceID.Text = "SqlDataSourceAllTeams"
GridView1.DataBind();
} 
else
{
GridView1.DataSourceID.Text = "SqlDataSource1"
GridView1.DataBind();
} 

Open in new window

Just not sure on the code-behind, being logical that is

Help Anyone?

Bob
0
Comment
Question by:Bob Bender
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
4 Comments
 
LVL 13

Expert Comment

by:jitendra patil
ID: 40456726
yes good to add a ListItem ALL option to the DDL control

don't take a new data source on the page, as it doesn't look good as well as illogical,  

i would suggest to use the drop down to fire a selected index change event and in the code behind you can configure your sql select command for one single data source.

try the below code in your ddlselectedindexchanged event
if (Selectedvalue== "ALL") 
{
SqlDataSource1.SelectCommand="SELECT [ContactUs_key], [ContactUs_intended_for], [ContactUs_status], [ContactUs_UID], [ContactUs_first_name], [ContactUs_last_name], [ContactUs_subject], [ContactUs_date_entered], [ContactUs_updatedDate], [ContactUs_updatedBy], 
CASE [ContactUs_status] WHEN 'N' THEN 'New' WHEN 'I' THEN 'In Process' WHEN 'C' THEN 'Complete' END AS ContactUs_status_phrase, 
CASE [ContactUs_status] WHEN 'C' THEN 1 ELSE 0 END AS Status_Sort FROM [ContactUs] 
ORDER BY [Status_Sort] ASC, [ContactUs_date_entered] DESC";
GridView1.DataSourceID.Text = "SqlDataSourceAllTeams"
GridView1.DataBind();
} 
else
{
SqlDataSource1.SelectCommand="SELECT [ContactUs_key], [ContactUs_intended_for], [ContactUs_status], [ContactUs_UID], [ContactUs_first_name], [ContactUs_last_name], [ContactUs_subject], [ContactUs_date_entered], [ContactUs_updatedDate], [ContactUs_updatedBy], 
CASE [ContactUs_status] WHEN 'N' THEN 'New' WHEN 'I' THEN 'In Process' WHEN 'C' THEN 'Complete' END AS ContactUs_status_phrase, 
CASE [ContactUs_status] WHEN 'C' THEN 1 ELSE 0 END AS Status_Sort FROM [ContactUs] 
WHERE [ContactUs_intended_for] ="+ SelectedValue.ToString()+"
ORDER BY [Status_Sort] ASC, [ContactUs_date_entered] DESC"
GridView1.DataSourceID.Text = "SqlDataSource1"
GridView1.DataBind();
} 

Open in new window


hope this helps
0
 
LVL 5

Author Comment

by:Bob Bender
ID: 40458436
Well, now, I am confused....

You mentioned:
i would suggest to use the drop down to fire a selected index change event and in the code behind you can configure your sql select command for one single data source.
But in the code, the All option  is  GridView1.DataSourceID.Text = "SqlDataSourceAllTeams"

While the Else condition is  GridView1.DataSourceID.Text = "SqlDataSource1"

Was this an oversight and they both should reflect "SqlDataSource1"????

Bob
0
 
LVL 13

Accepted Solution

by:
jitendra patil earned 500 total points
ID: 40459005
its very simple just keep a  single data source on your page for gridview binding and configure the same datasource in the dropdown list selected index change event.

you dont' need to keep seperate datasources for all option in the markup.

and yes there is a typo mistake in the code given above, please use only the single datasource
if (Selectedvalue== "ALL") 
{
SqlDataSource1.SelectCommand="SELECT [ContactUs_key], [ContactUs_intended_for], [ContactUs_status], [ContactUs_UID], [ContactUs_first_name], [ContactUs_last_name], [ContactUs_subject], [ContactUs_date_entered], [ContactUs_updatedDate], [ContactUs_updatedBy], 
CASE [ContactUs_status] WHEN 'N' THEN 'New' WHEN 'I' THEN 'In Process' WHEN 'C' THEN 'Complete' END AS ContactUs_status_phrase, 
CASE [ContactUs_status] WHEN 'C' THEN 1 ELSE 0 END AS Status_Sort FROM [ContactUs] 
ORDER BY [Status_Sort] ASC, [ContactUs_date_entered] DESC";
GridView1.DataSourceID.Text = "SqlDataSource1"
GridView1.DataBind();
} 
else
{
SqlDataSource1.SelectCommand="SELECT [ContactUs_key], [ContactUs_intended_for], [ContactUs_status], [ContactUs_UID], [ContactUs_first_name], [ContactUs_last_name], [ContactUs_subject], [ContactUs_date_entered], [ContactUs_updatedDate], [ContactUs_updatedBy], 
CASE [ContactUs_status] WHEN 'N' THEN 'New' WHEN 'I' THEN 'In Process' WHEN 'C' THEN 'Complete' END AS ContactUs_status_phrase, 
CASE [ContactUs_status] WHEN 'C' THEN 1 ELSE 0 END AS Status_Sort FROM [ContactUs] 
WHERE [ContactUs_intended_for] ="+ SelectedValue.ToString()+"
ORDER BY [Status_Sort] ASC, [ContactUs_date_entered] DESC"
GridView1.DataSourceID.Text = "SqlDataSource1"
GridView1.DataBind();
} 

Open in new window


hope this helps.
0
 
LVL 5

Author Closing Comment

by:Bob Bender
ID: 40477078
Good follow-through, despite all the holidaze!
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Just a quick little trick I learned recently.  Now that I'm using jQuery with abandon in my asp.net applications, I have grown tired of the following syntax:      (CODE) I suppose it just offends my sense of decency to put inline VBScript on a…
Problem Hi all,    While many today have fast Internet connection, there are many still who do not, or are connecting through devices with a slower connect, so light web pages and fast load times are still popular.    If your ASP.NET page …
NetCrunch network monitor is a highly extensive platform for network monitoring and alert generation. In this video you'll see a live demo of NetCrunch with most notable features explained in a walk-through manner. You'll also get to know the philos…
Monitoring a network: how to monitor network services and why? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the philosophy behind service monitoring and why a handshake validation is critical in network monitoring. Software utilized …

718 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question