We help IT Professionals succeed at work.

Refresh Query

lizzzard
lizzzard asked
on
Medium Priority
354 Views
Last Modified: 2010-04-04
Now I asked this one before,but maybe now someone comes up with another answer. I have a Query on a DBGrid,but it won't refresh after I updated one of its sourcefiles. I have RequestLive set to true. I've refreshed everything I can,but nothing helps. Query.Close followed by Query.Open takes too much time. Is there anything else I can try?
To explain what is going on: I have a query on a grid;the SQL statement involves an 'order by' statement which puts 'CanModify' to False. Since the user must be allowed to set a checkbox to false or true by clicking on it on the grid,I have the table to be updated linked to the query (by setting the table-mastersource to the query),so, by clicking, I update the table and then refresh the query. But ,as stated,the query JUST WON'T refresh (unless I close and open it which is a loosy way to refresh).
In case it is not clear what I mean :

 Query ,involving Table1, linked to Table1 (<-MasterSource = Query)
 
 DBGrid showing Query
 User Clicks DBGrid -> Table1 is updated.
 Refresh Query -> User sees new value(which doesn't work)

 PS I have only 45 points left,if I had more I would give a 100 to solve this one!
Comment
Watch Question

Your ORDER BY clause also sets the RequestLive property to False. This means that you simply _cannot_ refresh the table (look for Refresh Method into the Delphi Help file).

-julio

Not the solution you were looking for? Getting a personalized solution is easy.

Ask the Experts

Author

Commented:
Does anyone have a suggestion then how to solve this problem? I cannot use a table,because I HAVE to filter the records using the SQL IN statement. Can I for example force a query to use a certain index without using an ORDER BY statement?
First, if you need more suggestions, just wait to accept an answer; now your question is open to me only (other experts should pay each time they would access the question).

Back to the question:

i haven't seen your SQL statement, but i guess you can just go with the Filter property of a TTable to do what your IN SQL clause does, and keep your records ordered with an index.

If this does not apply, please show your SQL statement.


-julio

Author

Commented:
Well, I could do it with a filter,but it could turn out to be a gigantic one. The SQL statement is variable and put together once the user has made his/her choices what he/she wishes to see,and in what order.
It could turn out like this
Select * from MTG where Color in ("B","U","G","W","Art") and Edition in ("A","B","WL","CH","TP","RV","VI") order by Color,Card,Edition;

I suppose I could write a filter for this one.....

Author

Commented:
I do have an additional question/comment:
I could go for the query.close/query.open solution. It takes a little longer for the result to show up,but I think I can get away with it,if there is a possibility of setting sort=OFF to enable RequestLive.
I managed to get the good positioning with GotoBookmark after the query.open,but sometimes the row shows up on a different position in the grid. If I can fix this I'm out of trouble! Do you happen to know how I can a specific row to show up on the position it was before the query.close?
> The SQL statement is variable

well, i would have seen it.
And, are you calling the Prepare method for your dynamic query? that makes a query open much much faster.

This said, consider that your main problem is that you need to order the result set on more than one field.

I can see just a few solutions:

1. you forgot to call Prepare, and just have to do that;

2. you are working on dbase tables and can create one or more expression indexes to order the table with; than, each time a user submits a request, you create a filter expression on the fly, and open the right index to order the result set.

3. you give a non-live result set to the user (he/she just need to be able to switch a check box), and keep synchronized and updated the underlying table.

4. similar to 3rd; you give a non-live result set and, when the user has *finished*, you do an update of the underlying table.

Ok, let me know.

-julio

BTW, another reason for not grading too soon an answer is that, sometimes, you find out you should have given more than Cx45 points.
> Do you happen to know how I can a specific row to show up on the position it was before the query.close?

AFAIK, you cannot.
This is because you cannot trust a Bookmark after the data set has been closed - a Bookmark is a reference to an internal BDE table, not a reference to a fisical row.

IMO, giving the user a non-live result set could be the best strategy.

-julio
Access more of Experts Exchange with a free account
Thanks for using Experts Exchange.

Create a free account to continue.

Limited access with a free account allows you to:

  • View three pieces of content (articles, solutions, posts, and videos)
  • Ask the experts questions (counted toward content limit)
  • Customize your dashboard and profile

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.