Advanced PowerBuilder

DataWindow Events

Till now, most of the code was written for CommandButton's Clicked event. Similar to CommandButtons, DataWindow Controls also have many events. The following are the list of DataWindow Control's important events and let's learn about them one after the other.

RetrieveStart Event

This event triggers as soon as PowerBuilder gets notification from the database saying "your query is done and this is the result set you are waiting for", i.e., even before PowerBuilder starts retrieving the first row. Display any message on the status bar, by writing the following code.

// Object: DataWindow "dw_query" in w_product_master window
// Event: RetrieveStart

w_mdi_frame.SetMicroHelp( "Starting to Retrieve Rows…" )

RetrieveRow Event

This event triggers after retrieving the row and before displaying it on the screen. This is the right place to write the code, if you want to do some process on the row,  before the user sees the row on the screen. Writing code in this event and setting the "asynchronous" option of the transaction object to "true", makes PowerBuilder to display the row on the screen, as soon as the code in this event is executed. For example, say, the result set of a query is 10,000 rows. User will see the data on the screen only when PowerBuilder completes retrieval of the 10,000^th row*. By writing code in this event ( even a single line comment ), user will see the data on the screen as soon as the first row is received by PowerBuilder ( of course, code for this event is executed before it is displayed on the screen ).

By writing code or comment increases the total time required to retrieve the result set. To reduce the time, you might want to write the code in the "RetrieveEnd" ( explained later ), which is executed after retrieving all the rows in the result set. Writing code for the "RetrieveRow" or "RetrieveEnd" depends on the application requirement.

* There is an exception to this behavior. If "Retrieve only as needed" option is set, PowerBuilder displays data as soon it completes retrieving one screen full of data. Whenever user tries to scroll down the screen, another screen full of data will be retrieved. The number of rows that fits on a single screen is determined by PowerBuilder, depending on the DataWindow control size at run-time. Let's write the following code:

// Object: DataWindow "dw_query" in w_product_master window
// Event: RetrieveRow

w_mdi_frame.SetMicroHelp( "Rows Retrieved: " + &
                           String( row ) )

RetrieveEnd Event

This event triggers as soon as the last row of the result set is retrieved.

// Object: DataWindow "dw_query" in w_product_master window
// Event: RetrieveEnd

w_mdi_frame.SetMicroHelp( "Rows Retrieved: " + &
                           String( RowCount ) +  &
                      ". Query execution complete.")

PrintStart Event

This event gets triggered as soon as printing is started by PowerBuilder.

// Object: DataWindow "dw_product" in w_product_master window
// Event: PrintStart

w_mdi_frame.SetMicroHelp( "Starting to Print…" )

PrintPage Event

This event also gets triggered for each page printed ( just before the page gets printed. Please note that, RetrieveRow event gets triggered after the row is retrieved by PowerBuilder. ). PowerBuilder sends "PageNumber" as the parameter to this event.

// Object: DataWindow "dw_product" in w_product_master window
// Event: PrintPage

w_mdi_frame.SetMicroHelp( "Printing Page # " + &
                           String( PageNumber ) )

Infact, you need not code the above script, because, when you call DataWindow's Print() function with TRUE as an argument, PowerBuilder automatically displays the page number being printed and also allows you to cancel if needed.

PrintEnd Event

This event gets triggered as soon as the last page is printed.

UpdateStart Event

This event gets triggered just before PowerBuilder starts sending changes back to the database.

UpdateEnd Event

This event gets triggered as soon as the changes are applied to the database.

EditChanged Event

This event gets triggered whenever user types something in the current field ( irrespective of its contents being changed or not ). You wouldn't want to code for this event, unless you want to validate each key the user presses. Do not confuse "EditChanged" event with "ItemChanged" event; "EditChanged" event triggers whenever user types, where as "ItemChanged" event triggers, whenever data is changed and the current field looses its focus ( clicking on other field or other control, pressing tab, etc. ).

ItemChanged Event

This event triggers whenever the data is changed and the current field looses its focus ( clicking on other field or other control, pressing tab, etc. ). Use this event to validate the data and trigger "ItemError" event, whenever there is an error in the data. This is one of the frequently used events in the DataWindow control. This event gives access to the old data ( which is retrieved from the database ), as well as the new data (  the data changed by the user ).

