Help with Case Sensitive searches

[JimSchuuz]

What my problem is: The text records in my dBASE DB are case sensitive, and I can't change that. Therefore I have tried to make sure all data is in UPPER CASE for uniformity of queries. Customers use an .ASP page to check for pertinent records, but they aren't returned non-casematching lookups. I am looking for a way to submit a SQL query via an .ASP page that will force the search string submitted to uppercase -OR- will return all matching records regardless of the case (ie. search string = "JOHN DOE" returns "John Doe" and "JOHN DOE" and "john doe" etc.) The DB search and results are handled by Front Page currently (I'm not great on writing entire scripts myself) but am willing to switch it if necessary.

WHAT I'VE TRIED: I've tried to use the UPPER() and/or LOWER() statements and found that .ASP pages don't support those, and quite a few other SQL commands as well.

WHAT'S AT MY DISPOSAL: A local IIS 5.1 server (what I'm currently using and prefer to use), a remote IIS 5.0 server (complete administrative control, but only when at the console), another remote IIS 5.0 server (only web authoring control), MS-VB6 Pro, MS-VB5 Developer, FP 2003, 2002 and 2000, and Borland Delphi 6. I mention these in case someone says a certain method is better than another.

The DB is local to the webserver, to which I have Admin access. I also have read/write access to the DB, but have not found a way to simply make a "case change" to every record. Oh yeah, and my exact SQL query is below:

SELECT Ord.REFERENCE,
Ord.CUSTPO,
Ord.TRANSDATE,
Ord.DUEDATE,
Ord.SHIPVIA,
Cust.EMAIL,
Cust.FIRSTNAME,
Cust.LASTNAME,
Cust.ZIP,
Ord.NOTES
FROM Ord
INNER JOIN Cust
ON Ord.CUSTNUM = Cust.CUSTNUM
WHERE (EMAIL =  '::EMAIL::')

Jim Schuuz
{ F1 = my.friend
}

 

[Chopstik]

In other words, if the user enters "john doe" and the DB is case sensitive and everything is upper case, it should convert the lower case "john doe" to "JOHN DOE" before doing the search, yes?

Have you tried UCASE in ASP?

------------------------------------------------------------------------------------------------------------------------
"I am not young enough to know everything."
Oscar Wilde (1854-1900)

 

[JimSchuuz]

Yes, that's one of the possible solutions I'm willing to use. About 95% of the data was entered in upper case for continuity, but some human involvement has obviously led to mistakes. (Not mine LOL)

I have not tried UCASE. I will get the syntax for it and try it shortly, and post the results back here.

If anyone has the other solution about how to make a search for variable-case-but-otherwise-identical-matches I'd also like to try that.

I forgot to mention that I have searched this forum and the VB 5&6 forum for related threads to no avail. (Someone else I called said it might be able to make either a .DLL or Active-X control to do it, but no suggestions how to do that yet.)

Jim Schuuz
{ F1 = my.friend
}

 

[JimSchuuz]

OK, I tried 2 different uses of UCASE and the webserver crashed both times. If anyone can give me an example on how to apply it to the following (abbreviated but working) code I would appreciate it!

SELECT Ord.REFERENCE,
Cust.EMAIL,
FROM Ord
INNER JOIN Cust
ON Ord.CUSTNUM = Cust.CUSTNUM
WHERE (EMAIL =  '::EMAIL::')

I am deliberately leaving the join in there just in case it impacts the UCASE reference.

Jim Schuuz
{ F1 = my.friend
}

 

[Dashley]

Jim,

A basic use of the UCASE is like:

dim mystr 
mystr ="lalalala"
mystr = ucase(mystr)
Response.Write mystr

What DB are you using

 

[Chopstik]

Using the short query you have above, try this:

strEmail = [COLOR=green]'value I want to search by[/color]
strSQL = "SELECT Ord.REFERENCE, Cust.EMAIL, FROM Ord INNER JOIN Cust ON Ord.CUSTNUM = Cust.CUSTNUM WHERE (EMAIL = '" & UCase(strEmail) & "')"

------------------------------------------------------------------------------------------------------------------------
"I am not young enough to know everything."
Oscar Wilde (1854-1900)

 

[Sheco]

Is it possible for you to update the database to fix the records entered by humans so that every record is uppercase? .... or will that have unwanted side effects?

 

[Chopstik]

Good catch, Sheco. I somehow missed that some records in the DB may not be in upper case... That would certainly throw a wrench in things...

------------------------------------------------------------------------------------------------------------------------
"I am not young enough to know everything."
Oscar Wilde (1854-1900)

 

[JimSchuuz]

UCASE hasn't worked either.

Once I solve the problem with the search finding all upper and lowercase records, I will spend time looking for a utility that will convert any lowercase letters to upper in the DB.

It is a dBASE database with the BDE.

