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:

<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("EMAIL"))%>"></td>
</tr>
</table>
<br>
<input TYPE="Submit"><input TYPE="Reset">-->
<!--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
%>

Jim Schuuz
{ F1 = my.friend
}

 

[Lilliabeth]

Sorry to say that I don't have a solution for you, but I can offer this:

You have make a wrong assumption when you said: 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. This is incorrect.

A pass-through query must be written in the dialect of the database to which ASP is passing the query.

I am sorry I am not more help. I would try a programming forum.