Let's write some validation code in the event. For example, let's display an error message, if the user enters an existing product_no, while entering a new record. Actually, database gives an error message if duplicate product_no is entered, because, product_no is a primary key for the product_master table. The reason we would like to validate here is, to display the error message as soon as the user enters a duplicate product_no, instead of waiting till the whole record is entered and sent to the database. Which means that we need to check for the existence of the product_no, as soon as the user presses the tab key in the new record.

This check can be done by using Embedded SQL or with a hidden DataWindow control. We didn't teach embedded SQL yet, so, let's go about it using the second method.

Place a new DataWindow control in the window and name it "dw_product_check". Go to the properties dialog box for this DataWindow control and assign "d_display_product" DataWindow object to this control. In case you don't remember, "d_display_product" takes an argument product_no and brings the data for the given product_no. If you don't get any results for the given product_no, it means that the product_no is not existing.

We find no reason to display "dw_product_check" DataWindow control to the user, so, turn off its "Visible" property.

Do you remember what we should do before we do any database operation on this DataWindow control ? Right ! We need to set the transaction object. Write the following code:

// Object: Window "w_product_master"
// Event: Open
// Append the following line to the existing code.

dw_product_check.SetTransObject( SQLCA )

Write the following code in the "ItemChanged" event for the dw_product DataWindow.

// Object: dw_product in w_product_master Window
// Event: ItemChanged

If This.GetColumnName() = "product_no" And &
        ( This.GetItemStatus( Row, 0, Primary! ) = New! Or &
        This.GetItemStatus( Row, 0, Primary! ) = &
        NewModified! ) Then
   dw_product_check.Retrieve( Integer( Data ) )
   If dw_product_check.RowCount() = 1 Then
      MessageBox( "Error", "Product No: '" + Data + &
                  " ->" + &
                  dw_product_check.GetItemString(Row, &
                  "product_description") +&
                  "' already exists.", StopSign!, OK!,1)
      Return 1
   End If
End If

String l_desc1 

