Solved

Hide DetailsView Row if Null

Posted on 2010-11-18
3
1,499 Views
Last Modified: 2012-05-10
I have a DetailsView control based off a sqldatasource with the following SELECT statement:

SELECT id
        , CASE title WHEN titlenew THEN NULL
            ELSE 'Title changed from ' + title + ' to ' + titlenew END AS title
        , CASE firstname WHEN firstnamenew THEN NULL
            ELSE 'First name changed from ' + firstname + ' to ' + firstnamenew END AS firstname
        , CASE middlename WHEN middlenamenew THEN NULL
            ELSE 'Middle name changed from ' + middlename + ' to ' + middlenamenew END AS middlename
FROM            tblAudit
WHERE        (varid = @varid)

varid is passed form a Textbox control. The query compares two fields of a given record within the table. If the fields are equal it returns NULL, if the fields are not, it returns the string showing the values from the 2 fields. An example result set might look like:

title                                                    firstname                                                          middlename
NULL                                                'First name changed from John to Roger'         NULL
'Title changed from Man to Woman  NULL                                                                 'Middle name changed from M to W'


I would like for my DetailsView (with Paging enabled) to only display the Rows which are not null. ie for the Result set above, Page 1:

First Name: First name changed from John to Roger

Page 2:

Title: Title changed from Man to Woman
Middle name: Middle name changed from M to W


I've tried multiple methods of evaluating the data of the Row and hiding it if NULL but the rows remain... ie

        protected void DetailsView1_DataBound(object sender, EventArgs e)
        {
            foreach (DetailsViewRow Row in DetailsView1.Rows)
            {
                if ((string.IsNullOrEmpty(Row.Cells[1].Text)))
                {
                    Row.Visible = false;
                }
            }
        }

Annoying! Any ideas? Please ask for clarification
0
Comment
Question by:alright
[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
3 Comments
 

Expert Comment

by:elmbrook
ID: 34167602
Have you tried removing the null values from the SELECT statement?
0
 

Author Comment

by:alright
ID: 34167638
Within the tblAudit there can be multiple records with the same varid. It's my understanding that the results from a SELECT statement must always have the same amount of return columns, ie using the above example, I can't get a result set that only SELECTS column firstname from record 1 but columns title & middlename from record 2, can I?
0
 

Accepted Solution

by:
elmbrook earned 125 total points
ID: 34168763
I guess the other way to do it is to put the information into a datatable, copy it to a dataview and then manipulate the dataview to remove the records you don't need.

This code is untested though but you could try it.

//Copy first query to Datatable
Datatable dt ="SELECT id
        , CASE title WHEN titlenew THEN NULL
            ELSE 'Title changed from ' + title + ' to ' + titlenew END AS title
        , CASE firstname WHEN firstnamenew THEN NULL
            ELSE 'First name changed from ' + firstname + ' to ' + firstnamenew END AS firstname
        , CASE middlename WHEN middlenamenew THEN NULL
            ELSE 'Middle name changed from ' + middlename + ' to ' + middlenamenew END AS middlename
FROM            tblAudit
WHERE        (varid = @varid)"

// Copy to Dataview
DataView dvResult = new DataView(dt);

// Process Dataview
            int _datadatablelength = dvResult.Rows.Count;

            for (int i = _datadatablelength - 1; i > -1; i--)
            {
                foreach (DataRow _column in dvResult.Rows)
                {                  
                    if (_row[1].ToString().Trim() == null) // Row you do not want to display based on your fields
                            {
                                _row.Delete();
                            }
                    dvResult.AcceptChanges();
                }              
            }
0

Featured Post

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

Question has a verified solution.

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

Calculating holidays and working days is a function that is often needed yet it is not one found within the Framework. This article presents one approach to building a working-day calculator for use in .NET.
Real-time is more about the business, not the technology. In day-to-day life, to make real-time decisions like buying or investing, business needs the latest information(e.g. Gold Rate/Stock Rate). Unlike traditional days, you need not wait for a fe…
Michael from AdRem Software explains how to view the most utilized and worst performing nodes in your network, by accessing the Top Charts view in NetCrunch network monitor (https://www.adremsoft.com/). Top Charts is a view in which you can set seve…
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 …

688 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