› User interface › Forms › Form events and actions › Form actions › DB query action › DB query to update records in a database table

DB query to update records in a database table

Create a database query to update the database records.

Before you begin
To create a DB query action, you must to set up a database connection string. The following sample data values can be provided:

  • All numeric types = -1

  • Dates = 01/01/1900

  • Byte = 0

  • String = Blank String

  • Boolean = False

Procedure
  1. On the DB query action dialog box of a form, select Configure.
    The DBQuery dialog box is displayed.
  2. To specify the database connection string that holds the details of the relational database, select either Database connection.

    • Local (Default): Select a global variable or select to manually enter the connection string.

    • External: To use the database connection from external storage, enter the secret name for the connection secret stored in the configured external storage.

    A list of schema that the database user has permission to access is displayed.

  3. On the Schema list, select a database schema to use for retrieving database objects.

    The schema list displays all schemas that the database user has permission to access. If no schema is explicitly selected, the system uses the default schema for the database user (typically dbo for SQL Server).

  4. On the Tables list, select the table for which to update the records.
    All columns within the selected table appear under Available columns.
  5. On the Record list, select Update. (Default: Get)
  6. Select the columns and select Shift right icon to move the columns from Available columns to Selected columns.

    Use and on the column header to reorder the columns.

  7. To map the values to the selected columns, on the Value list of the column, select form control, form/global variable, or field, or select and enter a static value.
  8. For Return rows affected to, select a form control, variable (form/global), or field.
  9. To update multiple records in a database table, select Perform multiple updates. (Default: Clear)
  10. Configure your query based on specific conditions.

    For example, to update the ContactName, Address, and Phone records for the cities of London and Berlin, specify the condition as follows:

    1. Select .
    2. On the Column list, select a column, such as City(String).
    3. Set the Operator as equals (default).
    4. For Criteria, select a form control, variable (form/global), or field.

      Select Null to keep the criteria empty.

    5. Set the Logic as AND.
    6. Select Add.
  11. Select OK.

    The DB query action dialog box displays the Query.

  12. Select Save.