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


ASP.NET VB Hide certain rows/fields in a DataView

Posted on 2014-02-04
Medium Priority
Last Modified: 2014-02-22
Hi guys

Hopefully something simple again here. I'm looking to display some data in a DataView but I need to hide certain fields from the data source (namely the ID of the record).

Now my code currently looks like this:
<asp:SqlDataSource ID="AC_HistoricalRecord" runat="server" ConnectionString="<%$ ConnectionStrings:DefaultConnection %>" 
                                    SelectCommand="SELECT * FROM vw_OAS_HistoricalReports WHERE ([OAS Record ID] = @RecordID)">
                                    <asp:QueryStringParameter Name="RecordID" QueryStringField="RecordID" Type="String" />
                    <asp:DetailsView ID="HistoricalAftercare" AutoGenerateRows="false" DataSourceID="AC_HistoricalRecord" runat="server" CssClass="table table-striped table-bordered" ></asp:DetailsView>

Open in new window

No code behind at present but I believe this is how I'll need to perform this job? Hopefully someone can advise how I go about taking this code and make it work for me.

The column I wish to remove is [OAS Record ID]

I'm also looking for a way to hide rows that are essentially NULL. So only display the whole row if there is any data in the row. I have my SQL doing this in the background but the row is still appearing in my DataView so I'm guessing that I need to do something programmatically again to not display these rows if the data value is NULL?

Hope you can help.

Thanx again
Question by:Steven O'Neill
  • 5
  • 3
  • 2
LVL 83

Expert Comment

ID: 39832572
For first question, you have AutoGenerateRows="false" for your details view which means you have declared the columns manually. Is that correct? If yes then you can just remove the column for the ID so its not rendered. Otherwise, you have 2 options

1) Change "Select *..." in the datasource sql to "Select Col1, Col2, ..." and not include the ID column

2) Do not change the sql but define the columns on details view manually so you can hide the unwanted columns.

For the second question, what column do you want to check for null? You can just change your query to something similar to below

SELECT * FROM vw_OAS_HistoricalReports WHERE ([OAS Record ID] = @RecordID AND ColName Is Not Null)

Author Comment

by:Steven O'Neill
ID: 39833248
Hi there

Thanx for the response. Don't think I've described my issue correctly so I'll try again.

Yes you are correct about my declaring the AutoGenerateRows = "false" but this was me 'playing' with my code to stop the rows appearing automatically but that's where I hit an issue as I wasn't sure how to reference the rows manually as I can do with a GridView (using asp:BoundField or ItemTemplate, etc.). I wasn't sure how to manually reference these rows in a DataView.

I had thought about manually removing the column but the query hangs on the field that I don't wish displayed (it's used to produce the other rows but I don't need to see the specific row for [OAS Record ID] as it's irrelevant for the end user.

As for the second question I'll also try again:

