Microsoft KB Archive/271645

= PRB: CompareBookmarks Method and Bookmarks in ADO =

Article ID: 271645

Article Last Modified on 5/12/2003

-

APPLIES TO


 * Microsoft ActiveX Data Objects 2.1
 * Microsoft ActiveX Data Objects 2.5
 * Microsoft ActiveX Data Objects 2.6
 * Microsoft ActiveX Data Objects 2.7
 * Microsoft Data Access Components 2.1
 * Microsoft Data Access Components 2.5
 * Microsoft Data Access Components 2.6
 * Microsoft Data Access Components 2.7

-



This article was previously published under Q271645



SYMPTOMS
The CompareBookmarks method in ActiveX Data Objects (ADO) compares two bookmarks, and then returns an indication of their relative values. When comparing two bookmarks the following error message may occur:

Arguments are of the wrong type, are out of acceptable range, or are in conflict with one another.

This article explains the reason for the error message and also describes how to prevent the error message from occurring.



CAUSE
When you compare bookmarks with the CompareBookmarks method, ADO does not attempt any type of data type conversion. ADO merely passes the values to the OLE DB provider being used, which is where the comparison occurs. The preceding error message might occur with the CompareBookmarks method, if the bookmarks are stored in variables of a type other than Variant.



STATUS
This behavior is by design.



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 &quot;Symptoms&quot; 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:

http://msdn.microsoft.com/dataaccess

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 = &quot;TB_COMPAREBOOKMARKS&quot; sSQL = &quot;Create Table &quot; & sTblName & &quot; (PrimaryKey int identity CONSTRAINT pk_&quot; & sTblName & &quot; PRIMARY KEY)&quot;

' Modify the connection string as required. cn.Open &quot;Provider=SQLOLEDB;UID=YourUser;PWD=YourPassword;Data Source=YourServer;Initial Catalog=YourDatabase;&quot; On Error Resume Next cn.Execute &quot;Drop Table &quot; & 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

Additional query words: compare bookmarks

Keywords: kbcodesnippet kbdatabase kbprb KB271645

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.