COMMIT TRANSACTION does not work?

[robleer]

Hi,

I trying to implement the Reorder functionality of a Query and made a process to store the new ordervalues in the database like this:

IF ThatCBTQuestion.OrderNr > ThisCBTQuestion.OrderNr then
ThatCBTQuestion.OrderNr = ThisCBTQuestion.OrderNr -1
else
IF ThatCBTQuestion.OrderNr < ThisCBTQuestion.OrderNr then
ThatCBTQuestion.OrderNr = ThisCBTQuestion.OrderNr +1

This works fine, but immediately after this, I have to recalculate the rest of the table in a certain order.
This is not possible via AwareIM because AIM does not follow the right order when updating.

So, I wrote a stored procedure in my database what does this job.

When I perform the processes one-by-one it works fine (first Reorder the records in my grid, then call the stored procedure with a separate process)

But when I call the stored procedure in the same proces (directly after performing the reordering action), I found out that the data is not commited to the database yet and my stored procedure gives the wrong results (it works with the old data).

So to fix this, I've added a 'COMMIT TRANSACTION' action before I call the stored procedure so the stored procedure works with the actual, commited data, but this does not work and my stored procedure still give the wrong results. I did it like this:

IF ThatCBTQuestion.OrderNr > ThisCBTQuestion.OrderNr then
ThatCBTQuestion.OrderNr = ThisCBTQuestion.OrderNr -1
else
IF ThatCBTQuestion.OrderNr < ThisCBTQuestion.OrderNr then
ThatCBTQuestion.OrderNr = ThisCBTQuestion.OrderNr +1

COMMIT TRANSACTION

EXEC_SP 'UpdateOrderNr'

But it looks like the COMMIT TRANSACTION is doing nothing?

I'm really pulling my hair out to get this reordering functionality working and I hope somebody can help me with this.

Tia, Rob

[Jaymer]

I have done this exact thing.
Along with the stored procedure to re-order/renumber the lines.
Will check.

[BLOMASKY]

I use a lot of stored procedures and use COMMIT TRANSACTION a lot. It seems to work fine for me. You do know, after you commit, and then call a stored procedure, you have to RE-READ the record to get any changes.

Bruce