How to ASP variables in if statement when the value is based from SQL

Question

When I want to display a variable I use "<%=con("notes")%>" but how do I use that in an if statement? For example:

<% if con("notes") = "" then
  response.write("")
  else
  response.write("<tr><td colspan='8'><strong>Notes:</strong> ") 
  response.write(con("notes"))
  response.write("</td></tr>")
  end if
  %>

Doesn't seem to work. It doesn't cause any errors but I know a lot of the notes are empty therefore response.write("") should be used a lot in the code.

Anyone got any ideas?

Whole code below:

<%@CODEPAGE=65001%> 
<%
pagetitle="Aircraft and Simulator Multi-use List 06/074"
%>
<!--#INCLUDE VIRTUAL="/_lib/include/header.htm"-->
<!--#INCLUDE VIRTUAL="/_lib/include/menu.htm"-->
<script>
$(document).ready(function() {
    $("#state").change(function () {
    this.form.submit();
})
$('#supplier_name, #aircraft_type').focus()
$("#supplier_name").val($("#supplier_name").val());
$("#aircraft_type").val($("#aircraft_type").val());
var typingTimer;                
var doneTypingInterval = 800;  

$('#supplier_name, #aircraft_type').keyup(function(){
    clearTimeout(typingTimer);
    if ($('#supplier_name, #aircraft_type').val) {
        typingTimer = setTimeout(doneTyping, doneTypingInterval);
    }
});

function doneTyping () {
    $("form").submit();
}

});
</script>
<div class="twoColumnRow">
<div class="twoColumnContent">
<p class="breadCrm"><a href="/index.htm">Home</a> <span>&gt;</span> <a href="/tools/index.htm">Tools of the Trade</a> <span>&gt;</span> <a href="/tools/finance/index.htm">Finance and Purchasing</a><span>&gt;</span> <a href="/tools/finance/procurement/index.htm">Procurement and Contracts information</a></p>
<div class="contentPad">
<!-- Start of main content -->

<p class="imageRight">&nbsp;</p>
<h1><%=pagetitle%></h1>

<%
        Dim connectString, connect, conDB, sconDB, lDB, con, scon, lcon, src_ccn, src_state
        connectString = "Driver={Microsoft Text Driver (*.txt; *.csv)}; DBQ=" & Server.MapPath("data")
              src_supplier_name = Request.QueryString("supplier_name")
              src_aircraft_type = Request.QueryString("aircraft_type")
              src_state = Request.QueryString("state")
        set connect = Server.CreateObject("ADODB.connection")
        connect.open connectString

        if src_state = "" then
            conDB = "SELECT * FROM mul.csv WHERE ucase(supplier_name) LIKE ucase('%"+src_supplier_name+"%') AND ucase(aircraft_type) LIKE ucase('%"+src_aircraft_type+"%')"   
            lDB = "SELECT * FROM mul.csv WHERE ucase(supplier_name) LIKE ucase('%"+src_supplier_name+"%')" 
        elseif src_state = "any" then
            conDB = "SELECT * FROM mul.csv WHERE ucase(supplier_name) LIKE ucase('%"+src_supplier_name+"%') AND ucase(aircraft_type) LIKE ucase('%"+src_aircraft_type+"%')"   
            lDB = "SELECT * FROM mul.csv WHERE ucase(supplier_name) LIKE ucase('%"+src_supplier_name+"%') AND ucase(aircraft_type) LIKE ucase('%"+src_aircraft_type+"%')" 
        else 
            conDB = " SELECT * FROM mul.csv WHERE ucase(state) LIKE ucase('%"+src_state+"%') AND ucase(supplier_name) LIKE ucase('%"+src_supplier_name+"%') AND ucase(aircraft_type) LIKE ucase('%"+src_aircraft_type+"%')"
            lDB = " SELECT * FROM mul.csv WHERE ucase(state) LIKE ucase('%"+src_state+"%') AND ucase(supplier_name) LIKE ucase('%"+src_supplier_name+"%') AND ucase(aircraft_type) LIKE ucase('%"+src_aircraft_type+"%')"
        end if

        sconDB = "SELECT * FROM mul.csv"    

        set con = connect.execute(conDB)
        set scon = connect.execute(sconDB)
        set lcon = connect.execute(lDB)
%>
<h2>Search results</h2>
<% if not con.BOF then %>
<div style="margin-top:1em">

  <form id="searchf" action="search.htm" method="get">

      <p>Supplier name: <input id="supplier_name" name="supplier_name" type="text" value="<%=src_supplier_name%>"> Aircraft type: <input id="aircraft_type" name="aircraft_type" type="text" value="<%=src_aircraft_type%>"> </p>
     <p>State or Territory:<br/>
      <select style="width: 497px;" id="state" name="state">
        <option class="group" label="Any state or territory" value="any" <%if request.querystring("state") = "any" then response.write("selected") %>></option>
        <option class="group" label="Australian Capital Territory" value="ACT" <%if request.querystring("state") = "ACT" then response.write("selected") %>></option>
        <option class="group" label="New South Whales" value="NSW" <%if request.querystring("state") = "NSW" then response.write("selected") %>></option>
        <option class="group" label="Northern Territory" value="NT" <%if request.querystring("state") = "NT" then response.write("selected") %>></option>
        <option class="group" label="Queensland" value="QLD" <%if request.querystring("state") = "QLD" then response.write("selected") %>></option>
        <option class="group" label="South Australia" value="SA" <%if request.querystring("state") = "SA" then response.write("selected") %>></option>
        <option class="group" label="Tasmania" value="TAS" <%if request.querystring("state") = "TAS" then response.write("selected") %>></option>
        <option class="group" label="Victoria" value="VIC" <%if request.querystring("state") = "VIC" then response.write("selected") %>></option>
        <option class="group" label="Western Australia" value="WA" <%if request.querystring("state") = "WA" then response.write("selected") %>></option>
        <option class="group" label="Other" value="other" <%if request.querystring("state") = "other" then response.write("selected") %>></option>
      </select>

    <input type="submit" value="Search" /></p>
  </form>
  <br />

