Board index » delphi » re-executing the sql-statement

re-executing the sql-statement

I am using D3.02, BDE 4.51 (native MSACCESS driver) and a MS Access 97
database.

The database is accessed through a query.
In one of the tables, I have to insert records.
The first field of the table is a number called 'Position'.
The SQL statement orders by this field:
"SELECT * FROM POSITIONS ORDER BY POSITION"
(Positions is the name of the table, which holds 12 fields.)
When I insert a record at a certain position, a routine increases the
numbers in this position-field for the records that have a number
higher than the number of the selected record. Than the inserted
record gets the missing number.
So far so good.
But when I refresh the query it doesn't re-execute the sql-statement,
which results in a non-ordered result-set.

For example:
This is before:
1
2
3
4

then I insrert a record with record 2 selected, and this happens:
1
2
4
5
3

And this is what should happen:
1
2
3
4
5

The only way I know to re-order the mess, is to close and open the
query. But this is too slow.

Is there another way to do this ?
Please help ...

Adrian.

 

Re:re-executing the sql-statement


The only way to re-execute a query is to re-open the query.

With the access driver, an insert with refresh go wrong,
because the driver uses the RESULT SET from your old
query to insert. (The really insert is also done into your DB).

If you use a TTable, try the Append or AppendRecord method.
(not Insert method)

Skalar Development Team schrieb in Nachricht
<35c072da.271160...@forums.borland.com>...

Quote
>I am using D3.02, BDE 4.51 (native MSACCESS driver) and a MS Access 97
>database.

>The database is accessed through a query.
>In one of the tables, I have to insert records.
>The first field of the table is a number called 'Position'.
>The SQL statement orders by this field:
>"SELECT * FROM POSITIONS ORDER BY POSITION"
>(Positions is the name of the table, which holds 12 fields.)
>When I insert a record at a certain position, a routine increases the
>numbers in this position-field for the records that have a number
>higher than the number of the selected record. Than the inserted
>record gets the missing number.
>So far so good.
>But when I refresh the query it doesn't re-execute the sql-statement,
>which results in a non-ordered result-set.

>For example:
>This is before:
>1
>2
>3
>4

>then I insrert a record with record 2 selected, and this happens:
>1
>2
>4
>5
>3

>And this is what should happen:
>1
>2
>3
>4
>5

>The only way I know to re-order the mess, is to close and open the
>query. But this is too slow.

>Is there another way to do this ?
>Please help ...

>Adrian.

Other Threads