VB6 - ADO Data Control and DataGrid - sort order issues with RecordSet.Find

I am using an ADO data control (MSADODC.OCX) as the DataSource of a DataGrid
(MSDATGRD.OCX). Just for this simple example, let's say I have a MS SQL
Server table that contains some names. I initially populate the grid with a
statement like this:

adodc.RecordSource = "select name from my_table order by name"
adodc.Refresh

In this example the names are sorted on the DataGrid as shown here:

APPLEGATE
BISCAYNE
BOGGLE
CAT
CINEMA
O'MALLEY
OAR
OBESE
OREGON
OSHKOSH
OTTER
OZ
PEORIA
PITTSBURGH
POPPY

Here we see that O'MALLEY is first of the O's.  This is correct in
accordance with the ASCII sort order of the characters.  However, if I try
to use find search criteria to move to the first record where name is
greater than or equal to OR

adodc.RecordSet.Find "name >= 'OR'"
adodc.Refresh

It moves to O'MALLEY.  It should move to OREGON.  When doing a Find using a
search string that starts with an O, the search gets "stuck" on O'MALLEY.
Other Find searches work perfectly.  For instance

adodc.RecordSet.Find "name >= 'PI'"
adodc.Refresh

moves to PITTSBURGH.  Can anyone provide a solution to this problem?
Thanks.
glentekAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Marv-inCommented:
it must be the ' char - they always cause problems

does this work

adodc.RecordSet.Find "name LIKE 'OR%'"
adodc.Refresh
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
atheekurrahmanCommented:
hi,

try this. works in access. try the equivalent in ur db

adodc.RecordSet.find "InStr (1, name, '''')<0 and name>='OR'"


remember its four of ' not 2 of "                         

regards
Atheek

0
glentekAuthor Commented:
Marv-in.  Thank you for your response.  The search string (in this case "OR") is provided by the user in a text box. Your suggestion works if the user provides "OR".  However, it would not work if the user entered "OK".  In that case nothing would qualify, and the "current" row of the grid would not change.  If the user entered "OK", I want the first name greater than "OK" to be the current row.
0
atheekurrahmanCommented:
hi,

ok then get the user input into a variable say sInput
then

adodc.RecordSet.find "InStr (1, name, '''')<0 and name>='" & sInput & "'"             // " ' "
                                                                                    ^
                                                                                     |
                                                                                //  ' " 
remember its four of ' not 2 of " in InStr                    

regards
Atheek
0
glentekAuthor Commented:
atheekurrahman.  Thank you for your response.  I tried your suggestion but got a run time error "3001 - Arguments are of the wrong type, are out of acceptable range, or are in conflict with each other".  It doesn't seem to work with SQL Server.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic Classic

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.