• The Prepared property on the command object is set to TRUE.
• The query has a parameterized LIKE clause.
• The column included with the LIKE clause is defined as CHAR (and not as VARCHAR) at the back end.
• The parameter value has fewer characters than the back-end column size.
• You are connecting to either a Microsoft SQL Server 7.0 or SQL Server 2000 database. If it is a SQL Server 2000 database, the Defer Prepare property is set to FALSE on the command object.

Back to the top

    Date           Version         Size        File name     
    ------------------------------------------------------------
    18-OCT-2001    2.62.8018.0     528,656     Msado15.dll		
				

Back to the top

WORKAROUND

Back to the top

Steps to Reproduce Behavior

1. Create a Standard EXE project using Microsoft Visual Basic 6.0. Add a Project Reference to the Microsoft ActiveX Data Objects 2.6 Library.
2. Add a CommandButton to the main Form.
3. Double-click the Command button. Paste the following code in the Command1_Click() event handler:
   
   Note You must change the User ID= <username> value and the Password =<strong password> value to the correct values before you run this code. Make sure that User ID has the appropriate permissions to perform this operation on the database.

     Dim cn As New ADODB.Connection
     Dim cmdPrep1 As New ADODB.Command
     Dim rs As New ADODB.Recordset
     Dim prm1 As New ADODB.Parameter
     Dim strCn As String
   
     ' Connection string to a SQL Server 7.0.
     strCn = "Provider=SQLOLEDB.1;integrated Security=SSPI;Persist Security Info=False;Initial Catalog=pubs;Data Source=mySQL7"
   
     ' Connection string to a SQL Server 2000.
     ' strCn = "Provider=SQLOLEDB.1;User ID=<user name>;Password=<strong password>;Initial Catalog=pubs;Data Source=mySQL2000"
   
     cn.ConnectionString = strCn
     cn.Open strCn
     cmdPrep1.ActiveConnection = cn
     cmdPrep1.CommandType = adCmdText
     cmdPrep1.Prepared = True
     cmdPrep1.CommandText = "SELECT * FROM authors WHERE zip LIKE ? ORDER BY au_id"
   
     Set prm1 = cmdPrep1.CreateParameter(, adChar, adParamInput, 2, "9%")
   
     ' Set this property to repro against SQL Server 2000. By default, this is TRUE.
     ' cmdPrep1.Properties("Defer Prepare") = False
   
     cmdPrep1.Parameters.Append prm1
     rs.CursorLocation = adUseClient
     rs.CursorType = adOpenStatic
     rs.Open cmdPrep1
     MsgBox "Number rows = " & rs.RecordCount
     rs.Close
     cn.Close
   					

4. Save the project and then run it. Although you would expect to see a record count of 16, no rows are obtained.

Back to the top