471,066 Members | 953 Online
Bytes | Software Development & Data Engineering Community
Post +

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 471,066 software developers and data experts.

How to hide fields/columns which are null

Hello. I am very bad at ASP and use ASPRunner Pro to write ASP pages for me. I have come across a problem however which I cannot figure out. Quite simply on a list page I have six fields, [G1 Title],[G2Title],[G3Title],[G4Title],[G5Title],[G6Title]. Depending on who has logged in however, not all of these fields will contain a value. Each of these has its own column and is only displayed once via a SELECT DISTINCT statement. In other words [G1 Title] for example is either populated or NULL, it can not be both. All that I want to do is get the ASP page not to show an empty column for [G1 Title] if it is null. I only want the columns for [G1 Title] through to [G6 Title] to display if their respective values are not NULL. Could anybody help me with this? I would be extremely grateful.
Jun 20 '10 #1
9 3688
3,406 Expert 2GB
show the code that (1)queries the db and (2)builds the table.

Jun 22 '10 #2
Hi Jared

As I have said I am very bad at ASP so I am not quite sure even what you mean.

The code for the ASP Page is as follows...

Expand|Select|Wrap|Line Numbers
  1. <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
  2. <HTML {$html_attrs}><HEAD><TITLE>Test</TITLE>
  3. <link REL="stylesheet" href="include/style.css" type="text/css"><!--[if IE]><link REL="stylesheet" href="include/styleIE.css" type="text/css"><![endif]-->{BEGIN style_block}
  4. <STYLE>
  5. #height100{$id} {padding:10px;}
  6. #toplinks_caption{$id} {padding:0px 30px;font-size:12px;font-weight:bold;}
  7. #mastertable_block{$id} {margin-left:50px;margin-top:10px}
  8. #toplinks_block{$id} {width:100%;padding:3px;padding-left:30px}
  9. #toplinks_block{$id} span {padding-left:5px;}
  10. #search_records_block{$id} { padding-left:20px;}
  11. #search_records_block{$id} > * {vertical-align:middle;}
  12. #search_records_block{$id} select {margin:3px 0px;}
  13. #login_block{$id} {padding:10px; margin-bottom:3px}
  14. #login_block{$id} * {margin-left:5px; padding:5px 0px;}
  15. #menu_block{$id} {padding:10px;}
  16. #menu_block{$id} * {list-style-type:none;}
  17. #menu_block{$id} IMG {vertical-align:middle;}
  18. #records_block{$id} {margin-left:203px;}
  19. #record_controls{$id} {padding:10px 10px;}
  20. #record_controls{$id} span {padding-left:10px;}
  21. #recordcontrolsadd_block{$id} {padding:10px 10px;border: solid; border-width: 1px; border-color: #C0C0C0;}
  22. #recordcontrolsadd_block{$id} span {padding-left:10px;}
  23. #grid_block{$id} {border:solid 1px #C0C0C0;}
  24. .grid_recordheader{$id} {padding:0 5px 5px;vertical-align:top;}
  25. .grid_recordheader{$id} * {margin:0px 5px; vertical-align:top;}
  26. body {margin:0px;padding:0px;}
  27. </STYLE>
  28. {END style_block}
  29. <META name=GENERATOR content="MSHTML 8.00.7600.16588"></HEAD>
  30. <BODY {$bodyattrs} marginwidth="0" marginheight="0">{BEGIN body}
  31. <DIV id="height100{$id}">{$header} 
  32. {BEGIN toplinks_block}<DIV id="toplinks_block{$id}">&nbsp;<FONT 
  33. size=+0><B>Test</B></FONT> <SPAN id="login_block{$id}">Logged on 
  34. as&nbsp;<B>{$username}</B>&nbsp; <A 
  35. href="login.asp?a=logout">Log out</A> </SPAN>{BEGIN asearch_link}<SPAN><A 
  36. href="Test_search.asp">Advanced search</A></SPAN>{END asearch_link} </DIV>{END toplinks_block}
  37. <DIV id="recordcontrolsadd_block{$id}" class=shade>{BEGIN search_records_block}<SPAN id="search_records_block{$id}" class=shade>{BEGIN searchform}<SPAN id="searchform{$id}" class=shade><B>Search for: </B>&nbsp;&nbsp;&nbsp; {BEGIN searchform_field}<SELECT id="ctlSearchField{$id}"> <option value="">Any field</OPTION> <option value="ID" {$ID_searchfieldoption}>ID</OPTION> <option value="G1 Title" {$G1_Title_searchfieldoption}>G1 Title</OPTION> <option value="G2 Title" {$G2_Title_searchfieldoption}>G2 Title</OPTION> 
  38.   <option value="G3 Title" {$G3_Title_searchfieldoption}>G3 Title</OPTION> <option value="G4 Title" {$G4_Title_searchfieldoption}>G4 Title</OPTION> <option value="G5 Title" {$G5_Title_searchfieldoption}>G5 Title</OPTION> <option value="G6 Title" {$G6_Title_searchfieldoption}>G6 Title</OPTION></SELECT>{END searchform_field} &nbsp; {BEGIN searchform_option}<SELECT id="ctlSearchOption{$id}"> <option value="Contains" {$Contains_searchtypeoption}>Contains</OPTION> <option value="Equals" {$Equals_searchtypeoption}>Equals</OPTION> <option value="Starts with ..." {$Starts_with_____searchtypeoption}>Starts with ...</OPTION> <option value="More than ..." {$More_than_____searchtypeoption}>More than ...</OPTION> <option value="Less than ..." {$Less_than_____searchtypeoption}>Less than ...</OPTION> 
  39.   <option value="Equal or more than ..." {$Equal_or_more_than_____searchtypeoption}>Equal or more than ...</OPTION> 
  40.   <option value="Equal or less than ..." {$Equal_or_less_than_____searchtypeoption}>Equal or less than ...</OPTION> 
  41.   <option value="Empty" {$Empty_searchtypeoption}>Empty</OPTION></SELECT>{END searchform_option} &nbsp; {BEGIN searchform_text}<INPUT {$searchfor_attrs}>{END searchform_text} &nbsp; {BEGIN searchform_search}<INPUT class=button value=Search type=button {$searchbutton_attrs}>{END searchform_search} 
  42. &nbsp; {BEGIN searchform_showall}<INPUT class=button value="Show all" type=button {$showallbutton_attrs}>{END searchform_showall} 
  43. </SPAN>{END searchform}{BEGIN details_block}<SPAN>Details found: <B>{$records_found}</B>&nbsp;&nbsp;&nbsp;&nbsp;</SPAN>{END details_block} 
  44. {BEGIN pages_block}<SPAN>Page <B>{$page}</B> of <B>{$maxpages}</B></SPAN>{END pages_block} 
  45. &nbsp;&nbsp;&nbsp;&nbsp; {BEGIN recordspp_block}<SPAN id="recordspp_block{$id}">Records Per Page: <SELECT {$recordspp_attrs}> 
  46.   <option value="10" {$rpp10_selected}>10</OPTION> <option value="20" {$rpp20_selected}>20</OPTION> <option value="30" {$rpp30_selected}>30</OPTION> <option value="50" {$rpp50_selected}>50</OPTION> <option value="100" {$rpp100_selected}>100</OPTION> <option value="500" {$rpp500_selected}>500</OPTION></SELECT> </SPAN>{END recordspp_block}</SPAN>{END search_records_block}</DIV>
  47. {BEGIN record_controls}<DIV id="record_controls{$id}"></DIV>{END record_controls}
  48. <DIV id="usermessage{$id}" class=message></DIV>
  49. {BEGIN grid_block}<TABLE style="WIDTH: 100%" id="grid_block{$id}" border=0 cellSpacing=2 cellPadding=3 name="maintable">
  50.   <TBODY>
  51.   {BEGIN grid_header}<TR vAlign=top {$gridHeader_attrs}>{BEGIN record_header}
  52.     {BEGIN ID_fieldheadercolumn}<TD class=blackshade>{BEGIN ID_fieldheader}<A class=blackshade 
  53.       {$ID_orderlinkattrs}>ID</A> {END ID_fieldheader}</TD>{END ID_fieldheadercolumn}
  54.     {BEGIN G1_Title_fieldheadercolumn}<TD class=blackshade>{BEGIN G1_Title_fieldheader}<A class=blackshade 
  55.       {$G1_Title_orderlinkattrs}>G1 Title</A> {END G1_Title_fieldheader}</TD>{END G1_Title_fieldheadercolumn}
  56.     {BEGIN G2_Title_fieldheadercolumn}<TD class=blackshade>{BEGIN G2_Title_fieldheader}<A class=blackshade 
  57.       {$G2_Title_orderlinkattrs}>G2 Title</A> {END G2_Title_fieldheader}</TD>{END G2_Title_fieldheadercolumn}
  58.     {BEGIN G3_Title_fieldheadercolumn}<TD class=blackshade>{BEGIN G3_Title_fieldheader}<A class=blackshade 
  59.       {$G3_Title_orderlinkattrs}>G3 Title</A> {END G3_Title_fieldheader}</TD>{END G3_Title_fieldheadercolumn}
  60.     {BEGIN G4_Title_fieldheadercolumn}<TD class=blackshade>{BEGIN G4_Title_fieldheader}<A class=blackshade 
  61.       {$G4_Title_orderlinkattrs}>G4 Title</A> {END G4_Title_fieldheader}</TD>{END G4_Title_fieldheadercolumn}
  62.     {BEGIN G5_Title_fieldheadercolumn}<TD class=blackshade>{BEGIN G5_Title_fieldheader}<A class=blackshade 
  63.       {$G5_Title_orderlinkattrs}>G5 Title</A> {END G5_Title_fieldheader}</TD>{END G5_Title_fieldheadercolumn}
  64.     {BEGIN G6_Title_fieldheadercolumn}<TD class=blackshade>{BEGIN G6_Title_fieldheader}<A class=blackshade 
  65.       {$G6_Title_orderlinkattrs}>G6 Title</A> {END G6_Title_fieldheader}</TD>{END G6_Title_fieldheadercolumn}{END record_header}</TR>{END grid_header}
  66.   {BEGIN grid_row}<TR vAlign=top {$rowattrs} {$rowstyle}>{BEGIN grid_record}
  67.     {BEGIN ID_fieldcolumn}<TD vAlign=middle align=left {$ID_style}>{$ID_value} </TD>{END ID_fieldcolumn}
  68.     {BEGIN G1_Title_fieldcolumn}<TD vAlign=middle align=left {$G1_Title_style}>{$G1_Title_value} </TD>{END G1_Title_fieldcolumn}
  69.     {BEGIN G2_Title_fieldcolumn}<TD vAlign=middle align=left {$G2_Title_style}>{$G2_Title_value} </TD>{END G2_Title_fieldcolumn}
  70.     {BEGIN G3_Title_fieldcolumn}<TD vAlign=middle align=left {$G3_Title_style}>{$G3_Title_value} </TD>{END G3_Title_fieldcolumn}
  71.     {BEGIN G4_Title_fieldcolumn}<TD vAlign=middle align=left {$G4_Title_style}>{$G4_Title_value} </TD>{END G4_Title_fieldcolumn}
  72.     {BEGIN G5_Title_fieldcolumn}<TD vAlign=middle align=left {$G5_Title_style}>{$G5_Title_value} </TD>{END G5_Title_fieldcolumn}
  73.     {BEGIN G6_Title_fieldcolumn}<TD vAlign=middle align=left {$G6_Title_style}>{$G6_Title_value} </TD>{END G6_Title_fieldcolumn}{END grid_record}</TR>{END grid_row}</TBODY></TABLE>{END grid_block}
  74. {BEGIN pagination_block}<DIV class=toplist align=center>{$pagination}</DIV>{END pagination_block}
  75. {BEGIN message_block}<DIV class=toplist align=center valign="middle"><B>{$message}</B></DIV>{END message_block}{$footer} </DIV>{END body}</BODY></HTML>

