?
Solved

Hide DetailsView Row if Null

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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
In this article I will describe the Copy Database Wizard 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.
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…
How to fix incompatible JVM issue while installing Eclipse While installing Eclipse in windows, got one error like above and unable to proceed with the installation. This video describes how to successfully install Eclipse. How to solve incompa…

765 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