insert a new record in the middle of table !

[masterDicanio]

Hi

i want to insert row in the middle of table , then i set the dbgrid current record to 3 (for examp) and then , a button click do : adotable1.insert . then a new record insert abow of active record ! i fill it and press another button that does : adotable1.post ! but it sends the new record to the end table records ! and i wanted to place it there not end of table !

and if it is impossible (realy strange) is it possible to exchange two records in a table ?

i use adoconnection - adotable - datasource - dbgrid - ms sql as database !

 

[Tilen]

Hi

I'm not an expert in Delphi or Databases, but as far as I know inserting records is always done at the botoom of the table.

It's the indexes that are sorting the records. So you just need to have an indexed field, maybe ID oz. Sequence_No and you just put new record in the middle of these numbers.

You just need to use that index when you're getting data from db and you'll get it ordered as you need/want.

To swap records order, you just need to change their indexed fields.

You need to consider uniqueness of index keys and/or duplication errors...

 

[masterDicanio]

Hi

You Are Absolutly Right !
But My Employer Says "The Table Has No Order Field (Index) And I Want To Place Records in The Place i want" , Then i have two ways :

1.find a way to the record where he wants
2.forget the money i wanted to earn !

my table have an auto increament field , and any suggession would be greate !

 

[Tilen]

If this is really small table there is one REALLY REALLY REALLY BAD way...

But if you can't do anything else you could do this:

For example you have records

A
B
C
D
E

and you would to put a new record between B and C you can do this:

1. Insert new record whicih would be F
2. Store all fields from E and F into Delphi's variables,
and swap them, so now at position E is new record and on position F is the old E record ...
3. Do it again now with D and E..
4. Repeat until you get to swap C and D..
5. That's it.

This is for small tables, where the whole process could be quick.

If for any reason you can't store all fields from records into Delphi variables (or you don't want to), you can create a temp table to store record fields, and then delete it when you're done.

But maybe you could persuade your employer to allow you to put a new filed on a table...

 

[svanels]

I Want To Place Records in The Place i want

Suggestion: he should design the Database Engine, probably he have seen something like that in Acces, but doesn't know that the fields are ordered everytime.

Questions
1) How many fields does the database have?
2) Are things ordered alfabetically?
3) How big is the table?
4) Why he wants to put it in the middle of the table?

If you know exactly why, you can use queries to update the table, and use the afterpost event to re-fire the query and display the data in the order needed.
Acces works the same way, if works with system queries all the time (but the user doesn't know)



Steven

 

[Taltoss]

Maybe he THINKS he wants them inserted where he wants, but just put an order No or something field. When I make a sequence field I normally go in blocks of 100 for example

100
200
300
400
500

That way I have 99 spaces in between that I can add to

 

[masterDicanio]

ok !

i knew that it is strange to insert a row in middle of a table and it seems , i was right !

The Table Has 4 Columns and is in a relation with another table (it's id field)

it is a table about metals athat holds the name and description of any metal ! and is in relation with an archive table that holds fees !

i am sure it should have a Logic for ordering ! but my employer doesnt know ! what is the logic and insist for inserting records exactly according to his resources !

i decide to place a numeric field for them ! and they have to make the order as they want !

i didnt found any better way (i dont want to do proccesses on arrays and other types to make a dumb order for such punk man !)

but this topic is open for your comments !

 

[earthandfire]

You could manage the ordering with three fields in the syle of a linked list:

Index, Previous and Next

Index could be an autonumber field, but in any event should be unique.

Previous contains the Index of the record that should be displayed before this one and Next contains the index of the record that should follow this one in display order.

This way records can be added as they should be at the end of the table; you then set the Previous and Next fields accordingly - not forgetting to reset the appropriate fields of the records that must be displayed before and after.

Whilst this is tedious it will allow you to insert any number of records in any position at any time.

Hope this helps.