PRB: CompareBookmarks Method and Bookmarks in ADO (271645)

MORE INFORMATION

When you open a Recordset object in ADO, each of its records have a unique bookmark. To save the bookmark for the current record, assign the value of the Recordset object's Bookmark property to a Variant variable. You can quickly return to that record at any time after moving to a different record, by setting the Recordset object's Bookmark property back to the value of that saved Variant variable.

Because the data type of a bookmark is OLE DB provider-specific, ADO exposes them as Variants. For example, SQL Server bookmarks are of type DBTYPE_R8 (Double). ADO therefore exposes these as Variants with a subtype of Double.

When comparing bookmarks with the CompareBookmarks method, ADO does not attempt any type of data type coercion. ADO passes the values to the OLE DB provider, which is where the comparison occurs. The error message described in the "Symptoms" section might occur with the CompareBookmarks method if the bookmarks are stored in variables of type other than Variant.

To avoid unpredictable behavior, always retrieve the value of a Recordset object's Bookmark property into a Variant variable. Do not store it in anything other than a Variant. After retrieving the value from the Bookmark property, do not coerce the bookmark value to other data types.

Steps to Reproduce the Behavior

The following Microsoft Visual Basic code example shows how to use the CompareBookmarks method. In the code the bookmarks are deliberately stored in non-Variant types. The comments in the code show where the error message might occur.

To use this example, you must have Microsoft Data Access Components (MDAC) version 2.x or higher installed, which you can download from the following Web address: NOTE: This example is shown only to demonstrate the error message (not as an example of how to handle bookmarks with ADO) and uses SQL Server as the backend database.

    Dim cn As New Connection, rs As New Recordset
    Dim sSQL As String, iCounter As Integer
    Dim varBookMark1 As Variant, varBookMark2 As Variant
    Dim intBookMark As Integer, lngBookMark As Long

    Const sTblName = "TB_COMPAREBOOKMARKS"
    sSQL = "Create Table " & sTblName & " (PrimaryKey int identity CONSTRAINT pk_" & sTblName & " PRIMARY KEY)"

    ' Modify the connection string as required.
    cn.Open "Provider=SQLOLEDB;UID=YourUser;PWD=YourPassword;Data Source=YourServer;Initial Catalog=YourDatabase;"
    
    On Error Resume Next
        cn.Execute "Drop Table " & sTblName
    On Error GoTo 0

    cn.Execute sSQL
    rs.Open sTblName, cn, adOpenKeyset, adLockOptimistic, adCmdTable
    For iCounter = 1 To 100
        rs.AddNew
        rs.Update
    Next iCounter
    
    rs.MoveFirst
    intBookMark = rs.Bookmark
    lngBookMark = rs.Bookmark
    varBookMark1 = rs.Bookmark
    varBookMark2 = rs.Bookmark
    Debug.Print intBookMark, lngBookMark, varBookMark1, varBookMark2
    Debug.Print TypeName(intBookMark), TypeName(lngBookMark), TypeName(varBookMark1), TypeName(varBookMark2)
    ' Next line works, because variant type is used.
    Debug.Print rs.CompareBookmarks(varBookMark1, varBookMark2)
    ' Next two lines work, but the results are unpredictable because non-variant types are used.
    Debug.Print rs.CompareBookmarks(lngBookMark, lngBookMark)
    Debug.Print rs.CompareBookmarks(intBookMark, lngBookMark)
    Debug.Print TypeName(intBookMark), TypeName(lngBookMark), TypeName(varBookMark1), TypeName(varBookMark2)
    
    rs.MoveLast
    intBookMark = rs.Bookmark

    lngBookMark = rs.Bookmark
    varBookMark1 = intBookMark
    varBookMark2 = lngBookMark
    Debug.Print intBookMark, lngBookMark, varBookMark1, varBookMark2
    Debug.Print TypeName(intBookMark), TypeName(lngBookMark), TypeName(varBookMark1), TypeName(varBookMark2)
    ' Next two lines work, because SQL Server's bookmark type is Double, but this is not recommended.
    Debug.Print rs.CompareBookmarks(CDbl(intBookMark), CDbl(lngBookMark))
    Debug.Print rs.CompareBookmarks(CDbl(varBookMark1), CDbl(varBookMark2))
    ' Next four lines fail with the error message.
    Debug.Print rs.CompareBookmarks(intBookMark, lngBookMark)
    Debug.Print rs.CompareBookmarks(lngBookMark, lngBookMark)
    Debug.Print rs.CompareBookmarks(varBookMark1, varBookMark2)
    Debug.Print rs.CompareBookmarks(CVar(varBookMark1), CVar(varBookMark2))
    Debug.Print TypeName(intBookMark), TypeName(lngBookMark), TypeName(varBookMark1), TypeName(varBookMark2)
    
    rs.Close
    cn.Close
    Set rs = Nothing
    Set cn = Nothing