FIX: Edit/Update Fails for SQL 7.0 for Column of Type Text When CursorDriver Is Set to rdUseODBC

View products that this article applies to.

This article was previously published under Q257657

SYMPTOMS

When editing a column of type Text in SQL Server 7.0 through a Remote Data control or an rdoEnvironment object opened with the CursorDriver property set to rdUseODBC, the update fails.

Back to the top

CAUSE

This behavior occurs because the ODBC cursor library uses all columns to build the WHERE clause. For inexact types, such as Text, the driver may be unable to locate the row.

Back to the top

RESOLUTION

Install the latest service pack for Visual Studio 6.0.

To work around the problem, use ActiveX Data Objects (ADO). Or, do not use ODBC client-side cursors with Remote Data Objects (RDO).

Back to the top

STATUS

Microsoft has confirmed that this is a bug in the Microsoft products that are listed at the beginning of this article. This bug was corrected in the latest service pack for Visual Studio 6.0.

For additional information about Visual Studio service packs, click the following article numbers to view the articles in the Microsoft Knowledge Base:

194022 (http://support.microsoft.com/kb/194022/EN-US/ ) INFO: Visual Studio 6.0 Service Packs, What, Where, Why

194295 (http://support.microsoft.com/kb/194295/EN-US/ ) HOWTO: Tell That a Visual Studio Service Pack Is Installed

To download the latest Visual Studio service pack, visit the following Microsoft Web site:

http://msdn2.microsoft.com/en-us/vstudio/Aa718353.aspx (http://msdn2.microsoft.com/en-us/vstudio/Aa718353.aspx)

Back to the top

MORE INFORMATION

Steps to Reproduce Behavior

Create a new Visual Basic Standard EXE project. Form1 is created by default.
On the Project menu, choose References, and then add a reference to the Microsoft Remote Data Objects 2.0 library.
Remove Form1, and add a new module.

Insert the following code into the module:

Note You must change uid=<username> and pwd=<strong password> to the correct values before you run this code. Make sure that the uid has the appropriate permissions to perform this operation on the database.

Option Explicit

Public Sub Main()
    Dim cn As New rdoConnection
    Dim rs As rdoResultset
    Dim strTemp As String
    
    cn.CursorDriver = rdUseODBC
    cn.Connect = "driver={sql server};server=MyServer;database=pubs;uid=<username>;pwd=<strong password>;"
    cn.EstablishConnection
    Set rs = cn.OpenResultset("SELECT au_lname FROM Authors", _
        rdOpenKeyset, rdConcurRowVer)
    strTemp = "NewLastName"
    rs.Edit
    rs!au_lname = strTemp
    rs.Update
    rs.Requery
    If rs!au_lname <> strTemp Then
        Debug.Print "Test FAILED!! -- Strings don't match"
    Else
        Debug.Print "Test PASSED!! -- Strings match"
    End If
    
    rs.Close
    cn.Close
End Sub

If the source database for the data is SQL Server 7.0, the test fails.

Back to the top