Microsoft KB Archive/231654

= PRB: Use IS NULL in SQL Queries when Calling from Visual InterDev =

Article ID: 231654

Article Last Modified on 10/22/2002

-

APPLIES TO


 * Microsoft Visual InterDev 1.0 Standard Edition
 * Microsoft Visual InterDev 6.0 Standard Edition

-



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.



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.

Keywords: kbprb KB231654

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.