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


The information in this article applies to:

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

SYMPTOMS

When you run a SQL Query from Visual InterDev that has "= NULL" the data returned may be different than what you see in ISQL/W tool (which comes with SQL Server).

CAUSE

If the Use ANSI nulls, paddings and warnings check box is selected in your ODBC data source name (DSN) settings, this behavior will appear. According to ANSI standards "= NULL" is FALSE and the following query against the PUBS database in SQL Server from Visual InterDev returns NO RECORDS.

RESOLUTION

There are two ways to overcome this problem
  • Use ANSI recommended "IS NULL" instead of "= NULL" to avoid ambiguity.

    -or-
  • From your ODBC Data Source Administrator, clear the Use ANSI nulls, paddings and warnings check box for your ODBC DSN settings.

STATUS

This behavior is by design.

MORE INFORMATION

Steps to Reproduce Behavior

  • From your ODBC Data Source Administrator, select the Use ANSI nulls, paddings and warnings check box for your ODBC DSN settings. Use the PUBS database in SQL Server as an example for this test.
  • After you add a connection to your Visual InterDev project, double-click the Discounts table.
  • Make sure the Query toolbar is visible. If it's not visible, right-click the toolbar and select Query.
  • Click the SQL icon and change the query from SELECT * FROM discounts to SELECT * FROM discounts WHERE stor_id = NULL.
  • Run the query by clicking the ! icon from the query toolbar.
No records are returned for this query. Close the query window for the discounts table, clear the Use ANSI nulls, paddings and warnings check box for your ODBC DSN settings and repeat the above steps. Now the same query returns two records.
Modification Type:MajorLast Reviewed:10/22/2002
Keywords:kbDSupport kbprb KB231654
©2002 Microsoft Corporation. All rights reserved.