The query is just SELECT * FROM Test but I'm not sure if that's what you mean by the code that queries the db.

Many Thanks

Jun 22 '10 #3
Hi Jared
I just wondered if you had had any thoughts on this? It is giving me a real headache.
Jun 23 '10 #4
Why don't you try using isnull function.

strValue = ""
if not isnull(rst.field("Column1")) then strValue = rst.field("Column1")
Jun 24 '10 #5
3,406 Expert 2GB
I don't recognize the code at all, it doesn't look like asp to me. What development environment are you using?

Jun 29 '10 #6
Hi Jared
It is ASPRunner Pro. So far I have come up with...

if IsNull(("G2")) or IsEmpty ("G2") or ("G2")="" or len("G2")>0 then
xt.assign "G2_fieldheadercolumn",false
xt.assign "G2_fieldcolumn",false
end if

but this just gets rid of the G2 header and its column whether the value for G2 is null or not. It is as if none of the above if conditions actually mean a NULL value in the database.

Jun 29 '10 #7
This is classic ASP.
isnull is a function (VBscript and Visual Basic the same) that verifies a variable/field for a NULL
Jun 29 '10 #8
Yes but how do I use it?
Jun 29 '10 #9
3,406 Expert 2GB
no, I recognize isnull, that's fine, what I don't recognize is
Expand|Select|Wrap|Line Numbers
  1. #    {BEGIN G1_Title_fieldheadercolumn}<TD class=blackshade>{BEGIN G1_Title_fieldheader}<A class=blackshade 
  2. #       {$G1_Title_orderlinkattrs}>G1 Title</A> {END G1_Title_fieldheader}</TD>{END G1_Title_fieldheadercolumn}
