PRB: TDS Error with Substring and Multiple ADO Recordsets (235052)

MORE INFORMATION

Steps to Reproduce the Behavior

1. In Visual Basic 5.0 or 6.0, open a new Standard EXE project.
2. From the Project menu select the References menu to add a Reference to Microsoft ActiveX Data Objects 2.x.
3. Paste the following code into the Form1 Code window.
   
   Note You must change Server=<Server Name>, Uid=<username>, and Pwd=<strong password> to the appropriate server name, username, and password before you run this code. Make sure that the changed Uid has the appropriate permissions to perform the required operations on the specified database.

   Option Explicit
   Const strConnect = "Provider=MSDASQL;Driver={SQL Server};Server=<Server Name>;Database=Pubs;Uid=<username>;Pwd=<strong password>"
   
   Private Sub Form_Click()
   
   On Error GoTo ErrorHandler
       Dim adoCn As ADODB.Connection
       Dim adoRs1 As ADODB.Recordset
       Dim adoRs2 As ADODB.Recordset
       Dim strSQL As String
   
       Set adoCn = New ADODB.Connection
       With adoCn
           .CursorLocation = adUseClient
           .ConnectionString = strConnect
           .CommandTimeout = 20
           .Open
       End With
   
       strSQL = "Select au_lname from Authors"
   
       Set adoRs1 = New ADODB.Recordset
       With adoRs1
           .CursorLocation = adUseClient
           Set .ActiveConnection = adoCn
           .CursorType = adOpenStatic
           .LockType = adLockOptimistic
           .Open strSQL
       End With
   
       'strSQL = "Select substring(au_lname, 1, 2) as subLname, " & _
                       "au_fname, city " & _
                       "From Authors " & _
                       "Order By subLname asc, au_fname asc, city asc"
   
       strSQL = "Select substring(au_lname, 1, 2) as subLname, " & _
                       "au_fname, city " & _
                       "From Authors " & _
                       "Order By au_fname asc, subLname asc, city asc"
   
       Set adoRs2 = New ADODB.Recordset
       With adoRs2
           .CursorLocation = adUseClient
           Set .ActiveConnection = adoCn
           .CursorType = adOpenStatic
           .LockType = adLockOptimistic
           .Open strSQL
       End With
       
       While Not adoRs2.EOF
           Debug.Print adoRs2.Fields(0).Value
           adoRs2.MoveNext
       Wend
       
       MsgBox "Success", vbOKOnly, "Data Object"
       Exit Sub
       
   ErrorHandler:
       MsgBox "Change Failed:" & vbCrLf & Err.Number & vbCrLf & Err.Description, vbOKOnly, "Data Object"
       Exit Sub
       
   End Sub
   					

4. Run the Project and click on the Form.