How to make Query.append and getting last position in dbgrid ?

Hi again,  Experts !
I believe, I am moved... I have a query and a dbgrid. Appending records to query results in records at the end of dbgrid - that's ok - but if I delete an older record, I cannot append a new one to the end of dbgrid anymore. The new record appears alwas near the deleted entry. Is there any way, to go around this 'filling of gaps' ? Thanks, Brainwashed2
Brainwashed2Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

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

kretzschmarCommented:
i would guess the deleted record is reused

what database?

meikl ;-)
Brainwashed2Author Commented:
Hi Meikl,
I use Advantage DS with TAdsQuery, TAdsTable  and TMS TAdvDBGrid.
The Table contains adressdata and adress-number, the Query contains
individual informations with referring numbers to the adresses and a unique,
autoinc key (MyKey).

The Query's SQL is 'Select * FROM Table WHERE AdrNr =: RefNr ORDER BY MyKey '.

regards,
tom :-))
   
RickJCommented:
What version of Advantage DS?
OWASP: Threats Fundamentals

Learn the top ten threats that are present in modern web-application development and how to protect your business from them.

Brainwashed2Author Commented:
Version 7.10
RickJCommented:
I did not think ADS did this.
I have the exact components you use. (Tads and TMS)
If you post your code I will try and reproduce...
VoodoomanCommented:
Hi

After deleting a record open and close the recordset.  Unless the table is huge there is little overhead in this.

Voodooman
RickJCommented:
Hello Voodooman,
Why the need to do this?
Are you thinking (or know) that deletes are not flushed/removed automatically?
Just curious as I have never had this problem.
VoodoomanCommented:
Hi Rickj

In my experience a database never bothers trying to 'fill in the gaps' in a table.  It would be illogical for the database to maintain markers to deleted records or to scan for them every time you want to add a record.

Brainwashed 2 says<<Appending records to query results in records at the end of dbgrid - that's ok - but if I delete an older record, I cannot append a new one to the end of dbgrid anymore.>>

If Brainwashed2 wants to overcome this behaviour and the table is not too big he could logically renew the query after delete in which case as he says <<Appending records to query results in records at the end of dbgrid - that's ok>>

As in general situations adding recors is common and deleting them is rare, it would be no big deal to refresh the recordset to achieve what Bwsd2 wants.

I would expect this is a problem with the grid rather than the recset.

regards

Voodooman
Brainwashed2Author Commented:
Dear RickJ, Dear Voodoman,

I have tried, to make a new and very simple app. with the concerned components and - you have no idea -
with this little program I can delete records and append new ones at the end of my grid without any problem.

I 've also tried, to open and close the recordset after deleting, but this was resulting in an error from
dbgrid - so I think you are right with your supposition, that's a problem with the grid, voodoman...

Tomorrow, when I'll be back in office, I will post more information !

Have a nice sunday evening,
Tom
VoodoomanCommented:
Hi

I have found some of the TMS stuff to be a bit buggy.  Try another grid just for fun.  I use EHGrid from http://www.ehlib.com/ myself which I think is very good.

You can also set the datasurce of the grid to nil before opening and closing the dataset and can then set the grid datasource to the new recset after its open - should solve your problem.

Lots of controls do not behave as expected unfortunately.

Voodooman
RickJCommented:
I agree that it is probably the grid.
As to TMS being sometimes buggy, I definitely agree.
I dont use TMS DBGrid much, but I cant reproduce your problem at all.
Maybe our versions are different? I have version 2.0.1.1
I also notice two properties that might be worth a try.

RefreshOnDelete
RefreshOnInsert

The pretty much do as it suggests. Maybe set them to true and try again.
I have also had instances where actually deleting the grid from the project, then dropping a new one on the form has fixed problems.

I mainly use InfoPower Components from Woll2Woll. The wwDBGrid is very nice.
Brainwashed2Author Commented:
Hi together,
now I've tried many changes within the properties - RefreshOnDelete, RefreshOnInsert, SortSettings, NavigationSettings a.s.o.
The result: Always the same.

My newest information for you: When I run my application, append some records and then delete again (i.e.) three older ones, then I close my app. and stop
the server-service. After restarting all, when I try to append new records to a new grid, the first record is at 1st row of grid - ok, not posted still now - but the
next three records appear before this first one and only the 4th one and the following are correct appended again.

When I have a look to the table-content with DataArchitect, it seems, the ADS is filling gaps after all - the gaps of missing AutoInc Values from the deleted records!

regards, tom
RickJCommented:
I accept filling of gaps.
Do you use ADT or DBF tables?
If DBF there is a property on the TadsSettings component, something like ShowDeleted.
It stops the server returning deleted records.
Set this and see if it makes a difference.
Still cant see why I cant reproduce it..!!!!  (I use ADT, DBF is different I suppose)
I will try again in the morning...

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
Brainwashed2Author Commented:
Thank you very, very much for your time !!!!
Sorry, but I think I'm in need of a holiday. So much time for such a silly solution :-((
I couldn't see the nearest way: AdsQuery.IndexFieldNames + AdsQuery.IndexDefs - PUHHH

For your patience I only can split points and say THSANK YOU again.

Tom
RickJCommented:
Glad you solved your problem!
I just wish I knew why I cant reproduce what you have.
So frustrating...
Brainwashed2Author Commented:
Don't worry - just take a table with 2 indexes, set a query with IndexFieldName No. One (adress number) and
run a Statement, ordering by IndexFieldName No. Two (AutoInc). Then, when u delete an older record and try
to append a new one,  the query first 'fills gaps' of the consecutively numbered table-entries concerning to the
adress number  ....  
Now be happy, like me !
 
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.