l_desc1 = dw_product_check.GetItemString( Row,&
                           "product_description )
if IsNull( l_desc1 ) Then l_desc1 = "Not Defined."
MessageBox( "Error", "Product No: '" + l_desc1 + &
            "' already exists, StopSign!, OK!,1)

The first IF statement is checking for two things. One, whether the user is tabbing out of "product_no" column or any other column. We are interested in it only if the user is leaving "product_no". GetColumnName() returns the current column name. Just FYI, GetColumn() returns the column number.

As you have learned in the "DataWindow Buffers" section, a row can be in one of the four statuses. New!, NewModified!, NotModified!, DataModified!. Typically, user might enter the "product_no", since it is the first column in the DataWindow. When the row is inserted, it is in the New! status and remains in the New! status till the user presses the tab and the ItemChanged event completes the script execution successfully. That's why we check for the New! status.

Sometimes,  user might enter data in the new record and navigate to other records and come back to change the "product_no". For that reason, we need to check for the NewModified! status.

GetItemStatus() gives the status of the specified column, in the specified buffer. If you specify the column number as 0  ( zero ), it returns the row status, instead of the column status. The third argument is the buffer name. Here, we are interested in the PrimaryBuffer.

PowerBuilder sends the data entered by the user as parameters to this event in the "Data" variable. Remember, it is always in the string format. We need to convert into appropriate formats. The next line is bringing data from the database. Observe the datatype conversion of the Retrieve() function parameter. RowCount() function returns the number of rows in the specified DataWindow. If the row count is greater than zero, it means that the given product is existing in the "product_master". ( Here, we are checking for 1 because, the product_no in "product_master" table is unique. Checking for ">0" instead of "= 1" will also do; It is useful when expecting one or more rows. ) Then we are displaying the error information by using the  MessageBox() function.

The last line "Return 1" is very important. We tell PowerBuilder, what it has to do after completing the script execution.

Return Code	Action
0	( Default ) Accept the data value.
1	Reject the data value and don't allow the focus to change.
2	Reject the data value, but allow the focus to change.

If the return value is 0 ( Zero ), PowerBuilder triggers "ItemFocusChanged" event and executes the script for that event and finally, the focus changes to the next column depending on the tab order. In this case, "product_description" is the next column.

If the return code is 1, PowerBuilder triggers "ItemError" event and executes the script for "ItemError" event and the cursor remains in the current field i.e., "product_no". Return code 2 is similar to code 1, except that, it changes focus to the next field in the tab order.

At this point, run the application, add a new record and enter an existing product_no and press the tab key. You will see two error messages. We were expecting only the MessageBox() function we placed in the ItemChanged event. Then where did the other message came from? The next event "ItemError" will solve this mystery.

ItemError Event

This event gets triggered when validation rules fail ( the data doesn't pass the validation rule ), and also when the "ItemChanged" event returns non-zero return code. If there is no code in this event by default, PowerBuilder displays an error message in the default format, and rejects the data and then the cursor remains in the current field.

You can tell PowerBuilder to display error messages or not, to reject data or not, by using proper return codes.

Now, you know why PowerBuilder was displaying two error messages. Did you find the solution for the problem from the return codes above. If not, just type "Return 1" in the "ItemError" event for the "dw_product" DataWindow. Now, run the application and test it.

It works perfectly. Do you know what we did? We fixed one problem and started many.

We have two situations. Display an error message in the ItemChanged event, and don't let PowerBuilder display an error message in the ItemError event. Don't display an error message in the ItemChanged event, and let PowerBuilder display its default error message.

We are displaying our message only for product_no. We are NOT displaying for other fields and for failed validation rules. So, let PowerBuilder display its own.

"Return 1" doesn't display the error message. That means we are telling PowerBuilder never to display error messages in the ItemError event, rather than telling it to display conditionally.

Now, let's display error messages in the ItemError event, conditionally.

Declare an instance variable by selecting 'Declare/Instance Variables' from the menu.

Boolean i_DoNotDisplayErrMsg = FALSE

Insert the following code, just above the "Return 1" statement in the "ItemChanged" event.

i_DoNotDisplayErrMsg = TRUE

Write the following code in the ItemError event for the dw_product.

// Object: DataWindow_product in w_product_master
// Event: ItemError

If i_DoNotDisplayErrMsg Then
   i_DoNotDisplayErrMsg = False
   Return 1
Else
   Return 0
End If

To test this, run the application. In a new record, enter an existing product_no and see how many error messages you get. Enter 0 ( zero ) in the "product_reorder_level" and see if you get the error message you defined in the validation rule.

ItemFocusChanged Event

This event gets triggered whenever the current field looses the focus, irrespective of whether the data was changed or not. Typical use of this event is to give control to the proper field depending on the value of the current field.

Don't confuse this event with "LooseFocus". LooseFocus event triggers when the DataWindow itself looses the focus. Remember, there may be other controls in a window, and the DataWindow control is one of those. A DataWindow control looses its focus when you click on any other control in the window or press tab when the focus is on the last field ( whichever field has the highest tab order in the DataWindow control  ) in the last row ( unless you add code in the DataWindow control to add a new row as soon as the tab is pressed in the last field. ).

Some functions give different results depending on the event from which you are calling the function. For example, say, the cursor is in the product_no field and you press tab. Now, you expect the cursor to go into "product_description" field. At this point, if you call GetColumnName() function in the ItemChanged event, you get "product_no". If you call, the same function in ItemFocusChanged event, you get "product_description".

DbError Event

Any database related error in DataWindow triggers DbError event. To find out the error number, you need not refer to the SQLCA object, instead, you can refer to the event parameters. SQLDbCode gives you the database specific error number. SQLErrText contains the actual error message from the database. You can also see the SQL statement that caused this error by referring to SQLSyntax. The row number that caused the error can be found in the 'row' and buffer name in the 'buffer' parameter. We will be using this event, later in this session.

Error Event

This event triggers when:

	You use OLE object DataWindow and an error occurs in the OLE object.
	You use wrong expression while accessing DataWindow in the dot notation.

You have the option of ignoring/changing values of the expression, retrying or failing. If you don't write script, it triggers "SystemError" event at the application level.

SQLPreview Event

This event is triggered when ever a SQL statement is sent to the database. That means, this is the right place ( only place ) to see and modify the SQL statement before sending it to the database. You can also skip sending the current SQL statement or stop the whole operation by returning appropriate return code.

For example, see the SQL statement sent by PowerBuilder to the database, by writing the following code in the 'SQLPreview' event in "dw_product" and "dw_query". We are writing in "dw_query" because, we retrieve the data from "dw_query" and update the database using "dw_product" DataWindows. Writing the following statement in both DataWindows, display the SQL statements while retrieving as well as while updating the database.

MessageBox( "SQL", SqlSyntax )

Now, run the application and retrieve the data. PowerBuilder will display the SELECT statement. Do some changes in the data and save the changes back to the database.  You will see INSERT/UPDATE/DELETE statements, depending on the changes you do.

Home	Previous Lesson: DataWindow - Data Validation Next Lesson: DataWindow Update Properties