This does not look like ASP classic, or ASP.NET. I guess it could just be place-holders for a wysiwyg editor, but this doesn't show how you are connecting to the db.

In asp this code usually looks something like this:
Expand|Select|Wrap|Line Numbers
  1. dim objConn, objRS, strQuery
  2. set objConn = server.createobject("adodb.connection")
  3. objConn.open "DSN=myDSN;UID=myUser;pwd=myPwd"
  4. set objRS = server.createobject("adodb.recordset")
  5. strQuery = "SELECT * FROM myTable"
  6. objRS.open strQuery, objConn
This is the code I'm looking for that queries the db. The code that builds the table is going to look something like this:
Expand|Select|Wrap|Line Numbers
  1. <table><tr>
  2. <%
  3. for each x in objRS.fields
  4.    response.write "<th>" & x & "</th>" & vbNewLine
  5. next %>
  6. </tr>
  7. <%
  8. do until objRS.eof
  9.    response.write "<tr>"
  10.    for each x in objRS.fields
  11.       response.write "<td>" & objRS(x) & "</td>" & vbNewLine
  12.    next
  13.    response.write "</tr>"
  14.    objRS.moveNext
  15. loop %>
  16. </table>
Jun 30 '10 #10

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

2 posts views Thread by Aad Aldus | last post: by
10 posts views Thread by DettCom | last post: by
5 posts views Thread by Shane Story | last post: by
2 posts views Thread by jeet_sen | last post: by

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.