When using ActiveX Data Objects (ADO) against certain OLE DB providers, you may find that recordset fields that should be NULL do not test for NULL correctly either by checking the VARIANT.vt value for VT_NULL (in Microsoft Visual C++) or by using IsNull (in Microsoft Visual Basic).
Certain OLEDB providers may not set the VARIANT.vt field of VARIANT values in a rowset to VT_NULL for NULL values. According to the OLE DB specification, this behavior is correct; the only responsibility of the provider is to set the dwStatus field of the accessor's bindings to DBSTATUS_S_ISNULL.
Because ADO is an OLE DB consumer, it has the responsibility to check this dwStatus value and if necessary set the VARIANT.vt field of VARIANT values in the recordset to VT_NULL. Because the current versions of ADO do not check this dwStatus value, an ADO programmer checking the VARIANT.vt field of a VARIANT value in an ADO recordset will not be able to correctly determine if the VARIANT value is NULL.
Although it is not the provider's responsibilty to set the VARIANT.vt field of VARIANT datatypes to VT_NULL, most providers, including Microsoft OLEDB Provider for SQL Server (SQLOLEDB), Microsoft OLEDB Provider for ODBC Driver (MSDASQL), Microsoft Jet OLEDB Provider (MSJETOLEDB40), and the Microsoft Oracle OLEDB Provider (MSDAORA) will set the dwStatus of the accessor's bindings to DBSTATUS_S_ISNULL and will also set the VARIANT.vt field to VT_NULL. Therefore, with these providers ADO will behave as expected. However, with providers who do not set this datatype to VT_NULL, you could face this problem.
To resolve this problem, obtain the latest service pack for SQL Server 2000, the latest service pack for MDAC 2.6, or the hotfix referenced below.
For additional information, click the article number below
to view the article in the Microsoft Knowledge Base:
290211Â
(http://kbalertz.com/Feedback.aspx?kbNumber=290211/EN-US/
)
INF: How to Obtain the Latest SQL Server 2000 Service Pack
For additional information, click the article number below
to view the article in the Microsoft Knowledge Base:
300635Â
(http://kbalertz.com/Feedback.aspx?kbNumber=300635/EN-US/
)
INFO: How to Obtain the Latest MDAC 2.6 Service Pack
Hotfix
The English version of this fix should have the following file attributes or later:
Date Version Size File name Platform
-----------------------------------------------------------
21-SEP-2001 2.61.7921.0 528,656 Msado15.dll x86
Microsoft has confirmed that this is a problem in the Microsoft products that are listed at the beginning of this article. This problem was first corrected in SQL Server 2000 Service Pack 2 and MDAC 2.6 Service Pack 2.