?
Solved

Hide DetailsView Row if Null

Posted on 2010-11-18
3
Medium Priority
?
1,510 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
  • 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 500 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

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

In this article I will describe the Detach & Attach method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Is your data getting by on basic protection measures? In today’s climate of debilitating malware and ransomware—like WannaCry—that may not be enough. You need to establish more than basics, like a recovery plan that protects both data and endpoints.…
As many of you are aware about Scanpst.exe utility which is owned by Microsoft itself to repair inaccessible or damaged PST files, but the question is do you really think Scanpst.exe is capable to repair all sorts of PST related corruption issues?

850 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