Solved

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

Posted on 2014-11-20
4
276 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

3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

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…
Performance in games development is paramount: every microsecond counts to be able to do everything in less than 33ms (aiming at 16ms). C# foreach statement is one of the worst performance killers, and here I explain why.
This video shows how to quickly and easily add an email signature for all users on Exchange 2016. The resulting signature is applied on a server level by Exchange Online. The email signature template has been downloaded from: www.mail-signatures…
This video shows how to use Hyena, from SystemTools Software, to bulk import 100 user accounts from an external text file. View in 1080p for best video quality.

778 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