Other things to note: I have complete control and authority over the GLOBAL.ASA file if someone thinks I can run (insert) something in there.

I'm still stumped.

Jim Schuuz
{ F1 = my.friend
}

 

[Tarwn]

Hmm, the cheapest solution I can think of is to force strings to upper case on both sides, ie use the ASP UCase function to upper case the value you want to match on, then in your SQL String use the UPPER function on the field your comparing to. This will force both fields to upper case for your comparison, regardless of what they were actually stored as. So something like:

strSQL = "SELECT Ord.REFERENCE, Ord.CUSTPO, Ord.TRANSDATE, Ord.DUEDATE, Ord.SHIPVIA, Cust.EMAIL, Cust.FIRSTNAME, Cust.LASTNAME, Cust.ZIP, Ord.NOTES " & _
   "FROM Ord INNER JOIN Cust ON Ord.CUSTNUM = Cust.CUSTNUM " & _
   "WHERE (UPPER(EMAIL) =  '" & UCase(yourVariable) & "')"

Or you could just as easily do both on the server-side, like so:

strSQL = "SELECT Ord.REFERENCE, Ord.CUSTPO, Ord.TRANSDATE, Ord.DUEDATE, Ord.SHIPVIA, Cust.EMAIL, Cust.FIRSTNAME, Cust.LASTNAME, Cust.ZIP, Ord.NOTES " & _
   "FROM Ord INNER JOIN Cust ON Ord.CUSTNUM = Cust.CUSTNUM " & _
   "WHERE (UPPER(EMAIL) =  UPPER('" & yourVariable & "'))"

Either way this should negate any issues you have with case-sensitivity without altering the actual contents of the field or content being returned.

-T

 

[Genimuse]

The short version of what Tarwn said (since it's what came to my mind when I read the OP, I figured I'd post it): Use UCase on the ASP side to make the search string upper case, and use UPPER in the SQL to make the database comparison upper case.

 

[Tarwn]

[ and the part of the synopsis Gen left out ]
Or use UPPER() on both the field name and the value in the comparison

 

[Genimuse]

Right!

:admonished:

 

[JimSchuuz]

I am realizing that everyone's (very helpful) suggestions are meant for use by executing the SQL query directly on the page, which I think is the reason why I haven't had any success in spite of everyone's wonderful assistance thus far. My problem is that I am attempting to use FP's data results feature to execute the custom query for other reasons, and it is clearly either ignoring or hiccupping on the UPPER or UCASE syntax.

Below is the exact code if anyone has a suggestion after seeing it:

<form BOTID="0" METHOD="POST" action="info_orders_mm.asp">
<input type="hidden" name="fpdbr_0_PagingMove" value="  |&lt;  ">
<table BORDER="0">
<tr>
<td><b>EMAIL</b></td>
<td>
<input TYPE="TEXT" NAME="EMAIL" VALUE="<%=Server.HtmlEncode(Request(UCASE("EMAIL")))%>"></td>
</tr>
</table>
<br>
<input TYPE="Submit"><input TYPE="Reset"><!--webbot bot="SaveAsASP" clientside suggestedext="asp" preview=" " startspan --><!--webbot bot="SaveAsASP" endspan --><p>&nbsp;</p>
</form>

<%
fp_sQry="SELECT Ord.REFERENCE, Ord.CUSTPO, Ord.TRANSDATE, Ord.DUEDATE, Ord.SHIPVIA, Cust.EMAIL, Cust.FIRSTNAME, Cust.LASTNAME, Cust.ZIP, Ord.NOTES FROM Ord INNER JOIN Cust ON Ord.CUSTNUM = Cust.CUSTNUM WHERE (EMAIL =  '::EMAIL::')"
fp_sDefault="EMAIL="
fp_sNoRecords="No date set."
fp_sDataConn="OrdCusIt"
fp_iMaxRecords=0
fp_iCommandType=1
fp_iPageSize=2
fp_fTableFormat=False
fp_fMenuFormat=False
fp_sMenuChoice="REFERENCE"
fp_sMenuValue="REFERENCE"
fp_sColTypes="&REFERENCE=200&CUSTPO=200&TRANSDATE=133&DUEDATE=133&SHIPVIA=200&EMAIL=200&FIRSTNAME=200&LASTNAME=200&ZIP=200&NOTES=201&"
fp_iDisplayCols=10
fp_fCustomQuery=True
BOTID=0
fp_iRegion=BOTID
%>

I have also cross-posted this problem to the FP forum in the (unlikely) event someone over there can come up with a solution. If they do, I will follow up with the answer here for future users who have a similar problem.

Jim Schuuz
{ F1 = my.friend
}

 

[JimSchuuz]

I should add for clarity that the "action" page used is the same page I'm starting from. Should I change this so that the entry/search page isn't the .ASP page executing? Would this fix my problem?

Jim Schuuz
{ F1 = my.friend
}