Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 303
  • Last Modified:

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

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
Bob Bender
Asked:
Bob Bender
  • 2
  • 2
1 Solution
 
Jitendra PatilSr.Software EngineerCommented:
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
 
Bob BenderAuthor Commented:
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
 
Jitendra PatilSr.Software EngineerCommented:
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
 
Bob BenderAuthor Commented:
Good follow-through, despite all the holidaze!
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now