<table style="font-size:.9em;" class="contentTable">
  <tr>
    <th width="25%">Supplier</th>
    <th width="5%">State</th>
    <th width="20%">Aircraft type</th>
    <th width="10%">Variant</th>
    <th width="10%">Hourly cost</th>
    <th width="10%">Rental basis</th>
    <th width="10%">Timing method</th>
    <th width="10%">Insurance excess</th>

  </tr>
  <% con.Movefirst() %>
  <% while (NOT con.EOF) %>
  <tr>
    <td><a href="searchcomp.htm?ccn=<%=con("ccn")%>"><%=con("supplier_name")%></a></td>
    <td><%=con("state")%></td>
    <td><%=con("aircraft_type")%></td>
    <td><%=con("variant")%></td>
    <td><%=con("cost_hr")%></td>
    <td><%=con("rental_basis")%></td>
    <td><%=con("timing_method")%></td>
    <td><%=con("insurance_excess")%></td>
  </tr>
  <% if con("notes") = "" then
  response.write("")
  else
  response.write("<tr><td colspan='8'><strong>Notes:</strong> ") 
  response.write(con("notes"))
  response.write("</td></tr>")
  end if
  %>
  <%
    con.MoveNext()
    Wend
  %>
</table>

</div>
<% else %>
    <% scon.Movefirst() %>
  <form id="searchf" action="search.htm" method="get">

      <p>Supplier name: <input id="supplier_name" name="supplier_name" type="text" value="<%=src_supplier_name%>"> Aircraft type: <input id="aircraft_type" name="aircraft_type" type="text" value="<%=src_aircraft_type%>"> </p>
     <p>State or Territory:<br/>
      <select style="width: 497px;" id="state" name="state">
        <option class="group" label="Any state or territory" value="any" <%if request.querystring("state") = "any" then response.write("selected") %>></option>
        <option class="group" label="Australian Capital Territory" value="ACT" <%if request.querystring("state") = "ACT" then response.write("selected") %>></option>
        <option class="group" label="New South Whales" value="NSW" <%if request.querystring("state") = "NSW" then response.write("selected") %>></option>
        <option class="group" label="Northern Territory" value="NT" <%if request.querystring("state") = "NT" then response.write("selected") %>></option>
        <option class="group" label="Queensland" value="QLD" <%if request.querystring("state") = "QLD" then response.write("selected") %>></option>
        <option class="group" label="South Australia" value="SA" <%if request.querystring("state") = "SA" then response.write("selected") %>></option>
        <option class="group" label="Tasmania" value="TAS" <%if request.querystring("state") = "TAS" then response.write("selected") %>></option>
        <option class="group" label="Victoria" value="VIC" <%if request.querystring("state") = "VIC" then response.write("selected") %>></option>
        <option class="group" label="Western Australia" value="WA" <%if request.querystring("state") = "WA" then response.write("selected") %>></option>
        <option class="group" label="Other" value="other" <%if request.querystring("state") = "other" then response.write("selected") %>></option>
      </select>
    </p>
    <input type="submit" value="Search" />
  </form>
  <br /><hr />
<p>No records match your query.</p>
<p><a href="dbtest.htm">Return to search page</a></p>
<%
  end if
  con.close
%>


<!-- End of main content -->
</div> <!-- end contentPad div -->
</div> <!-- end twocolumncontent div -->
<div class="twoColumnLinks">

<!--<div class="relatedLinks">
<h3>Related Links</h3>
<ul>
<li><a href="/index.htm">Related link 1</a></li>
</ul>
</div>--> <!-- end relatedlinks div -->
<!--#INCLUDE VIRTUAL="/_lib/include/quicklinks.htm"-->
<!--#INCLUDE VIRTUAL="/_lib/include/mylinks.htm"-->
</div> <!-- end twocolumnlinks div -->
</div> <!-- end twocolumnrow div -->
<!--#INCLUDE VIRTUAL="/_lib/include/footer.htm"-->

sgeddes · Accepted Answer · 2013-01-29 06:49:05Z

2

Are you receiving a bunch of table rows with "Notes:"? Sometimes when reading data from the database, I've needed to TRIM the results when checking to see if it's equal to "":

if Trim(con("notes") & "") = ""

Alternatively, perhaps the notes field in the database is storing other characters such as line breaks -- you should be able to view source to see what's being printed.

Hope this helps.

Good luck.

edited Jan 29, 2013 at 6:49

answered Jan 29, 2013 at 6:20

sgeddes

62.9k7 gold badges67 silver badges85 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

sephiith Over a year ago

That is perfect. Can you explain why TRIM is needed though?

sgeddes Over a year ago

From my experience, using Trim, along with concatenating a blank space to help cast the result, will ensure your check is correct. Glad I could help.

Steve · Accepted Answer · 2013-01-29 19:16:22Z

0

If sgeddes answer doesn't do it, I'm not sure, but I think Microsoft Text Driver might return nulls. You could check for null as well (I believe it is the "IsNull(arg)" function in Classic ASP).

answered Jan 29, 2013 at 19:16

Steve

5596 silver badges16 bronze badges

Collectives™ on Stack Overflow

How to ASP variables in if statement when the value is based from SQL

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related