//  home   //  advanced search   //  news   //  categories   //  sql build chart   //  downloads   //  statistics
 ASP FAQ 
Home
ASP FAQ Tutorials

   8000XXXX Errors
   Alerts
   ASP.NET 2.0
   Classic ASP 1.0
   Databases
   General Concepts
   Search Engine Optimization (SEO)

Contact Us
Site Map

Search

Web
aspfaq.com
tutorials.aspfaq.com

ASP FAQ Tutorials :: 8000XXXX Errors :: NZ() function and 80040E14 errors


Can I use the NZ() function without getting 80040E14 errors?

VBScript doesn't support the NZ() function. But here is a custom implementation, and a demonstration of its use: 

<% 
    Function NZ(ValueIfNotNull, ValueIfNull) 
        NZ = ValueIfNotNull 
        If (IsNull(NZ)) Then NZ = ValueIfNull 
    End Function 
 
    str1 = "foo" 
    Response.Write NZ(str1, "bar") 
 
    str2 = NULL 
    Response.Write NZ(str2, "bar") 
%>
 
A more common place you would expect to use NZ(), however, is returning the results of an Access query. You will find that if you try to use NZ() and call the query from an ASP page, you'll get this error: 
 
Microsoft OLE DB Provider for ODBC Drivers error '80040e14'  
[Microsoft][ODBC Microsoft Access Driver] Undefined function 'NZ' in expression.
 
Let's say you have a numeric column, and want it to return 0 instead of NULL for all records that are NULL. 
 
In Access, you would say: 
 
SELECT ColumnValue =  
    NZ(Column, 0) 
    FROM table ...
 
Since you can't use NZ() from ODBC/OLE-DB, you can use IIF instead: 
 
SELECT ColumnValue = 
    IIF(ISNULL([Column]), 0, [Column]) 
    FROM table ...
 
Or you can try IFNULL if you are going through ODBC, e.g. 
 
SELECT ColumnValue = 
    IFNULL(Column, 0) 
    FROM table ...
 
If you are porting this code to SQL Server, you would use slightly different syntax: 
 
SELECT ColumnValue = 
    COALESCE(Column, 0) 
    FROM table ...
 
or 
 
SELECT ColumnValue = 
    ISNULL(Column, 0) 
    FROM table ...
 
(I prefer the COALESCE expression, which accepts multiple successive arguments. So you could say COALESCE(col1, col2, col3, ...) and it would return the *first* column that is NOT NULL.) 
 
Of course if NULL means the same thing as 0, you should consider running this query: 
 
UPDATE column SET column = 0 WHERE column IS NULL
 
For more information on preventing NULLs from entering your ASP page, or avoiding NULLs in your data altogether, see Article 2150 and Article #2073.

Related Articles

Why do I get 8002000A errors?
Why do I get 80040200 / 80040514 / 800A0E7A errors?
Why do I get 80040e09 errors?
Why do I get 80040E0C errors?
Why do I get 80040E10 errors?
Why do I get 80040E14 errors?
Why do I get 80040E21 errors?
Why do I get 80040E23 errors?
Why do I get 80040E24 errors?
Why do I get 80040E2F errors?
Why do I get 80040e30 errors?
Why do I get 80040E31 errors?
Why do I get 80040E37 errors?
Why do I get 80040e4e errors?
Why do I get 80040E54 errors?
Why do I get 80040E57 / 80040E07 errors?
Why do I get 8004D00A errors?
Why do I get 80070070 errors?
Why do I get 800A01FB errors?
Why do I get 800A0BB9 / 800A1391 errors?
Why do I get 800A0C93 errors?
Why do I get 800a0cb3 errors?
Why do I get 800A0CC1 errors?
Why do I get 800A0E78 errors?
Why do I get 800A0E7C errors?
Why do I get 800A0E7D errors?
80004005 Errors

 

 


Created: 8/26/2002 | Last Updated: 6/9/2006 | broken links | helpful | not helpful | statistics
© Copyright 2006, UBR, Inc. All Rights Reserved. (153)

 

Copyright 1999-2006, All rights reserved.
Finding content
Finding content.  An error has occured...