Solved

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

Posted on 2014-02-04
10
398 Views
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)">
                                <SelectParameters>
                                    <asp:QueryStringParameter Name="RecordID" QueryStringField="RecordID" Type="String" />
                                </SelectParameters>
                            </asp:SqlDataSource>
                    <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
0
Comment
Question by:Steven O'Neill
  • 5
  • 3
  • 2
10 Comments
 
LVL 83

Expert Comment

by:CodeCruiser
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)
0
 
LVL 2

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.

Thanx
0
 
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?
0
 
LVL 2

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
0
 
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?
0
Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 2

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
0
 
LVL 83

Expert Comment

by:CodeCruiser
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.
0
 
LVL 2

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
0
 
LVL 83

Accepted Solution

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

http://msdn.microsoft.com/en-us/library/system.web.ui.webcontrols.detailsview.itemcreated(v=vs.110).aspx
0
 
LVL 2

Author Closing Comment

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

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

In an ASP.NET application, I faced some technical problems. In this article, I list them out and show the solutions that I found.  I hope it will be useful. Problem: After closing a pop-up window, the parent page should be refreshed automaticall…
The ECB site provides FX rates for major currencies since its inception in 1999 in the form of an XML feed. The files have the following format (reducted for brevity) (CODE) There are three files available HERE (http://www.ecb.europa.eu/stats/exch…
This video discusses moving either the default database or any database to a new volume.
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…

708 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

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now