?
Solved

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

Posted on 2009-04-16
12
Medium Priority
?
1,476 Views
Last Modified: 2013-11-23
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!
0
Comment
Question by:TTRMW
  • 7
  • 4
12 Comments
 
LVL 18

Expert Comment

by:Johnjces
ID: 24164138
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
 

Author Comment

by:TTRMW
ID: 24164186
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
 
LVL 18

Accepted Solution

by:
Johnjces earned 1000 total points
ID: 24164223
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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
LVL 18

Expert Comment

by:Johnjces
ID: 24164871
Did you have any luck?

John
0
 
LVL 38

Expert Comment

by:Geert Gruwez
ID: 24165023
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
 

Author Comment

by:TTRMW
ID: 24167043
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
 
LVL 18

Expert Comment

by:Johnjces
ID: 24167919
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
 

Author Comment

by:TTRMW
ID: 24168112
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
 
LVL 18

Expert Comment

by:Johnjces
ID: 24168202
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
 
LVL 18

Expert Comment

by:Johnjces
ID: 24168211
I cannot type today!

Open the record then delete it.

Might be spelled better!

John
0
 

Author Comment

by:TTRMW
ID: 24168252
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
 
LVL 18

Expert Comment

by:Johnjces
ID: 24168292
 StockQuery.Open;
  ADOStock.Delete;
  StockQuery.Close;  // If you have to close it.

John
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you need a simple but flexible process for maintaining an audit trail of who created, edited, or deleted data from a table, or multiple tables, and you can do all of your work from within a form, this simple Audit Log will work for you.
This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…
Suggested Courses

807 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question