Re: selecting case sensitive values from MSAccess or SQL Server
- From: Trey Walpole <treypole@xxxxxxx>
- Date: Thu, 22 Dec 2005 11:35:45 -0600
not sure about Access, but with SQL Server you can specify a collation.
the performance will most likely be slower when specifying the collation (as indexes won't come into play), so if you know the collation of the server you're hitting, to improve performance you may want to leave off the collation, if it suits your query.
e.g.
-- using out-of-the-box pubs.authors table as example -- case [and accent] insensitive select * from authors where au_lname='white' collate SQL_Latin1_General_CP1_CI_AI
returns 1 row
-- case [and accent] sensitive select * from authors where au_lname='white' collate SQL_Latin1_General_CP1_CS_AS
returns 0 rows
Joe wrote:
Hi
I have a bit of code which selects data from either a SQL Server or MS Access data base and then creates a report, code example below.
My problem is how do i select distinct case sensitivity to return the proper values that i need.
OR selecting an EXACT value = the exact value in the table
ex: tablefield = "JoHn"
Values in table: John jOhN JOhn joHN
should return 4 distinct values.
******** ******** CODE:
TheVariable = "JoHn"
lcTableName = "SELECT * FROM "+gcSQLString+"thetablename "+; "WHERE fieldname = ?TheVariable "+; "order by otherfield"
STORE SQLCONNECT(gcDataSource, 'dataconnection') TO gnConnHandle1 IF gnConnHandle1 > 0 = SQLSETPROP(gnConnHandle1, 'asynchronous', .F.) = SQLSETPROP(gnConnHandle1, 'packetsize', 6096)
= SQLPREPARE(gnConnHandle1, lcTableName, 'TableName')
SELECT 2 = SQLEXEC(gnConnHandle1, lcTableName, 'TableName')
= SQLDISCONNECT(gnConnHandle1) ******** ********
Any Help would be greatly appreciated
Joe
.
- Prev by Date: Re: Reports problems
- Next by Date: Re: Protection from decompilation
- Previous by thread: Protection from decompilation
- Next by thread: Re: Form With Lots of Tabs is Slow
- Index(es):
Relevant Pages
|
