I have a Data Table created in my Schema which gets the records from a stored procedure execution. The stored procedure returns results which were inserted in a Table variable in my procedure’s code. I want to update a couple of fields of that recordset and I wrote a second procedure for that. Then I created a New Command in the Schema Modeler calling the update procedure and assigned it to my Data Table’s UpdateCommandName. When updating, the command is called successfully and the changes are done in DB but I get the following Exception:
“Problem during executing RefreshDataset. Original error was: [SafeCall Exception]: Incorrect syntax near the keyword ‘WHERE’.”
Was it the issue here, should the refresh work correctly?
Looks like RefreshDataset can’t correctly executed. it can be in case you are using custom SQL
some workarounds:
create RefreshDataset sql for this table and assign it the same as you did for UpdateCommandName
set breakpoint at TDABusinessProcessor.RefreshDeltaChange (uDABusinessProcessor.pas) at line with aRefreshDataset.Open; and see in debugger what is wrong.
you can see SQL with aRefreshDataset.GetSQL
Not sure what’s going wrong but I get “Can’t refresh delta, record isn’t found”.
What I did is, I used a TDABusinessProcessor and assigned the ReferencedDataset property with the Dataset I want, then copied this dataset that contains the SQL"EXEC sp_AStoredProc :Param1, :Param2" and assigned this new dataset to the RefreshDatasetName property of the TDABusinessProcessor.
What else do I need to check? Or is it a bug of my version of DA.
RefreshDataset is usually are needed only if server-side changes some fields after inserting/updating like autoinc and it accepts fields as parameters.
in your case, seems it is impossible because you are using SP.
You can disable this feature for specific table: unset poAutoGenerateRefreshDataset in ProcessorOptions and set empty RefreshDatasetName.