Hide DetailsView Row if Null

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
alrightAsked:
Who is Participating?
 
elmbrookConnect With a Mentor Commented:
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
 
elmbrookCommented:
Have you tried removing the null values from the SELECT statement?
0
 
alrightAuthor Commented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.