Previous Topic Next topic Print topic


TO UPDATE USE

Specifies, for a specific DataTable, the SQL logic necessary to make updates to a data source.

Syntax:

>>--EXEC ADO--.---------------------.-TO UPDATE datatable_name--> 
               +-USING dataset_name-+

>--.----------------------------------------------------------.->
   |                                                          |
   +--WITH REFRESH USING--+-RETURNED DATAROW                 -+
                          +-RETURNED DATAROW , OUT PARAMETERS-+
                          +-OUT PARAMETERS                   -+
 
>--USE--+-sql_update_statement--.-----------------------.------->
        |                       +-sql_refresh_statement-+
        |                                               |
        +-stored_procedure_statement--------------------+                          
  
 >--END-EXEC---><

Parameters:

datatable_name The name of a DataTable.
dataset_name The DataSet reference to be used. If you do not specify dataset_name, the current DataSet is used.
WITH REFRESH USING Specifies how to retrieve new values in the data source as a result of the execution of the TO UPDATE statement.
sql_update_statement A standard SQL UPDATE (SEARCHED) statement that references an actual table and its columns in a data source.
sql_refresh_statement The SQL SELECT statement that cites columns in the DataTable that need to be refreshed.
stored_procedure_statement Specifies the stored procedure to execute to perform the SQL UPDATE (SEARCHED) statement

Comments:

The SQL logic must reference actual tables and columns in the data source.

The SQL logic can refer to column values for the row that has been deleted in the DataTable by using the column name enclosed in braces, optionally followed by the type of value (CURRENT, ORIGINAL, DEFAULT, or PROPOSED), separated by a period. For example, {OrderId} or {OrderID.Original}.

Use the WITH REFRESH USING clause to specify how to update the DataSet with new values when the data source is updated. With RETURNED DATAROWS, you must supply sql_refresh_statement, which is an SQL SELECT for the columns which are to have new values. With OUT PARAMETERS, the stored procedure that performs the SQL INSERT will supply the update columns as output parameters.

Example:

     EXEC ADO PREPARE TO UPDATE Orders, Customers, "Order Details" END-EXEC
        EXEC ADO TO UPDATE Customers USE
              UPDATE Customers SET 
                 ContactName = {ContactName},
                 City = {City}
              WHERE CustomerID = {CustomerID}
        END-EXEC
     EXEC ADO UPDATE DATASOURCE END-EXEC
Previous Topic Next topic Print topic