BUG: Jet Provider Errors when Setting a Field Equal to a Field Containing an Empty String

• Microsoft Visual Basic Learning, Professional, and Enterprise Editions for Windows, version 6.0
• ActiveX Data Objects (ADO), versions 2.1, 2.1 SP1

SYMPTOMS

When you save a recordset text field containing an empty string to another recordset's field (this assumes both fields allow zero length), you get the following error message:

Errors occurred. '-2147217887 (80040e21)'.

CAUSE

ADO thinks it is dealing with an object instead of a property.

RESOLUTION

This error does not occur if the value property is specified when setting the contents of one field equal to the contents of another field. ADO needs specific directions to let it know that you are dealing with a property and not an object. The value property of the field clarifies this.

In addition, changing the cursor location to aduseserver solves this problem.

STATUS

Microsoft has confirmed this to be a bug in the Microsoft products listed at the beginning of this article.

MORE INFORMATION

Steps to Reproduce Behavior

1. Start a new Standard EXE project. Form1 is added by default.



2. From the Project menu, click References, and select the Microsoft ActiveX Data Objects 2.0 or 2.1 and Microsoft ADO EXT. 2.1 for DDL and Security, if it is not already loaded.



3. Place a CommandButton on the form, Command1.



4. Place the following code into Form1:




' This sample requires ADO 2.1 and ADO Extensions 2.1 to
    ' be referenced.
    Private Sub Command1_Click()
        Dim TheConn As New ADODB.Connection
        Dim TheCat As New ADOX.Catalog
        Dim TheCursor As New ADODB.Recordset
        Dim TheCursor2 As New ADODB.Recordset
        Dim Tbl As New ADOX.Table
        Const sDBName = "c:\test.mdb"

        ' Create new database, with two similar tables MyTable and MyTable2
        On Error Resume Next
        Kill sDBName
        On Error GoTo 0

        TheCat.Create "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + sDBName
        Tbl.Name = "MyTable"
        Tbl.Columns.Append "Field1", adVarWChar, 20
        Tbl.Columns.Append "id", adInteger
        TheCat.Tables.Append Tbl
        TheCat.Tables("MyTable").Columns("Field1").Properties("JET OLEDB:Allow Zero Length") = True
        Set Tbl = New Table
        Tbl.Name = "MyTable2"
        Tbl.Columns.Append "Field1", adVarWChar, 20
        Tbl.Columns.Append "id", adInteger
        TheCat.Tables.Append Tbl
        TheCat.Tables("MyTable2").Columns("Field1").Properties("JET OLEDB:Allow Zero Length") = True
        TheConn.CursorLocation = adUseClient
        TheConn.Provider = "Microsoft.Jet.OLEDB.4.0"
        TheConn.Open sDBName
        TheConn.Execute "INSERT INTO MyTable (ID, Field1) VALUES (1, '')"
        TheConn.Execute "INSERT INTO MyTable2 (ID, Field1) VALUES (2, 'fred')"
        TheCursor.Open "SELECT * From MyTable WHERE ID = 1", TheConn, adOpenStatic, adLockReadOnly, adCmdText
        TheCursor2.Open "SELECT * From MyTable2 WHERE ID = 2", TheConn, adOpenDynamic, adLockPessimistic, adCmdText
        TheCursor2("Field1") = TheCursor("Field1") ' get error here<BR/>
        'comment the above line and uncomment the line below to eliminate this error <BR/>
        'TheCursor2("Field1").value = TheCursor("Field1").value ' get error here
        TheCursor2.Update
    End Sub 

5. Run the project and click Command1.

Additional query words:

Keywords : kbDatabase kbDataBinding kbGrpVBDB kbDSupport kbADO210sp2bug
Version : WINDOWS:2.1,2.1 SP1,6.0
Platform : WINDOWS
Issue type : kbbug