Refresh Record - SP Calc Field

Discussion of open issues, suggestions and bugs regarding SDAC (SQL Server Data Access Components) for Delphi, C++Builder, Lazarus (and FPC)
Post Reply
Hooman
Posts: 8
Joined: Wed 30 Jan 2013 18:53

Refresh Record - SP Calc Field

Post by Hooman » Fri 16 Aug 2013 00:20

Hi,

I have a problem using RefreshRecord. I have a calculated field inside my Stored Procedure which will not be updated with RefreshRecord. Here is a sample:

Stored Procedure:

SELECT Amount, Applied, Unapplied = ISNULL(Amount,0) - ISNULL(Applied,0)
FROM MyTable WITH (NOLOCK)
WHERE ...


I call Dataset.Refresh to bring data completely then if data is changed and user select a record and call Dataset.RefreshRecord, Unapplied column will not be updated.

What is the best solution for this?

I also tried to set the FullRefresh option before RefreshRecord but then RefreshRecord generates and error "Incorrect syntax near the keyword 'WHERE'."

Thanks

AndreyZ

Re: Refresh Record - SP Calc Field

Post by AndreyZ » Fri 16 Aug 2013 07:02

Hello,

The Unapplied field is a computed on the server field, its value is computed when you execute the stored procedure. That is why there is no way to generate an SQL statement for this field in order to get its value. The only way to do this, is to re-execute the stored procedure by calling the Refresh method.
Another way to achieve the same functionality, is to remove the Unapplied field from the stored procedure and make it calculated field in TMSStoredProc. In this case, its value will be update when calling the RefreshRecord method.
I also tried to set the FullRefresh option before RefreshRecord but then RefreshRecord generates and error "Incorrect syntax near the keyword 'WHERE'."
Thank you for the information. We have fixed this problem. This fix will be included in the next SDAC build.

Post Reply