Updating the database
After users have made changes to data in a DataWindow control,
you can use the Update method to save the changes in the database.
Update sends to the database all inserts, changes, and deletions
made in the DataWindow since the last Update or Retrieve method
was executed.
How the DataWindow control updates the database
When updating the database, the DataWindow control determines
the type of SQL statements to generate by looking at the status
of each of the rows in the DataWindow buffers.
There are four DataWindow item statuses, two of which apply
only to rows:
| Status | Applies to | ||
|---|---|---|---|
| PowerBuilder name | Web DataWindow name | Numeric value | |
| New! | New | 2 | Rows |
| NewModified! | NewModified | 3 | Rows |
| NotModified! | NotModified | 0 | Rows and columns |
| DataModified! | DataModified | 1 | Rows and columns |
Named or numeric constants The constants shown in the table are used differently in each
environment:
PowerBuilder The named values are values of the enumerated datatype dwItemStatus.
You must use the named values, which end in an exclamation point.
Web DataWindow You can use a string value with or without the exclamation
point
Web ActiveX Named values are not defined; use the numeric values.
This discussion uses the PowerBuilder names.
How statuses are set
When data is retrieved When data is retrieved into a DataWindow, all rows and columns
initially have a status of NotModified!.
After data has changed in a column in a particular row, either
because the user changed the data or the data was changed programmatically,
such as through the SetItem method, the column status for that column
changes to DataModified!. Once the status for any column in a retrieved
row changes to DataModified!, the row status also changes to DataModified!.
When rows are inserted When a row is inserted into a DataWindow, it initially has
a row status of New!, and all columns in that row initially have
a column status of NotModified!. After data has changed in a column
in the row, either because the user changed the data or the data
was changed programmatically, such as through the SetItem method,
the column status changes to DataModified!. Once the status for
any column in the inserted row changes to DataModified!, the row
status changes to NewModified!.
When a DataWindow column has a default value, the column’s
status does not change to DataModified! until the user makes at
least one actual change to a column in that row.
When Update is called
For rows in the Primary and Filter buffers When the Update method is called, the DataWindow control generates SQL INSERT and UPDATE statements
for rows in the Primary and/or Filter buffers based upon
the following row statuses:
| Row status | SQL statement generated |
|---|---|
| NewModified! | INSERT |
| DataModified! | UPDATE |
A column is included in an UPDATE statement only if the following
two conditions are met:
- The
column is on the updatable column list maintained by the DataWindow
object
For more information about setting the update
characteristics of the DataWindow object, see the PowerBuilder
User’s Guide
. - The column has a column status of DataModified!
The DataWindow control includes all columns in INSERT statements
it generates. If a column has no value, the DataWindow attempts
to insert a NULL. This causes a database error if the database does
not allow NULLs in that column.
For rows in the Delete buffer The DataWindow control generates SQL DELETE statements for
any rows that were moved into the Delete buffer using the DeleteRow
method. (But if a row has a row status of New! or NewModified! before
DeleteRow is called, no DELETE statement is issued for that row.)
Changing row or column status programmatically
You might need to change the status of a row or column programmatically. Typically,
you do this to prevent the default behavior from taking place. For example,
you might copy a row from one DataWindow to another; and after the user
modifies the row, you might want to issue an UPDATE statement instead of
an INSERT statement.
You use the SetItemStatus method to programmatically change
a DataWindow’s row or column status information. Use the
GetItemStatus method to determine the status of a specific row or
column.
Changing column status
You use SetItemStatus to change the column status from DataModified!
to NotModified!, or the reverse.
Change column status when you change row status Changing the row status changes the status of all columns
in that row to NotModified!, so if the Update method is called,
no SQL update is produced. You must change the status of columns
to be updated after you change the row status.
Changing row status
Changing row status is a little more complicated. The following
table illustrates the effect of changing from one row status to
another:
| Original status |
Specified status |
|||
|---|---|---|---|---|
| New! | NewModified! | DataModified! | NotModified! | |
| New! | – | Yes | Yes | No |
| NewModified! | No | – | Yes | New! |
| DataModified! | NewModified! | Yes | – | Yes |
| NotModified! | Yes | Yes | Yes | – |
In the preceding table, Yes means the
change is valid. For example, issuing SetItemStatus on a row that
has the status NotModified! to change the status to New! does change
the status to New!. No means that the change
is not valid and the status is not changed.
Issuing SetItemStatus to change a row status from NewModified!
to NotModified! actually changes the status to New!. Issuing SetItemStatus
to change a row status from DataModified! to New! actually changes
the status to NewModified!.
Changing a row’s status to NotModified! or New! causes
all columns in that row to be assigned a column status of NotModified!.
Change the column’s status to DataModified! to ensure that
an update results in a SQL Update.
Changing status indirectly When you cannot change to the desired status directly, you
can usually do it indirectly. For example, change New! to DataModified!
to NotModified!.