DOCUMENT:Q242993  02-MAY-2001  [odbc]
TITLE   :BUG: Retrieved Data May Display as Nonalphanumeric Using ODBC
PRODUCT :Open Database Connectivity (ODBC)
PROD/VER:WINDOWS:2.1 SP2,3.7
OPER/SYS:
KEYWORDS:kbOSWinNT400bug kbOSWin2000bug kbODBC kbSQLServ kbVBp600bug kbGrpDSVBDB kbMDAC210SP2bug

======================================================================
-------------------------------------------------------------------------------
The information in this article applies to:

 - Microsoft ODBC Driver for SQL Server, version 3.7 
 - Microsoft Data Access Components version 2.1 SP2 
-------------------------------------------------------------------------------

SYMPTOMS
========

The display of the second and subsequent SQL Server text columns retrieved using
the SQL Server ODBC Driver version 3.70.0690 with Data Access Objects (DAO) or
with Remote Data Objects (RDO) may appear as nonalphanumeric characters.

This behavior occurs in Windows 9x, Windows NT 4.0 and Windows 2000 RC2 with the
SQL Server ODBC driver, version 3.70.0690 or later using Data Access Object
(DAO) or Remote Data Objects (RDO). This behavior does not affect the data
stored on the server.

RESOLUTION
==========

There are two ways to work around to this behavior:

 - Reverse the order of the columns when you retrieve the data. For example, if
   your record has three columns, retrieve the third column first, then the
   second column, and finish with the first column.

   For example, if the table contains 3 columns, the last two of which are Text
   columns, access the third column first in assignment or print statements.
   Instead of using this:

   debug.print rs(0).Value
   debug.print rs(1).Value
   debug.print rs(2).Value

   Use this:

   debug.print rs(2).Value
   debug.print rs(1).Value
   debug.print rs(0).Value

   -or-

 - Use the SQL Server version 3.70.0623 or earlier driver.

STATUS
======

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

MORE INFORMATION
================

This section shows you how to create a sample project that demonstrates this
behavior using DAO. The steps assume that you can connect and write to a SQL
Server database using the SQL Server ODBC driver.

Steps to Reproduce Behavior
---------------------------

1. Start a Standard EXE project in Visual Basic. Form1 is created by default.

2. Make a reference to the DAO 3.6 Object Library.

3. Add a command button named Command1 to Form1.

4. Copy the following code into the Form1 Code window. Make the appropriate
   changes to connect to your SQL Server database:

   Option Explicit

   Private Sub Command1_Click()
      Dim TestConn As DAO.Connection
      Dim TheCursor As DAO.Recordset
      Dim sData1 As String
      Dim sData2 As String
      
      ' Connect to SQL database
      DBEngine.DefaultType = dbUseODBC
      DBEngine.Workspaces(0).DefaultCursorDriver = -1
      Set TestConn = DBEngine.Workspaces(0).OpenConnection("", _
                     dbDriverNoPrompt, _
                     False, _
                     "ODBC;DSN=<DSN>;UID=<User Name>;PWD=<Password>")

      ' Create a table and enter data into the table
      On Error Resume Next
      TestConn.Execute "DROP TABLE MyTable"
      
      On Error GoTo 0
      TestConn.Execute "CREATE TABLE MyTable (" _
         + " id int unique, Field1 text NULL , " _
         + " Field2 text null)"
      TestConn.Execute "INSERT INTO MyTable (id, Field1, Field2) " _
         + " VALUES (1, '" & "apple" & "', '" & "banana" & "')"

      ' Select data from the newly inserted record into a Recordset
         Set TheCursor = TestConn.OpenRecordset("SELECT * From MyTable " _
                        + " WHERE ID = 1", _
                        dbOpenDynaset, _
                        dbExecDirect, _
                        dbReadOnly)
           
      sData1 = TheCursor("Field1")
      sData2 = TheCursor("Field2")

      ' Test to check if data output is the same as the table data
      If sData1 <> "apple" Or sData2 <> "banana" Then
         MsgBox "Test failed.  Data1=" & sData1 & ", Data2=" & sData2
      Else
         MsgBox "Test succeeded."
      End If
   End Sub

5. Run the project and click the Command1 command button. A message box appears.

RESULT: The second item displayed in the message box displays incorrectly.

Use the SQL Server Enterprise Manager to view the contents of the table named
MyTable. Note that the correct data is stored in the record.

Additional query words:

======================================================================
Keywords          : kbOSWinNT400bug kbOSWin2000bug kbODBC kbSQLServ kbVBp600bug kbGrpDSVBDB kbMDAC210SP2bug 
Technology        : kbSQLServSearch kbAudDeveloper kbODBCSearch kbMDACSearch kbMDAC210SP2 kbODBCSQLServ370
Version           : WINDOWS:2.1 SP2,3.7
Issue type        : kbbug
Solution Type     : kbpending

=============================================================================

THE INFORMATION PROVIDED IN THE MICROSOFT KNOWLEDGE BASE IS
PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND.  MICROSOFT DISCLAIMS
ALL WARRANTIES, EITHER EXPRESS OR IMPLIED, INCLUDING THE WARRANTIES
OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE.  IN NO
EVENT SHALL MICROSOFT CORPORATION OR ITS SUPPLIERS BE LIABLE FOR
ANY DAMAGES WHATSOEVER INCLUDING DIRECT, INDIRECT, INCIDENTAL,
CONSEQUENTIAL, LOSS OF BUSINESS PROFITS OR SPECIAL DAMAGES, EVEN IF
MICROSOFT CORPORATION OR ITS SUPPLIERS HAVE BEEN ADVISED OF THE
POSSIBILITY OF SUCH DAMAGES.  SOME STATES DO NOT ALLOW THE EXCLUSION
OR LIMITATION OF LIABILITY FOR CONSEQUENTIAL OR INCIDENTAL DAMAGES
SO THE FOREGOING LIMITATION MAY NOT APPLY.

Copyright Microsoft Corporation 2001.