PRB: Query Testing for NULL in Access Database Does not Return Records with Jet 4.0

• Microsoft Data Access Components version 2.1 (GA)
• Microsoft ODBC Driver for Access, version 4.0
• Microsoft OLE DB Provider for Jet, version 4.0

SYMPTOMS

When using an SQL statement with a WHERE clause that contains a test for NULL using the = operator, records that match the query are not returned in the recordset. This happens with Jet 4.0 when using DAO, ODBC or OLE DB. Here are a few examples of such queries:

SELECT * FROM Customers WHERE region = NULL
SELECT * FROM Customers WHERE region = NOT NULL 

CAUSE

Jet 4.0 made some changes to the SQL syntax and is now more SQL ANSI 92 compliant than before. Especially SQL ANSI 92 NULL behavior was implemented. Also remember that the result of a Boolean expression can have three results - TRUE, FALSE and NULL (in some documents referred as UNKNOWN).

The ANSI compliant syntax for testing NULL values uses the IS keyword. Examples of queries that work are:

SELECT * FROM Customers WHERE region IS NULL
SELECT * FROM Customers WHERE region IS NOT NULL 

RESOLUTION

Use the IS keyword in queries that test for NULL. This change will not break on systems with Jet 3.5 as Jet 3.5 accepted both types of queries.

STATUS

This behavior is by design.

MORE INFORMATION

Steps to reproduce

MFC ODBC

1. Create an ODBC DSN in the ODBC Data Sources applet in the Control Panel that points to the Northwind.mdb file.



2. Create an MFC AppWizard application with database support. Use ODBC and specify the Customers table from the ODBC source created in the previous step as the source for your recordset.



3. Set a filter on the recordset before it is open. In your view class, insert the following line as the second line in the OnInitialUpdate() method.

   
   m_pSet->m_strFilter = "Region = NULL"; 




4. Create an edit box and bind it to the CustomerID field in the recordset to see the field on the form.



5. Compile and run the program. No records will be returned.

MFC DAO

1. Create an MFC AppWizard application with database support. Use DAO and specify the Customers table from the Northwind sample database as the source for your recordset.



2. Apply the fix from the following article if using a non-unicode build.

   Q235507 BUG: DAO 3.6 Causes Debug Errors in MFC DAO Non-Unicode Builds




3. Insert the following line in the application's InitInstance method to load DAO 3.6 as described in the following article.

   Q237992 PRB: Unrecognized Database Format Error with Access 2000

   
   AfxGetModuleState()->m_dwVersion = 0x0601; 

   This will ensure that the Jet 4.0 engine is loaded.



4. Set a filter on the recordset before it is open. In your view class, insert the following line as the second line in the OnInitialUpdate() method.

   
   m_pSet->m_strFilter = "Region = NULL"; 




5. Create an edit box and bind it to the CustomerID field in the recordset to see the field on the form.



6. Compile and run the program. No records will be returned.

ADO

1. Create a Win32 console application.



2. Insert the following line to use ADO.

   
   #import "C:\Program Files\Common Files\System\ado\msado15.dll" no_namespace rename( "EOF", "adoEOF" ) 




3. Paste the following code into your main function:

   
   CoInitialize(NULL);
   
   try
   {
   	_RecordsetPtr pRs;
   	pRs.CreateInstance(__uuidof(Recordset));
   	pRs->Open(L"SELECT * FROM Customers WHERE Region = NULL",
   				L"Provider=Microsoft.Jet.OLEDB.4.0;"
   				L"Data Source=C:\\Program Files\\Microsoft Office\\Office\\Samples\\Northwind.mdb;"
   				L"Persist Security Info=False",
   				adOpenStatic,
   				adLockOptimistic,
   				-1)<BR/>
   
           if (pRs->adoEOF)
   	        cout << "No records returned.\n";
   
   	while (!pRs->adoEOF)
   	{
   		cout << (char*) (_bstr_t) pRs->GetFields()->GetItem(L"CustomerID")->GetValue() << endl;
   		pRs->MoveNext();
   	}
   
   
   }
   catch (_com_error& e)
   {
   	cout << (char*) e.Description() << endl;
   }
   
    




4. Change the location of the Northwind.mdb file if necessary to reflect the location of the file on your system.



5. Compile and run the code. No records will be returned.

Resolution

REFERENCES

For additional information, click the article numbers below to view the articles in the Microsoft Knowledge Base:

Q236991 PRB: Unrecognized Database Format Error with Access 2000 Database

Q235507 BUG: DAO 3.6 Causes Debug Errors in MFC DAO Non-Unicode Builds

Q152021 PRB: Deleting Records Containing NULLs Using DAO

Additional query words: empty 3.51 odbcjt32.dll msjetoledb.dll zero

Keywords : kbADO kbADO210 kbDAO kbDatabase kbJET kbMDAC kbODBC kbOLEDB kbVC kbGrpVCDB kbMDAC210
Version : WINDOWS:2.1 (GA),4.0
Platform : WINDOWS
Issue type : kbprb