Advanced PowerBuilder

ItemChanged Event

This event occurs 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 with the second method.

Place a new DataWindow control in the window and name it as 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? I am sure you guessed it by this time! 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

This code works fine here, because, product_description column is defined as NOT NULL in the database, meaning product_description column always contain something and it is never null. If the column allows NULL value, and if the content of the product_description for that product is NULL, then the MessageBox() will never display, because you are sending a NULL value as the parameter to the MessageBox(). If you want to learn about how the MessageBox() function behaves when the message text is NULL value, Place a CommandButton in w_script_practice and write string ls_null; MessageBox( "Test", ls_null), run that window and click on that button. You will never see that message box being popped up.) If that is the case, call GetItemString() on a separate line and check for the NULL value, substitute with appropriate message and then display it. For ex:

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 column. 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 Primary buffer.

PowerBuilder sends the data entered by the user as parameters to this event in the Data variable. Remember that 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, meaning product_master would never have multiple records for a given product_no. Checking for >0 instead of = 1 will also serve the purpose; It is useful when you are 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. This statement tells 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 as per 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 come from? The next event ItemError will solve this mystery.

Home	Previous Lesson: EditChanged Event Next Lesson: ItemError Event