Microsoft KB Archive/259345

= FIX: Jet 4.0 Treats &quot;#&quot; as Numeric Wildcard Character =

Article ID: 259345

Article Last Modified on 6/5/2006

-

APPLIES TO


 * Microsoft Data Access Components 2.1
 * Microsoft Data Access Components 2.1 Service Pack 2
 * Microsoft Data Access Components 2.1 Service Pack 1
 * Microsoft Data Access Components 2.1 Service Pack 2
 * Microsoft Data Access Components 2.5

-



This article was previously published under Q259345



SYMPTOMS
When you use Microsoft Jet 4.0 and execute a SQL statement similar to the following, Jet 4.0 interprets the hash-mark character (#) as a numeric wildcard instead of a character literal, which causes incorrect results to be returned:

Select * from Table1 where Comments like '#%'

This behavior does not occur with Jet 3.51, which permits records containing the hash-mark character to be returned. In addition, the SQL statement returns the same results when run against both Jet and SQL Server. The Jet 4.0 behavior no longer provides this interoperability.



CAUSE
Parsing changes in the Jet 4.0 expression services incorrectly interpret the &quot;#&quot; character as a numeric wildcard, instead of a character literal.



RESOLUTION
A supported fix is now available from Microsoft, but it is only intended to correct the problem that is described in this article. Apply it only to computers that are experiencing this specific problem. This fix may receive additional testing. Therefore, if you are not severely affected by this problem, Microsoft recommends that you wait for the next Microsoft Data Access Components service pack that contains this hotfix.

To resolve this problem immediately, contact Microsoft Product Support Services to obtain the fix. For a complete list of Microsoft Product Support Services phone numbers and information about support costs, visit the following Microsoft Web site:

http://support.microsoft.com/default.aspx?scid=fh;EN-US;CNTACTMS

NOTE: In special cases, charges that are ordinarily incurred for support calls may be canceled if a Microsoft Support Professional determines that a specific update will resolve your problem. The typical support costs will apply to additional support questions and issues that do not qualify for the specific update in question.

The English version of this fix should have the following file attributes or later:

  Date      Time    Version      Size             File name     Platform --  3/21/00           4.0.3921.0   241,936 bytes    msjtes40.dll

Workaround
Enclosing the hash-mark character in square brackets ([ ]) forces Jet to evaluate it as a character literal. For example:

Select * from Table1 where Comments like '[#]%'



STATUS
Microsoft has confirmed that this is a bug in the Microsoft products that are listed at the beginning of this article.



Steps to Reproduce Behavior
  Copy the following code into a Visual Basic project, add a reference to the Microsoft ActiveX Data Objects library, and compile it. Please note that you may need to change the path to your database file. Note that the message box displays only those records that begin with a numeric character. On Error GoTo Wildcard_err Dim conn As Object Dim rs As Object Dim strResults As String

Set conn = CreateObject(&quot;ADODB.Connection&quot;) Set rs = CreateObject(&quot;ADODB.Recordset&quot;)

conn.Open &quot;Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\northwind.mdb&quot;

conn.Execute &quot;Create table HashmarkTest (Val varchar(10))&quot;,, adExecuteNoRecords conn.Execute &quot;Insert into HashmarkTest values ('1abc')&quot;,, adExecuteNoRecords conn.Execute &quot;Insert into HashmarkTest values ('#abc')&quot;,, adExecuteNoRecords conn.Execute &quot;Insert into HashmarkTest values ('2def')&quot;,, adExecuteNoRecords conn.Execute &quot;Insert into HashmarkTest values ('#def')&quot;,, adExecuteNoRecords

rs.Open &quot;Select * from HashmarkTest where Val like '#%'&quot;, conn 'Comment the line above and uncomment the line below to retrieve correct results. 'rs.Open &quot;Select * from HashmarkTest where Val like '[#]%'&quot;, conn

Do While Not rs.EOF strResults = strResults & rs!Val & vbCr rs.MoveNext Loop

MsgBox strResults

rs.Close conn.Execute &quot;Drop table HashmarkTest&quot;,, adExecuteNoRecords conn.Close Exit Sub

Wildcard_err: MsgBox conn.Errors.Item(0).Description Resume Next 

Additional query words: Pound Percent Number Sign

Keywords: kbbug kbfix kbmdac250fix kbqfe kbmdac210sp2fix kbjet kbhotfixserver KB259345

-

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

© Microsoft Corporation. All rights reserved.