Solved

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

Posted on 2014-11-20
4
278 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
  • 2
  • 2
4 Comments
 
LVL 12

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 12

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

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Introduction This article shows how to use the open source plupload control to upload multiple images. The images are resized on the client side before uploading and the upload is done in chunks. Background I had to provide a way for user…
Today I had a very interesting conundrum that had to get solved quickly. Needless to say, it wasn't resolved quickly because when we needed it we were very rushed, but as soon as the conference call was over and I took a step back I saw the correct …
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

733 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