PRB: Case Sensitivity Problem With RDO (151161)

WORKAROUND

Steps to Reproduce Behavior

1. Install MS-SQL Server as case sensitive and create a table in PUBS database:

      CREATE TABLE dbo.TestTable (
        Field1 int IDENTITY (1,1)NOT NULL,
        Field2 varchar (10))
   						

   use "INSERT INTO TestTable ( Field2 ) VALUES ('A001')" to add a record.
2. Start a new project in the 32-bit edition of Visual Basic 4.0. Form1 is created by default. Add a Command button, Command1, by default.
3. In the Click event for Command1, place the following code.
   
   Note You must change Username= <username> and PWD =<strong password> to the correct values before you run this code. Make sure that Username has the appropriate permissions to perform this operation on the database.

      Private Sub Command1_Click()
        Dim cn As rdoConnection
        Dim en As rdoEnvironment
        Dim rs1 As rdoResultset, rs2 As rdoResultset
        Dim cnstr As String, sql1 As String, sql2 As String
   
        Set en = rdoEnvironments(0)
        cnstr = "driver={sql server};server=jingsun;Username=<username>;PWD=<strong password>;database=pubs"
        Set cn = en.OpenConnection(dsname:="", _
          Prompt:=rdnoprompt, _
          ReadOnly:=False, _
          Connect:=cnstr)
   
        sql1 = "select * from TestTable where Field2 = 'a001'"
        sql2 = "select * from TestTable where Field2 = 'A001'"
        Set rs1 = cn.OpenResultset(sql1)
        Set rs2 = cn.OpenResultset(sql2)
        ' You expect rs1 to return 0 rows and rs2 to return 1 row.
        ' However, since the second SQL statement in the OpenResultSet method
        ' only differs in case from the first one, the second OpenResultSet
        ' method still sends the first SQL statement to the server and causes
        ' 0 return rows.
        If rs2.EOF Then
           MsgBox "No rows returned"
        Else
          MsgBox rs2(0) & ", " & rs2(1)
        End If
        MsgBox cn.rdoPreparedStatements.Count
        rs1.Close
        rs2.Close
        cn.Close
      End Sub
   						

4. Ensure that you have an appropriate SQL Server name, User ID and password. Press the F5 key to run the program, and click Command1. A message box appears with "No rows returned", then another message box displays cn.rdoPreparedStatements.Count, which is 1.