ExecSQL(%field.myField) - Failed to evaluate

[LUL]

Hi,

I have hit a problem with Version 7.6 and executing a piece of dynamic SQL. I have created an Admin form that will allow certain users modify field values within particular tables. I have provided a number of drop downs which allow the user to choose the table and field they wish to update. Some fields that are updateable are numeric, some datetime and some text. This is the kind of thing that is generated: update [tablename] set [fieldname] = 'myNewValue' where id = 100. When I run the step I get a Failed to Refresh form error and when I look in Event Viewer I see an exception (Failed to evaluate the following - %ExecSQL(%field.fldSQL).)

I have "escaped" the strings so that when I grab the value of %field.fldSQL and paste it into a SQL window and run it, it's fine. Is there something I am missing here?

Ideally, I would like to pass the generated SQL directly to a stored procedure as a parameter. I tried that as well and got the same kind of error (Failed to evaluate, followed by another exception (

ErrorCode: '-2147217900' Description: 'The identifier that starts with 'update mwrequest set memDescription = ''newValue'' where efolderid= (select efolderid from efolder where emapname = ''MWRequest'' a' is too long. Maximum length is 128.' Source: 'Microsoft OLE DB Provider for SQL Server' SQL State: '42000' NativeErrorCode: '103'

Can anyone help?

[Scott]

If you would like to use a stored procedure I would recommend that you pass the values from the dropdown controls to the stored procedure, and then build the dynamic sql in the stored procedure itself.  That would be a lot more efficient and flexible in the future.

As for the current solution, could you show the code used to generate the dynamic sql?  Is it in the "fldSQL" field's calculation formula or somewhere else?  And what does the server-side button control's properties look like?

[Jerome҉]

I think your SQL may be flawed. There should not be double quotes around the text (") and the sub-query may return multiple values which would not be valid.