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

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.

   235507 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.

   237992 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