glentek
asked on
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.
(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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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.
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
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
ASKER
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.
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