At the time of running the SQL query I won't know if the row will be empty. So I need to pull all records back and check if the row has anything in it...if the field is NULL then I don't want the row (and it's label) shown in the result set but when I call the record and it does have data then I need to show this. Hence why I think the query has to be open like it is and then have the DataView built on the fly (new to this so could be well out with that one). Or I need to add each row manually with something that allows me to determine if the value is Empty or NULL and not display that row (data field and label).

Hope that's better but again if any further clarification is needed then please let me know.

LVL 23

Expert Comment

by:Christopher Kile
ID: 39833327
Why don't you simply exclude the rows where ID is null in your SQL query?  That is the first thing you try when you don't want a row to appear in the rowset.  I see nothing in your query showing any attempt to exclude empty rows or any other rows except rows where [OAS Record ID] is equal to your target ID.  

Your explanation of your first question makes no sense whatsoever, and I suspect you've gotten too deep into the code to explain it properly.  I have no idea what you mean when you say
...the query hangs on the field that I don't wish displayed (it's used to produce the other rows...
.  Try explaining what you're doing as if you're explaining to someone who has no idea what you're talking about at all, because we don't.  I'm a VB expert, but I'm not expert in how you got into this situation - tell me completely how you got here, don't skip anything.

Your explanation of your second question says you don't know if the row will be empty.  Why don't you check this in your query, where such a check belongs, instead of in your client code?  First, what fields of your row have to have a value in order for the row to be considered non-empty?
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!


Author Comment

by:Steven O'Neill
ID: 39833779
Hi again guys

Apologies if I'm not being too clear on this so I'll try once again.

Attached is my rather long SQL query but I'll take a snippet from this as an example:
SELECT        dbo.YBSCRM_BusinessRecords.BusinessName AS [Business Name], dbo.YBSCRM_BusinessRecords.MainContact AS [Main Contact], 
                         dbo.YBSCRM_BusinessRecords.PhoneMain AS [Main Phone], dbo.YBSCRM_BusinessRecords.PhoneMobile AS [Mobile Phone], 
                         dbo.YBSCRM_BusinessRecords.EMailAddress AS [E-Maill Address], dbo.YBSCRM_BusinessRecords.AddLine1 AS [Address Line 1], 
                         dbo.YBSCRM_BusinessRecords.AddLine2 AS [Address Line 2], dbo.YBSCRM_BusinessRecords.AddLine3 AS [Address Line 3], 
                         dbo.YBSCRM_BusinessRecords.AddCity AS City, dbo.YBSCRM_BusinessRecords.AddPostCode AS [Post Code], 
                         CASE WHEN dbo.YBSCRM_BusinessRecords.MoneyLoan > 0 THEN '£' + CONVERT(VARCHAR(10), dbo.YBSCRM_BusinessRecords.MoneyLoan) ELSE NULL 
                         END AS [Total Loan Amount], CASE WHEN dbo.YBSCRM_BusinessRecords.MoneyGrant > 0 THEN '£' + CONVERT(VARCHAR(10), 
                         dbo.YBSCRM_BusinessRecords.MoneyGrant) ELSE NULL END AS [Total Grant Amount], CONVERT(VARCHAR(10), dbo.YBSCRM_BusinessRecords.dteDateAdded, 
                         103) AS [Aftercare Start Date], dbo.YBSCRM_AftercareRecords.AftercareName AS [Aftercare Record Name], CONVERT(VARCHAR(10), 
                         dbo.YBSCRM_AftercareRecords.AftercareReportDate, 103) AS [Aftercare Report Date], 
                         CASE WHEN dbo.YBSCRM_AftercareRecords.ContactMade = 'True' THEN 'Yes' ELSE 'No' END AS [Contact Made?], 
                         CASE WHEN dbo.YBSCRM_AftercareRecords.ContactMade = 'True' THEN NULL ELSE dbo.Lookup_NoACReason.strNoACReason END AS [No Contact Made Because],
                          CONVERT(VARCHAR(10), dbo.YBSCRM_AftercareRecords.dteContactDate, 103) AS [Date of Contact], 
                         CASE WHEN dbo.YBSCRM_AftercareRecords.DetailsChanged = 'True' THEN 'Yes' ELSE 'No' END AS [Have Details Changed?], 
                         CASE WHEN dbo.YBSCRM_AftercareRecords.CeasedTrading = 'True' THEN 'Yes' ELSE NULL END AS [Ceased to Trade?], 
                         CASE WHEN dbo.YBSCRM_AftercareRecords.CeasedTrading = 'True' THEN dbo.YBSCRM_AftercareRecords.dteCeasedTradingDate ELSE NULL 
                         END AS [Date Ceased Trading], 
                         CASE WHEN dbo.YBSCRM_AftercareRecords.CeasedTrading = 'True' THEN dbo.Lookup_CeasedTradingReason.CeadedTradingStr ELSE NULL 
                         END AS [Ceased Trading Reason], dbo.YBSCRM_AftercareRecords.ContactSummary AS [Report Detail], dbo.Lookup_ContactType.ContactTypeStr AS [Contact Type], 
                         dbo.Lookup_Duration.DurationStr AS [Contact Duration], 
                         CASE WHEN dbo.YBSCRM_AftercareRecords.empFull_Time = 6 THEN '0' WHEN dbo.YBSCRM_AftercareRecords.empFull_Time = 5 THEN '5+' ELSE dbo.YBSCRM_AftercareRecords.empFull_Time
                          END AS [Employment - Full Time], 
                         CASE WHEN dbo.YBSCRM_AftercareRecords.empPart_Time = 6 THEN '0' WHEN dbo.YBSCRM_AftercareRecords.empPart_Time = 5 THEN '5+' ELSE dbo.YBSCRM_AftercareRecords.empPart_Time
                          END AS [Employment - Part Time], 
                         CASE WHEN dbo.YBSCRM_AftercareRecords.empCasual = 6 THEN '0' WHEN dbo.YBSCRM_AftercareRecords.empCasual = 5 THEN '5+' ELSE dbo.YBSCRM_AftercareRecords.empCasual
                          END AS [Employment - Casual], dbo.Lookup_AverageIncome.strAverageIncome AS [Average Monthly Income], 
                         dbo.Lookup_ProjectedGrowth.strProjectGrowth AS [Projected Growth in 3 Years], 
                         CASE WHEN dbo.YBSCRM_AftercareRecords.finAdaequateRecords = 'True' THEN 'Yes' ELSE 'No' END AS [Adequate Book-Keeping], 
                         dbo.Lookup_Bookkeeping.strBKType AS [Book-keeping Used], dbo.YBSCRM_AftercareRecords.finBook_KeepingSystemOther AS [If other, which one?], 
                         CASE WHEN dbo.YBSCRM_AftercareRecords.FollowUpNeeded = 'True' THEN 'Yes' ELSE 'No' END AS [Any Follow-Up Needed by PTYBS?], 
                         dbo.YBSCRM_AftercareRecords.FollowUpAction AS [If so, what action], dbo.YBSCRM_AftercareRecords.RecordID AS [OAS Record ID]
FROM            dbo.YBSCRM_AftercareRecords LEFT OUTER JOIN
                         dbo.Lookup_Bookkeeping ON dbo.YBSCRM_AftercareRecords.finBook_KeepingSys = dbo.Lookup_Bookkeeping.intBKID LEFT OUTER JOIN
                         dbo.Lookup_ProjectedGrowth ON dbo.YBSCRM_AftercareRecords.finProjectGrowth = dbo.Lookup_ProjectedGrowth.intProjectGrowth LEFT OUTER JOIN
                         dbo.Lookup_AverageIncome ON dbo.YBSCRM_AftercareRecords.finMonthlySales = dbo.Lookup_AverageIncome.intAverageIncome LEFT OUTER JOIN
                         dbo.Lookup_CeasedTradingReason ON 
                         dbo.YBSCRM_AftercareRecords.CeasedTradingReason = dbo.Lookup_CeasedTradingReason.CeasedTradingID LEFT OUTER JOIN
                         dbo.Lookup_NoACReason ON dbo.YBSCRM_AftercareRecords.NoContactReason = dbo.Lookup_NoACReason.intNoACReasonID LEFT OUTER JOIN
                         dbo.Lookup_EmploymentTotals ON dbo.YBSCRM_AftercareRecords.empFull_Time = dbo.Lookup_EmploymentTotals.intEmploymentID AND 
                         dbo.YBSCRM_AftercareRecords.empPart_Time = dbo.Lookup_EmploymentTotals.intEmploymentID AND 
                         dbo.YBSCRM_AftercareRecords.empCasual = dbo.Lookup_EmploymentTotals.intEmploymentID LEFT OUTER JOIN
                         dbo.YBSCRM_BusinessRecords ON dbo.YBSCRM_AftercareRecords.CRM_BusinessGUID = dbo.YBSCRM_BusinessRecords.CRM_BusinessGUID LEFT OUTER JOIN
                         dbo.Lookup_Duration ON dbo.YBSCRM_AftercareRecords.ContactDuration = dbo.Lookup_Duration.DurationID LEFT OUTER JOIN
                         dbo.Lookup_ContactType ON dbo.YBSCRM_AftercareRecords.TypeofContact = dbo.Lookup_ContactType.ContactTypeID

Open in new window

As an example lets take the following:

CASE WHEN dbo.YBSCRM_AftercareRecords.CeasedTrading = 'True' THEN 'Yes' ELSE NULL END AS [Ceased to Trade?]

The resultset comes out with this field as NULL but because the field is still being generated [Ceased to Trade] I assume the row in my DataView will always be generated even if the result of this field is NULL? My understanding would be that if the result of this field was NULL then the DataView wouldn't use this field but I'm guessing I'm wrong here.

This is how my site currently works:

User comes in and signs on;
They are shown a list of business/people associated with them;
If they click on a link (one of the businesses/people) then a secondary GridView is shown with historical transactions with that company/person;
If you click on a link in the secondary GridView, a DataView page is opened with the details of this transaction.

So there can be dozens of transactions on the secondary GridView and this merely passes the ID for that transaction to the query I mentioned earlier:

SELECT * FROM vw_OAS_HistoricalReports WHERE ([OAS Record ID] = @RecordID

and that's the query (well SQL View) that you see in the snippet above.

I've tried to change the contents of the field to NULL for each row that = NULL but the row is still shown in the DataView. My guess is there is a different way to handle the SQL to stop these rows from being populated in the first place but perhaps I'm going about this all the wrong way and hopefully (now I've put it all there and explained the simple way the site works) that someone can point me in the right direction here.

Thanx again
LVL 23

Expert Comment

by:Christopher Kile
ID: 39834111
Well, of course the row is showing in the DataView.  A row with Null values in some fields is still a row and will be displayed if it is included in the output set.  The only value that will be excluded are rows with [OAS Record ID] <> @RecordID.

Why do you want to exclude a row from your DataView if the record ID matches?  List every possible condition that applies.

If you don't want it to show in your DataView, did you want that row to show up in the GridView in the first place?

Author Comment

by:Steven O'Neill
ID: 39834163
Perhaps my misunderstanding on how the data is stored here but I had assumed that if a field was NULL then it wouldn't show in the DataView but I guess I'm viewing this literally (since the DataView displays in rows my natural assumption was it was held in rows within the dataset - which Is why I'm saying exclude the row - but again thinking logically it's all one row of data).

So perhaps my description is incorrect.

It wouldn't be the entire row that I would want to exclude but the field that is NULL.

So from my example above:

CASE WHEN dbo.YBSCRM_AftercareRecords.CeasedTrading = 'True' THEN 'Yes' ELSE NULL END AS [Ceased to Trade?]

If the result of this is NULL then I don't want the [Ceased to Trade] line to appear in my DataView results at all (including the label) but the label appears but there is no data in the field (as it's NULL obviously).

All I'm wanting to do is hide the display of any fields that are NULL in my DataView resultset.

Yes stupid here but new to this I'm afraid so just looking for pointers in some way as I'm not sure if I can do this similar to how I've done my GridView (whereby I merely brought in the fields I wanted on conditions but can't see how I do this in a DataView).

Thanx again
LVL 83

Expert Comment

ID: 39835332
A screenshot of the dataview while it is showing some records would help.

If you define the item template manually, you can add a condition on the visible property of the label so that it only shows if field value is not null.

Author Comment

by:Steven O'Neill
ID: 39835376
Hi there

Thanx for this. Yes I've figured this out now and have it displaying only label and values that I determine manually (so this has fixed my I don't wish to display the [OAS Record ID] field issue but now having a slight issue with programmatically hiding the labels/fields that are NULL.

Below is an example of the data being returned and displayed.

Example of a DetailsView
What I now need to do is to determine if certain fields are NULL and is so change the visibility to False.

So the code that I had used on each field that needs checking is:

Visible='<%# (Eval("No Contact Made Because") == DBNull.Value) ? false:true %>'

but I'm getting an error:

Databinding expressions are only supported on objects that have a DataBinding event. System.Web.UI.WebControls.TemplateField does not have a DataBinding event
LVL 83

Accepted Solution

CodeCruiser earned 2000 total points
ID: 39835691
Another option is to use ItemCreated event. Check the example at bottom of this page


Author Closing Comment

by:Steven O'Neill
ID: 39879132
Pointed me in the direction needed. Thanx

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

Today is the age of broadband.  More and more people are going this route determined to experience the web and it’s multitude of services as quickly and painlessly as possible. Coupled with the move to broadband, people are experiencing the web via …
IntroductionWhile developing web applications, a single page might contain many regions and each region might contain many number of controls with the capability to perform  postback. Many times you might need to perform some action on an ASP.NET po…
Exchange organizations may use the Journaling Agent of the Transport Service to archive messages going through Exchange. However, if the Transport Service is integrated with some email content management application (such as an anti-spam), the admin…
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …
Suggested Courses

571 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