Hide <TR> tag based on Recordset NULL value

Question

I am using a Microsoft SQL Server 2005 stored procedure to post records in an HTML table. In the HTML table, I have rows for the following fields: entry #, open date, description, and owner.

Sometimes, the owner field in the db table will be NULL. When this happens, I have ASP response.write "N/A" in the HTML table row corresponding to the owner. However, I'd like to avoid this, as it seems superfluous. Instead, I'm hoping there is a way to just eliminate that table row altoegether if the owner field is NULL in the db. How would I go about doing this? I'm using Javascript, classic ASP, and SQL Server 2005. My code is below. Note - I am a total newbie at all of this. Thanks.

'Declare Variables
Dim CN, RS, vOutputType, vSQL, vNumber, vOwner

'Connection from includes file
Set CN = GetDataConnection

vOutputType = Request.QueryString("ot")

If Request.QueryString("txtNumber") <> "" Then
vNumber = Rtrim(Request.QueryString("txtNumber"))
End If

If Request.QueryString("cboOwner") <> "" Then
    vOwner = Rtrim(Request.QueryString("cboOwner"))
End If

If vNumber <> "" Or vOwner <> "" Then

vSQL = "spReport "
vSQL = vSQL & "@vNumber = '" & vNumber & "', "
vSQL = vSQL & "@vOwner = '" & vOwner & "'"

Set RS = CN.Execute(vSQL)

If IsObject(RS) Then
 If Not RS.EOF Then%> 
  <table class="WebApps">
   <tr>
    <td width="5%"><h3>Entry #</h3></td>
    <td width="5%"><h3>Open Date</h3></td>
    <td width="5%"><h3>Description</h3></td>
    <td width="5%"><h3>Owner</h3></td>  
   </tr>

   <%RS.MoveFirst
   Do While Not RS.EOF
    %>    
    <tr>
     <td><p><%= RS("ID")%></p></td>
     <td><p><%= RS("OpenDate")%></p></td>
     <td><p><%= RS("Description")%></p></td> 
     <td><p><%If (RS("OwnerName")) <> "" Then Response.Write(RS("OwnerName")) Else Response.Write("N/A")%></p></td>
    </tr>
    <%RS.MoveNext
   Loop%>   
  </table> 
<%End If
End If

'Close objects
Set RS = NOTHING
CN.Close
Set CN = Nothing

Answer 1

It's best to filter out rows containing NULL values in the SQL query itself, but if you can't for some reason, just move your IF statement up a bit so if the condition isn't met, no table row is written out in the first place:

<%If (RS("OwnerName"))<>"" Then Response.Write( "<tr><td>" & "</td></tr>" ) %>

Answer 2

Filtering the source is of course better, by simply selecting records where OwnerName is not ""

Otherwise you can use your bit of code and modify it slightly

from:

<%RS.MoveFirst
Do While Not RS.EOF
        %>                              
        <tr>
                <td><p><%= RS("ID")%></p></td>
                <td><p><%= RS("OpenDate")%></p></td>
                <td><p><%= RS("Description")%></p></td> 
                <td><p><%If (RS("OwnerName")) <> "" Then Response.Write(RS("OwnerName")) Else Response.Write("N/A")%></p></td>
        </tr>
        <%RS.MoveNext
Loop%>

to

<%RS.MoveFirst
Do While Not RS.EOF
    If (RS("OwnerName") <> "") Then
        %>                              
        <tr>
                <td><p><%= RS("ID")%></p></td>
                <td><p><%= RS("OpenDate")%></p></td>
                <td><p><%= RS("Description")%></p></td> 
                <td><p><%= RS("OwnerName")%></p></td>
        </tr>
        <%
     End If   
     RS.MoveNext
Loop%>

Haven't tested the code but it should help out.

Answer 3

You can do it in 2 ways.

1) Frontend: Check for NULL values in Owner field as following.

If Not (IsNull(rsNew("Owner")) Then
      <table class="WebApps">
      ...........
End If

2) Backend:

Filter NULL values in your stored procedure using WHERE clause.

Answer 4

Since you're a "total newbie" here are some othe pieces of advice.

SQL Injection Attack

Your code:-

vSQL = "spReport "
vSQL = vSQL & "@vNumber = '" & vNumber & "', "
vSQL = vSQL & "@vOwner = '" & vOwner & "'"

Set RS = CN.Execute(vSQL)

DO NOT do this. Someone can create a query string to execute arbitary SQL on your server:-

?txtNumber=30&cboOwner='; arbitary SQL code here ; --

You should always use an ADODB.Command object to execute SQL that requires parameter value retrieved from the client. Web Search "SQL Injection ASP"

The meaning of NULL

You seem a little confused about the meaning of NULL. "the owner field in the db table will be NULL" yet your code is testing for <> "". Null is not the same as empty string. A field that is actually NULL will not be equal to "".

Use Server.HTMLEncode

Your code:-

<td><p><%= RS("Description")%></p></td>

What happens if the Description field contains a < or & character? Worse yet if the data in Description field orignates from data entry on web clients it could contain Javascript injection attempts. Always use Server.HTMLEncode on string data being sent to the client:-

<td><p><%= Server.HTMLEncode(RS("Description"))%></p></td>

Your actual question

Don't be tempted to use VBScript in your ASP page to do the job that SQL should be doing. Modifiy your SQL with a WHERE clause:-

 WHERE Owner IS NOT NULL

or if you're not sure whether Owner will NULL or an empty string:-

WHERE Owner IS NOT NULL AND Owner <> ""