Move around in DBGrid

[lespaul]

I have a DBGrid that lists thousands of people. I have an edit box and a "Search" button at the top. When the user enters a name in the edit box, I would like to move to where the field matches the edit box. so if the user enters 'SMITH' and presses the button they would see the first 'SMITH'. Is there a way to do this?

Thanks!
Leslie
landrews@metrocourt.state.nm.us

SELECT * FROM USERS WHERE CLUE > 0
No Rows Returned

 

[svanels]

If you are using a TTable it would be simple

procedure TForm1.Edit1Change(Sender: TObject);
begin
if (Edit1.Text <> '') then
try
Table1.FindNearest([Edit1.Text]);
except
On EDatabaseError do;
On EConvertError do;
end;
end;

This example doesn't need the search button

For a TQuery use the Locate function Steven van Els
SAvanEls@cq-link.sr

 

[lespaul]

So I looked at changing to a TTable, except I am displaying data from two tables (UNION query) and that won't work. Here's the scoop: I have to show all the jurors in the database for at least the last 3 years. The old database has a NAME field (lastname, firstname). Then new database has separate fields for first and last. I have a DBGrid which has: LASTNAME, FIRSTNAME, SSN, STATUS. I use the following query to manipulate the data to fit the DBGrid:

SELECT JURNUM, LASTNAME, FIRSTNAME, SSN, STATUSCD,
'JMS' AS TYPE
FROM JMPMAIN WHERE TERMDATE >= :CUTOFF1
UNION
SELECT 0 AS JURNUM, NAME AS LASTNAME, '' AS FIRSTNAME, SSN, 
STATUS AS STATUSCD, 'AS4' AS TYPE
FROM CMLIB/CMPJURY WHERE APPRDAT >= :CUTOFF2
ORDER BY LASTNAME

(and before you ask, yes I have to have all the data showing in the same DBGRid)

So in my LastName column of the DBGrid, some records only have a last name and some have last, first.

I have 2 edit boxes to get a mandatory LastName to search for and an optional FirstName. If they entered both, I want to search first for the last, first combo in the LastName Column. If that's not found then look for the Last name in the LastName Column and if an optional FirstName was entered, find the row where both names match. However, that seems fairly complex when I can't get it to do the simple ones! (see code below!!).

I have tried using the Locate but I get an access violation error. Here is the locate command that give the violation:

procedure TfrmPriorServ.btnSearchFindClick(Sender: TObject);
begin
  JMSData.qrySearch.DataSource.DataSet.Locate('LASTNAME',Uppercase(frmPriorServ.edLastNmCrit.Text),[]);
end;

Any help or suggestions or helpful suggestions would be appreciated! Leslie
landrews@metrocourt.state.nm.us

SELECT * FROM USERS WHERE CLUE > 0
No Rows Returned

 

[lespaul]

Ok, I have been trying to get this to work for days now! Is there no other way to find a specific record in a dataset and move there????

Thanks!
Leslie
landrews@metrocourt.state.nm.us

SELECT * FROM USERS WHERE CLUE > 0
No Rows Returned

 

[svanels]

Finding the data is not a problem, but you stated thousands of people.
Wouldn't it be wiser to mount a special query to call all the &quot;look like SMITH?&quot;

Reasons:
Do you mix upper and lowercase in your Database?
Are the records indexed? So yes on what?
If SMITH1 has his physical location at Record 10 in the database, SMITH2 at 100878 and SMITH3 at 203, the processing time will be greater then if the table contains only 50 entries.

Here is a code snippet, how I query &quot;big&quot; tables
I make use of the LIKE operator

table structure:

| CODE | NAME | LOCATION | GENDER |

Needed

1) a form

2) TQuery named MyQuery

3) TDBGrid or other db aware fields, TDataSource, DataModule etc.

4) TEditBox named edtSelect

5) TComboBox named cbSelect

6) TBitButton named bbtnSelect (to fire the query)

7) Some global variables
SString --> string
RecordsRetrieved --> integer

8) A statusbar (optional)


The Query is a parametrized type.

Properties:

SQL = Select CODE, NAME, LOCATION, TYPE from
MYTABLE MYTABLE
where CODE like :SearchString

Active = false

Parameters --> SearchString --> ftString



As mentioned before, cbSelect will indicate the column searched.
In the sql statement the column is located after 'where'

Items of cbSelect:
CODE
NAME
LOCATION
GENDER


Events:

On form create

procedure TForm1.FormCreate(Sender: TObject);
begin
inherited;
SString :='%%';
cbSelect.ItemIndex := 0;
end;


Typing in the search criteria in the TeditBox

procedure TForm1.edtSelectChange(Sender: TObject);
begin
inherited;
SString := '%' + edtSelect.Text + '%';
end;


Firing the query

procedure TForm1.bbtnSelectClick(Sender: TObject);
begin
inherited;
with Myquery do
begin
close;
SQL.Clear;
SQL.Add('SELECT CODE, NAME, LOCATION, TYPE');
SQL.Add('FROM MYTABLE MYTABLE');

case cbSelect.ItemIndex of
0: begin
SQL.Add('WHERE CODE LIKE :SearchString');
ParamByName('SearchString').AsString := SString;
end;
1: begin
SQL.Add('WHERE NAME LIKE :SearchString');
ParamByName('SearchString').AsString := SString;
end;
2: begin
SQL.Add('WHERE LOCATION LIKE :SearchString');
ParamByName('SearchString').AsString := SString;
end;
3: begin
SString := 'M';
edtSelect.Text:= 'M';
SQL.Add('WHERE GENDER = &quot;M&quot;');
end;
4: begin
SString := 'F';
edtSelect.Text:= 'F';
SQL.Add('WHERE GENDER = &quot;F&quot;');
end;
End; {case}

SQL.Add('ORDER BY CODE, GENDER');
open;

end; { with }

RecordsRetrieved := MyQuery.RecordCount;
sbStatusBar.Panels[2].text:= IntToStr(RecordsRetrieved) + ' Records';
//update statusbar

end;


The user do not have to use wildcards to find for example edward, any character will satisfy the search. Leaving the edtSelect blank, will retrieve all the records.
Steven van Els
SAvanEls@cq-link.sr