Microsoft KB Archive/151161

= PRB: Case Sensitivity Problem With RDO =

Article ID: 151161

Article Last Modified on 6/24/2004

-

APPLIES TO


 * Microsoft Visual Basic 6.0 Learning Edition
 * Microsoft Visual Basic 6.0 Professional Edition
 * Microsoft Visual Basic 6.0 Enterprise Edition
 * Microsoft Visual Basic 4.0 32-Bit Enterprise Edition

-



This article was previously published under Q151161



SYMPTOMS
If multiple SQL statements that only differ in letter cases are used against a case sensitive SQL Server through RDO, only the first SQL statement is used by RDO to request data from the SQL Server.



RESOLUTION
In the code below, you expect rs1 to contain 0 rows and rs2 to contain 1 row. However, because the second SQL statement in the OpenResultSet method only differs in letter cases from the first, the second OpenResultSet method just sends the first select statement to the server and causes 0 rows to return. To work around this behavior, insert a space anywhere in the second select statement. For example, you can place a leading space at the beginning of the string: sql2 = " select * from TestTable where Field2 = 'A001'" Set rs2 = cn.OpenResultset(sql2)



STATUS
This behavior is by design.



Steps to Reproduce Behavior
  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.  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.  In the Click event for Command1, place the following code.

Note You must change Username= and PWD = 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= ;PWD= ;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  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.

Additional query words: kbVBp400 kbVBp600 kbdse kbDSupport kbVBp kbRDO

Keywords: kbprb KB151161

-

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

© Microsoft Corporation. All rights reserved.