asked on

Wildcard filtering a DBGrid in Delphi - ADO/Access..?

Hi there, I asked a similar question a while ago, got a solution but in the end couldn't make it work.. Gave up and did something else, but it was a half arsed way of doing things, and this is how I want it to work, so..

I'm trying to do a wildcard filter on the contents of a DBGrid populated by an adotable component, which is linked to an access database. I want to do the filter at runtime, on an onclick event tied to the contents of an editbox, so filter = edtfilter.text or whatever. I want the results to be displayed on the DBGrid of course. Can't for the life of me figure out how to get this to work, when I tried last time, the onclick would result in the dbgrid being populated with no records at all, bit strange. Oh and I need it to compare on partials aswell so antony would come up with tony entered etc...

Cheers in advance!

Johnjces

Why must you use a filter?

Can we not use an SQL Query? Change the ADOTable to an ADOQuery, and for normal stuff use Select * from YourTable.

With a query, you can get your results easily. Just let us know if there is some reason that you must use a db filter.

John

TTRMW

ASKER

True, could use a query, not 100% on the syntax, though. Normal stuff will be select * from... Then at runtime how would I change it? I seem to remember that you can change query.sql at runtime? Again syntax I'm not sure on, and it'd be select * from tablename where ..... what? How do I pass the text from the edtbox into the sql query?

This sort of thing is why I was looking more to go for the filter option - I'm pretty crap with SQL! :D

ASKER CERTIFIED SOLUTION

Johnjces

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Johnjces

Did you have any luck?

John

Geert G

if you don't want to code everything yourself, you could look at
http://devexpress.com/Products/VCL/ExQuantumGrid/

in there, you set the filtered row visible. it's like the filter in access

TTRMW

ASKER

Bravo, the SQL worked a treat, does exactly as I need it to.

However I'm now having some trouble with appending. I didn't want to use data aware editboxes, rather have it append onclick. Originally I tried:

ADOStock.Edit;
ADOStock.Append;
ADOStock.FieldValues['ProductName']:=EdtStockProductName.Text;

However this would only update the DBGrid after clicking the button to run the SQL search (with no data in the related editbox).

So I tried:

StockQuery.Close;
ADOStock.Edit;
ADOStock.Append;
ADOStock.FieldValues['ProductName']:=EdtStockProductName.Text;
ADOStock.FieldValues['ProductCost']:=EdtStockProductCost.Text;
ADOStock.FieldValues['QuantityInStock']:=EdtStockQuantityInStock.Text;
StockQuery.Open;

Which only works if you click the append button twice..

There must be a solution to this glitchiness!

Also deleting records seems to present a problem - clicking on the dbgrid does not set the selected (clicked) record to current, so it will only delete the BoF record or EoF following the addition of a new record..

Here is the code I used for deleting:

StockQuery.Close;
ADOStock.Delete;
StockQuery.Open;

As clarification, ADOStock refers to the ADOTable component, StockQuery ofc is the query of success and win.

Cheers again :D

Johnjces

I guess I don't understand.... This new code is in the same OnClick button event?

AND, if you are appending, addining or inserting, you gott use Post!

ADOStock.Edit;
ADOStock.Append;
ADOStock.FieldValues['ProductName']:=EdtStockProductName.Text;
ADOStock.Post

John

Also, just FYI, this is kind of a new Q... an off shoot of the original.

John

TTRMW

ASKER

Hi there - nah, different OnClick, sorry if that wasn't clear.

Oops, done that, appending now works fine, cheers.

Will post a different Q. about deleting then, thought it was best to not clog up with questions that related to one another.

Johnjces

Sorry, I missed the delete one...

You have to have the record OPEN the Delete it!

Do not Close, Dleete Open that just will not work!

So Open Delete and only close it if you have to.

John

Johnjces

I cannot type today!

Open the record then delete it.

Might be spelled better!

John

TTRMW

ASKER

Crap, already added a new question after what you said - reply and points will be awarded, haha.

When you say open the record, I presume you don't mean open the dataset (so stockquery.open..)

How do I open the record?

Johnjces

StockQuery.Open;
ADOStock.Delete;
StockQuery.Close; // If you have to close it.

John