Solved

Refresh Query

Posted on 1997-11-23
7
308 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!
0
Comment
Question by:lizzzard
  • 4
  • 3
7 Comments
 
LVL 5

Accepted Solution

by:
julio011597 earned 40 total points
ID: 1351536
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
0
 

Author Comment

by:lizzzard
ID: 1351537
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?
0
 
LVL 5

Expert Comment

by:julio011597
ID: 1351538
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
0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 

Author Comment

by:lizzzard
ID: 1351539
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.....
0
 

Author Comment

by:lizzzard
ID: 1351540
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?
0
 
LVL 5

Expert Comment

by:julio011597
ID: 1351541
> 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.
0
 
LVL 5

Expert Comment

by:julio011597
ID: 1351542
> 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
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Suggested Solutions

Introduction The parallel port is a very commonly known port, it was widely used to connect a printer to the PC, if you look at the back of your computer, for those who don't have newer computers, there will be a port with 25 pins and a small print…
Hello everybody This Article will show you how to validate number with TEdit control, What's the TEdit control? TEdit is a standard Windows edit control on a form, it allows to user to write, read and copy/paste single line of text. Usua…
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, Just open a new email message.  In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…
Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.

758 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

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now