PRB: Visual Basic String (BSTR) in Variant Field in ADO Recordset Cannot Be Persisted to ADTG (288403)


The information in this article applies to:

  • Microsoft Data Access Components 2.1
  • Microsoft Data Access Components 2.1 (GA)
  • Microsoft Data Access Components 2.1 SP1
  • Microsoft Data Access Components 2.1 SP2
  • Microsoft Data Access Components 2.5
  • Microsoft Data Access Components 2.5 SP1
  • Microsoft Data Access Components 2.6
  • Microsoft Data Access Components 2.7
This article was previously published under Q288403

SYMPTOMS

If you create an ActiveX Data Objects (ADO) or ADOR recordset with a field of type adVariant, and populate that field with a Microsoft Visual Basic string value, then call the Recordset's Save method, specifying ADTG as the persistence format, the Save method appears to work with MDAC 2.1, but fails under MDAC version 2.5 or later with the following error message:
Run time error -2147217891 (80040e1d) Requested conversion is not supported

CAUSE

The ADTG persistence format available for ADO Recordsets does not support the following Variant field types: VT_RECORD, VT_ILLEGAL, VT_VARIANT, VT_UNKNOWN, VT_BSTR, and VT_DISPATCH. These variant subtypes are explicitly disallowed by the persistence provider for ADTG. The XML persistence format supports a slightly different set of types.

RESOLUTION

Because Visual Basic strings cannot be persisted to ADTG format in a field of ADO datatype adVariant, a string datatype such as adChar or adVarchar must be used for Visual Basic string fields.

STATUS

This behavior is by design and is now more clearly documented in the MDAC 2.5 documentation than in previous MDAC documentation versions.

MORE INFORMATION

Under MDAC 2.1, the attempt to persist a Visual Basic string in ADTG format in an adVariant field did not generate an error message. However the data that MDAC 2.1 saved in this case was not, in fact, valid. Therefore, the error message that is raised by MDAC 2.5 or later indicates improved error-handling in the persistence provider, not a change in behavior.

Steps to Reproduce Behavior

  1. Paste the following code into an event procedure in a Visual Basic project, with a reference to the ADOR library. Note that field "C" is of type adVariant and is being filled with Visual Basic String values.
    Dim intI As Integer
Dim RS As ADOR.Recordset
Dim strTempFile As String
Dim varV As Variant
    
Set RS = New ADOR.Recordset
With RS
    .Fields.Append "A", adInteger
    .Fields.Append "B", adBSTR
    .Fields.Append "C", adVariant, , adFldIsNullable
    .Fields.Append "D", adSingle
    .Fields.Append "E", adSmallInt
    .Fields.Append "F", adTinyInt
    .Fields.Append "G", adBigInt
    .Fields.Append "H", adBoolean
    .Fields.Append "I", adDBTimeStamp
    .Open
    .Fields("A").Properties("Optimize") = True
End With
        
For intI = 1 To 100
    RS.AddNew
    varV = "abcde"
    With RS
        .Fields("A") = intI
        .Fields("B") = "abcde"
        .Fields("C") = varV
        .Fields("D") = 123.456
        .Fields("E") = 10000
        .Fields("F") = 100
        .Fields("G") = 1000000
        .Fields("H") = True
        .Fields("I") = Now
    End With
    RS.Update
Next intI

strTempFile = "c:\ADOPersistence.TMP"
On Error Resume Next
Kill strTempFile
On Error GoTo 0
RS.Save strTempFile, adPersistADTG
RS.Close
  2. Test the code with MDAC 2.1, and note that it appears to succeed.
  3. Test the code with MDAC 2.5 or later, and note that it fails with the conversion error message noted in the "Symptoms" section.
Modification Type:MajorLast Reviewed:5/8/2003
Keywords:kbprb KB288403
©2002 Microsoft Corporation. All rights reserved.