Refresh Query

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!
Who is Participating?
julio011597Connect With a Mentor Commented:
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).

lizzzardAuthor 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.

Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

lizzzardAuthor 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.....
lizzzardAuthor Commented:
I do have an additional question/comment:
I could go for the query.close/ 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,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.


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.

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.