Database UPDATE

Home Forum Integrating PerfectForms Database UPDATE

This topic contains 3 replies, has 0 voices, and was last updated by  MABrown 9 years, 4 months ago.

Viewing 4 posts - 1 through 4 (of 4 total)
  • Author
    Posts
  • #256

    MABrown
    Participant

    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.)

    #4685

    ijobling
    Participant

    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.

    #4689

    MABrown
    Participant

    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?

    #4690

    ijobling
    Participant

    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

Viewing 4 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic.