CODEWITHSUNDEEP
sql-serverasp-classic
T. Milliken
T. Milliken

Reputation: 43

T-SQL query running on classic ASP not returning NULL records

I have the following script in an ASP classic page connected to MS SQL 2012:

 <%
 Set Conn = Server.CreateObject("ADODB.Connection")
 Set RS = Server.CreateObject("ADODB.RecordSet")
 Conn.Open SQLCFG
 RS.CacheSize = 40

 RS.Open "SELECT * FROM orders WHERE (final_trackingnumber = '' OR final_trackingnumber = NULL OR final_trackingnumber LIKE '%BACK%') AND status <> 'pp' ORDER BY dt DESC", Conn, adOpenKeyset,adLockReadOnly 
%>

<table width="100%" cellpadding=7>
  <tr>
    <td class='theading'></td>
        <td class='theading'>Invoice Number</td>
        <td class='theading'>Billed to</td>
        <td class='theading'>Date Ordered</td>
        <td class='theading'>Shipping Type</td>
        <td class='theading'>Tracking ID</td>
        <td class='theading'>Status</td>
  </tr>
  <tr>
<%

Do While Not RS.EOF

%>
  <tr>
        <td align=center bgcolor="<%=bcolor%>" class="trow"><font face="arial" size=2><a href="process-view.asp?aid=<%=RS("aid")%>&oid=<%=RS("oid")%>"><B>View Order</b></A></td>
        <td bgcolor="<%=bcolor%>" class="trow"><%= RS("oid") %></td>
        <td bgcolor="<%=bcolor%>" class="trow"><%= RS("b_name") %></td>
        <td bgcolor="<%=bcolor%>" class="trow"><%= RS("dt") %></td> 
        <td bgcolor="<%=bcolor%>" class="trow"><%= RS("shippingtype") %></td>   
        <td bgcolor="<%=bcolor%>" class="trow"><%= RS("final_trackingnumber")  %></td>  
        <td bgcolor="<%=bcolor%>" class="trow"></td>    
  </tr>

<%
RS.MoveNext
Loop
%>
</table>
<%
Rs.Close
Conn.Close
%>

I can get the query to run correctly in MS SQL management studio, but when I run it on the ASP page, I do not return any records that have "final_trackingnumber = NULL". What is required in the query to return records that have NULL in a column? Suggestions below state that I need to use final_trackingnumber IS NULL, but that is not supported in ADO.

Upvotes: 0

Views: 297

Answers (2)

MBurnham
MBurnham

Reputation: 381

In TSQL, when checking if a value is NULL or not, you cannot use the = or <> comparison operators. They will always return a not true value (unkown or false).

This best approch would be myColumn IS NULL likewise if you are checking that some column doesn't equal a value make sure to check for NULL as well

WHERE (myValue IS NOT NULL) AND (myValue <> someOtherValue)

Upvotes: 0

Kannan Kandasamy
Kannan Kandasamy

Reputation: 13959

It has to be "is null" as below

final_trackingnumber is NULL

Upvotes: 5

Related Questions