Using UpdateCommandName for a Data Table

(chrisroyal) #1


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?

Thank you,


(EvgenyK) #2

pls specify your platform (.NET. Delphi, etc)

(chrisroyal) #3

Sorry, I forgot to mention them. It’s Delphi 2007, SQLServer and DA

(EvgenyK) #4

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

(chrisroyal) #5

Where do I assign the RefreshDataset sql? Can’t find it in Schema modeler. Is it done by code?

(EvgenyK) #6

drop TDABusinessProcessor to _Impl and set up it’s properties

(chrisroyal) #7

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.


(EvgenyK) #8

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.

(chrisroyal) #9

Thank you, this what I needed. Works like I expect it to now.