Microsoft KB Archive/231647

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

Article ID: 231647

Article Last Modified on 5/8/2002

-

APPLIES TO


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

-



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.



Steps to Reproduce Behavior
 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. Add a Data Connection to your Visual InterDev project that uses the new DSN that you created.</li> Double-click the Discounts table in the Data View.</li> If the Query toolbar is not visible, right-click on a toolbar and select Query.</li>  Click the SQL icon and change the query that reads: SELECT * FROM discounts to SELECT * FROM discounts WHERE stor_id = NULL </li> Run the query by clicking the "!" icon from the Query toolbar.</li> 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.</li> Right-click your Data Connection and select Refresh.</li> Repeat steps 3 through 6.</li></ol>

You will now see the same query returning 2 records.

Keywords: kbdatabase kbprb KB231647

-

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

© Microsoft Corporation. All rights reserved.