- July 20, 2009 at 7:32 pm #256
I have successfully created an INSERT action to add rows to a database designed to hold information from my form. Now I need to create an action to update some of the fields in those rows.
I have setup an UPDATE action, but I cannot get it to work. Do I need to explicitly allow UPDATE and DELETE actions in the Connection Agent configuration file? I am able to perform an UPDATE query in SQL from a database management tool, so I am not sure that it is a matter of permissions on the database side.
I tried sending the conditions (the WHERE clause) in the upper half of the parameters and the new values in the lower half of the parameters, and vice versa. Neither worked. (As a suggestion for a future improvement, explicitly state which is which.)July 21, 2009 at 8:19 am #4685
Could you have some fields in your DB table that are not set to ‘allow nulls’ such that if your update action is not writing back to all the fields, then it is being refused?
look to the Connection agent log files as there may be more information there if there is such a problem, and if it is a configuration issue and you will need to explicity allow ‘update’ it will show in there.
your actions to pass the ‘where’ in the top part of the action is correct and something we are already aware of.July 21, 2009 at 7:04 pm #4689
Now I understand; I mistakenly assumed that empty parameters would not be passed in the query. In looking at the log file, I now see that empty parameters are passed as “”, hence my UPDATE did not work because it was looking for a record of (mostly) empty strings.
I created and tested an UPDATE action passing only two parameters, the row ID (primary key) for the WHERE clause, and an updated string for an Employee Name field, which worked as expected.
Is there any way to setup an UPDATE action for all fields where you can specify that the action omit empty fields from the query?July 22, 2009 at 8:11 am #4690
Not at the moment I’m afraid, but its something we are aware of as an annoyance and looking at how we can handle this.
However, you could probably do this by setting up a stored procedure on your server that handles this by ignoring/discarding empty records and then set up an action using the ‘exec’ function
You must be logged in to reply to this topic.