Index a query?

[lespaul]

I have a process that I previously created local tables for and am now trying to update this process to use queries so it reflects changes in real time. When the user entered in the criteria (either lastname or ssn), I would alternate between two previously created indexes and use the FindNearest in the table. However, since I want to use the query instead, I've been looking into the AddIndex and NameIndex, but can't quite get it to work!!

Can someone show me an example of how to create an index on the fly for a query? Or another way to search a query and have the result be found and displayed in my DBGrid (I'm currently moving to that row and changing it's color).

Thanks,

Leslie

 

[BillDoorNZ]

Leslie,

any chance of some more info? what components are you using (you're obviously using a DBGrid...but whats it hooked to??).

When you say you created local tables - do you mean you created a local TTable? or something else? Where do you get the data to populate the local table with as well?

Why 2 tables? Are your SSN and LastName stored in different tables?

Why not use a TQuery, then when you want to find records like the ones the user specifies, use the TQuery.Filter property to add something like:

TQuery.Filter := Format('SSN like ''%%%s%%'' or LastName like ''%%%s%%''', [ssn, lastname]);
TQuery.Filtered := True;

 

[lespaul]

I'm trying to get rid of the tables and just use queries. I have a Tquery (qrySearch), a TDataSource (dsSearch), a DBGrid with dsSearch as the DataSource for the grid. I have two TEdit boxes (edLastName and edSSN). When the user enters a last name, I'd like to move to the appropriate record (the first SMITH that's found) or if they enter a SSN, move to the matching SSN.

I've tried using the DataSet.Locate and DataSet.Lookup, but get "Capability not supported" or "Operation not supported" or access errors.

I've not had luck in the past getting the filter options to work either. Will filters work for navigating within the grid?

Thanks for your reply!

leslie

 

[BillDoorNZ]

unfortunately, filters will not do what you want...I'm not sure about adding indexes to TQueries...you might have to end up writing the search funcationality yourself!

e.g.

procedure FindBySSN(SSN: string);
var
Found: boolean;
begin
SSN := UpperCase(SSN);
DataSet.DisableControls;
try
Found := False;
DataSet.First; //may want to search from current pos tho....you can work it out
while (not DataSet.EOF) do
begin
if (Pos(SSN, UpperCase(DataSet.FieldByName('SSN').AsString)) > 0) then
begin
Found := True;
break;
end;
DataSet.Next;
end;
if (not Found) then
begin
First;
ShowMessage('Could not locate any matching SSN');
end;
finally
end;
end;

 

[Svanhooft]

Hi,

Queries donn't have indexes.

Select * from Table
where ssn = variable
order by ssn

Will return a table sorted by ssn and all the records with ssn equal to the variable. Variable could be an edit1.text.

hope this helps

Steph [Bigglasses]

 

[lespaul]

Thanks Steph, but I already have the query ordered properly, but this process is mianly used to verify if a person has served as a juror within the last 3 years (if so then they are eligible for an excusal) so we get calls from the different courts in the state for this verification. I return about 30,000 records from a union query and display them in the DBGrid. The users (rightfully) don't want to scroll through 30,000 records to find Joe Smith. I want to let them enter SMITH in the last name box and have the DBGrid move to the record that first matches or if they have the person's SSN, move to the first match.

Thanks Bill, I'll give that a try!

But I'd still like to figure out why the 'Locate' and 'Lookup' functions don't work on the dataset! If someone has an example of how to use these properly, I'd appreciate it!

Thanks,
Leslie

 

[richardchaven]

Why not just put their criteria into the query and show them just the Smith's? In other words, don't even get the 30,000 rows in the first place: the odds of the row(s) they want being in the first 50 (which is about as much as users want to see) is low.

Cheers

 

[Svanhooft]

hi,

Why use locate is you can do the same with a query:

Select * from table
where name = '%SMITH%'
order by

will give alle the people with SMITH in the name.

Steph [Bigglasses]