PRB: Use 'IS NULL' in SQL Queries when Calling from Visual InterDev (231647)


The information in this article applies to:

  • Microsoft Visual InterDev 1.0
  • Microsoft Visual InterDev 6.0
This article was previously published under Q231647

SYMPTOMS

When you run a SQL Query from Visual InterDev that has "= NULL", the data returned may be different than the data returned from other tools, such as the ISQL/W tool that comes with SQL Server 6.5.

CAUSE

If the "Use ANSI nulls, paddings and warnings." check box is selected in your ODBC DSN settings, you will see this behavior. According to ANSI standards, the "= NULL" syntax is FALSE. Executing the following query against the pubs database in SQL Server from Visual InterDev returns no records: 
"SELECT * FROM discounts WHERE stor_id = NULL"

RESOLUTION

There are two ways to work around this problem:
  • Use the ANSI recommended syntax "IS NULL" instead of "= NULL" to avoid ambiguity, as in the following example: 
    "SELECT * FROM discounts WHERE stor_id IS NULL"
  • From your ODBC Data Source Administrator, clear the Use ANSI nulls, paddings and warnings checkbox for your ODBC DSN settings.

STATUS

This behavior is by design.

MORE INFORMATION

Steps to Reproduce Behavior

  1. From your ODBC Data Source Administrator, create a new system DSN using the pubs database. Make sure to select the Use ANSI nulls, paddings and warnings checkbox for your ODBC DSN settings.
  2. Add a Data Connection to your Visual InterDev project that uses the new DSN that you created.
  3. Double-click the Discounts table in the Data View.
  4. If the Query toolbar is not visible, right-click on a toolbar and select Query.
  5. Click the SQL icon and change the query that reads: 
    SELECT * FROM discounts
    to 
    SELECT * FROM discounts WHERE stor_id = NULL
  6. Run the query by clicking the "!" icon from the Query toolbar.
  7. You will see that no records are returned for this query. Close the Query window for the discounts table, clear the Use ANSI nulls, paddings and warnings checkbox for your ODBC DSN settings.
  8. Right-click your Data Connection and select Refresh.
  9. Repeat steps 3 through 6.
You will now see the same query returning 2 records.
Modification Type:MajorLast Reviewed:5/8/2002
Keywords:kbDatabase kbDSupport kbprb KB231647
©2002 Microsoft Corporation. All rights reserved.