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!
TTRMWAsked:
Who is Participating?
 
JohnjcesConnect With a Mentor Commented:
First, change you table to a query. In the Object Inspector > SQL add in there Select * from YourTable  and of course YourTable is your table's name.

Then in your code, and this is how I do it in a button click:

 ADOQuery1.Close;
 ADOQuery1.SQL.Clear;
 ADOQuery1.SQL.Add('Select * From YourTable where TheField Like :SrchStr');
 ADOQuery1.Parameters[0].Value := '%'+ Edit1.Text + '%';
 ADOQuery1.Open;

Using this will give you anything like Antony, Tony etc.

When you're done, and you want to go back to 'normal' simply do:

 ADOQuery1.Close;
 ADOQuery1.SQL.Clear;
 ADOQuery1.SQL.Add('Select * From YourTable ';
 ADOQuery1.Open;


'TheField' will be your fields name within the table (Query) that you are searching on.

Experiment with it and see if it works for you and fits your needs.

John
0
 
JohnjcesCommented:
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
0
 
TTRMWAuthor Commented:
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
0
Cloud Class® Course: MCSA MCSE Windows Server 2012

This course teaches how to install and configure Windows Server 2012 R2.  It is the first step on your path to becoming a Microsoft Certified Solutions Expert (MCSE).

 
JohnjcesCommented:
Did you have any luck?

John
0
 
Geert GOracle dbaCommented:
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
0
 
TTRMWAuthor Commented:
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
0
 
JohnjcesCommented:
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
0
 
TTRMWAuthor Commented:
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.
0
 
JohnjcesCommented:
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

0
 
JohnjcesCommented:
I cannot type today!

Open the record then delete it.

Might be spelled better!

John
0
 
TTRMWAuthor Commented:
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?
0
 
JohnjcesCommented:
 StockQuery.Open;
  ADOStock.Delete;
  StockQuery.Close;  // If you have to close it.

John
0
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.

All Courses

From novice to tech pro — start learning today.