TADOConnection

I'm connecting to Sql 2000 using TADOConnection and TADOQuery with Delphi 7. How can I avoid my TADOQuery to return all the row existing in the table and force it to return only the number of row visible within my TDBGrid?
LVL 2
phadarAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

dygjCommented:
Set adoquery.Maxrecords to the numbers of visible rows.

adoquery.Maxrecords:=mydbgrid.rowcount;

or you can redo your select statement to:
 select top n from yourtable
(n= the number of records you want to have returned. )

pcsentinelCommented:
agree with dygj
phadarAuthor Commented:
Thanks, I've tried that one already, the problem is that I get only the first 10 records, what I meant is that I like to get the number of records according to my dbgrid rowcount and keep scrolling through the entire table, like it is done automatically when connecting to Informix or to Interbase – every time you hit pgdn it retrieves the next 10 records and so on

C++ 11 Fundamentals

This course will introduce you to C++ 11 and teach you about syntax fundamentals.

kretzschmarCommented:
?? why do you need this (i see no need)

usually not all records are fetched in one time,
only this amount is fetched, which fits into the internal buffer (ado)

meikl ;-)
kretzschmarCommented:
btw. the cursorlocation-property should be clServer (had just forget this ;-)
dygjCommented:

ok thats a different story, try this:



set
ExecuteOptions  to eoAsyncFetch       
and set  cachesize to the number of records you want to fetch first, (the remaining will be fetched then you scroll down)

JohnjcesCommented:
If you have a large table in MSSQL Server using ADO's default settings, you will receive all records locally taign a loooong time to "get things going". Once all the records for your query have been retrieved, you can of course scroll through them via a datagrid quickly. But, that may not be the way to do it and as I understand it you only want the scroll to fetch records as you scroll through the data grid.

In order to keep all that data off your client and use just a bit of it, you need to set these properties:


ADOConnection1.CursorLocation := clUseServer;
ADOQuery.CursorLocation := clUseServer;

By setting these properties to use the server all work is performed there. As you scroll in a dbgrid it will continually fetch what is needed. Sometimes some reporting software does not work well this way and you need to set the datasets, (querie,s etc) to useclient and make sure you have indexes set and esp a primary index.

Hope this helps. Worked for me and many occasions.
phadarAuthor Commented:
Thanks for your reply, I tried it but when I set the property in the ADOConnection I get the following error:
"Dataset does not support bookmarks, which are required for multi-record data controls."
On the other hand, setting the property on the ADOQuery does not have any effect.

I could get some kind of results though by changing the ExecuteOptions to eoAsyncFetchNonBlocking but I cannot close the query until the all data is fetched in - very wierd, I can see all the records in the DBGrid but get an error "Object was open" if I try to close it before its time.      
JohnjcesCommented:
Well, make sure you have a primary index set and any other secondary indexes. Check your CursorType!

Should work!

John
dygjCommented:
A forward only coursor will give you the "Dataset does not support bookmarks, which are required for multi-record data controls"  error.

Change cursor type to keyset
ziolkoCommented:
 ADODataSet1.Close;
  ADODataSet1.CommandText:=Memo2.Lines.Text;
  ADODataSet1.Open;
  ADODataSet1.Recordset.PageSize:=10;
  ADODataSet1.Recordset.AbsolutePage:=FPos;

where FPos is "page number", instead ADODataSet You can use ADOQuery :-)

ziolko.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Pierre CorneliusCommented:
No comment has been added to this question in more than 21 days, so it is now classified as abandoned.
I will leave the following recommendation for this question in the Cleanup topic area:
- Accept Ziolko

Any objections should be posted here in the next 4 days. After that time, the question will be closed.

Pierre
EE Cleanup Volunteer
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Delphi

From novice to tech pro — start learning today.