Modify

Description

Modifies a DataWindow object by applying specifications, given as a list of instructions, that change the DataWindow object’s definition.

You can change appearance, behavior, and database information for the DataWindow object by changing the values of properties. You can add and remove controls from the DataWindow object by providing specifications for the controls.

Applies to

Syntax

string dwcontrol.Modify  ( string modstring ) 

Returns

Returns the empty string (“”) if it succeeds and an error message if an error occurs. The error message takes the form "Line n Column n incorrect syntax". The character columns are counted from the beginning of the compiled text of modstring.

If any argument’s value is null, in PowerBuilder and JavaScript the method returns null.

Usage

Modify lets you make many of the same settings in a script that you would make in the DataWindow painter. Typical uses for Modify are:

• Changing colors, text settings, and other appearance settings of controls

• Changing the update status of different tables in the DataWindow so that you can update more than one table

• Modifying the WHERE clause of the DataWindow object’s SQL SELECT statement

• Turning on Query mode or Prompt For Criteria so users can specify the data they want

• Changing the status of Retrieve Only As Needed

• Changing the data source of the DataWindow object

• Controlling the Print Preview display

• Deleting and adding controls (such as lines or bitmaps) in the DataWindow object

Each of these uses is illustrated in the Examples for this method.

You can use three types of statements in modstring to modify a DataWindow object.

The DataWindow painter automatically gives names to all controls. In previous versions, it named only columns and column labels, and to describe and modify properties of other controls easily, you had to name them.

When you specify an expression for a DataWindow property, the expression has the format:

defaultvalue~tDataWindowpainterexpression

Defaultvalue is a value that can be converted to the appropriate datatype for the property. It is followed by a tab (~t).

DataWindowpainterexpression is an expression that can use any DataWindow painter function. The expression must also evaluate to the appropriate datatype for the property. When you are setting a column’s property, the expression is evaluated for each row in the DataWindow, which allows you to vary the display based on the data.

A typical expression uses the If function:

'16777215 ~t If(emp_status=~~'A~~',255,16777215)'

To use that expression in a modstring, specify the following (entered as a single line):

modstring = "emp_id.Color='16777215 ~t

If(emp_status=~~'A~~',255,16777215)'"

Because Modify’s argument is a string, which can include other strings, you need to use special syntax to specify quotation marks. To specify that a quotation mark be used within the string rather than match and end a previously opened quote, you can either specify the other style of quote (single quotes nested with double quotes) or precede the quotation mark with a tilde (~).

For another level of nesting, the string itself must specify ~", so you must include ~~ (which specifies a tilde) followed by ~" (which specifies a quote). For example, another way to type the modstring shown above (entered as a single line) is:

modstring = "emp_id.Color=~"16777215 ~t

If(emp_status=~~~"A~~~",255,16777215)~""

For more information about quotes and tildes, see the section on standard datatypes in the PowerScript Reference.

To use variable data in modstring, you can build the string using variables in your program. As you concatenate sections of modstring, make sure quotes are included in the string where necessary. For example, the following code builds a modstring similar to the one above, but the default color value and the two color values in the If function are calculated in the script. Notice how the single quotes around the expression are included in the first and last pieces of the string:

red_amount = Integer(sle_1.Text)

modstring = "emp_id.Color='" + &

		String(RGB(red_amount, 255, 255)) + &

		"~tIf(emp_status=~~'A~~'," + &

		String(RGB(255, 0, 0)) + &

		"," + &

		String(RGB(red_amount, 255, 255)) + &

		")'"

The following is a simpler example without the If function. You do not need quotes around the value if you are not specifying an expression. Here the String and RGB functions produce in a constant value in the resulting modstring:

modstring = "emp_id.Color=" + &

		String(RGB(red_amount, 255, 255))

You can set several properties with a single call to Modify by including each property setting in modstring separated by spaces. For example, assume the following is entered on a single line in the script editor:

rtn = dw_1.Modify("emp_id.Font.Italic=0

oval_1.Background.Mode=0

oval_1.Background.Color=255")

However, it is easier to understand and debug a script in which each call to Modify sets one property.

PowerBuilder already includes many functions for modifying a DataWindow. Before using Modify, check the list of DataWindow functions in Objects and Controls to see if a function exists for making the change. Many of these functions are listed in the See also section.

Modify is for modifying the properties of a DataWindow object and its internal controls. You can set properties of the DataWindow control that contains the object using standard dot notation. For example, to put a border on the control, specify:

dw_1.Border = true

dwcontrolname.Modify ( "DataWindow.Color='long'" )

To set the text color of a column or a text control, use similar syntax:

dwcontrolname.Modify ( "controlname.Color='long'" )

To set the background color of a column or other control, use the following syntax to set the mode and color. Make sure the mode is opaque:

dwcontrolname.Modify ( "controlname.Background.Mode= & 
	'<0 - Opaque, 1 - Transparent>'")

dwcontrolname.Modify ( "controlname.Background.Color='long'" )

Example 3

The following examples use the syntaxes shown above to set the colors of various parts of the DataWindow object.

This statement changes the background color of the DataWindow dw_cust to red:

dw_cust.Modify("DataWindow.Color = 255")

Example 4

This statement causes the DataWindow dw_cust to display the text of values in the salary column in red if they exceed 90,000 and in green if they do not:

dw_cust.Modify( &

		"salary.Color='0~tIf(salary>90000,255,65280)'")

Example 5

This statement nests one If function within another to provide three possible colors. The setting causes the DataWindow dw_cust to display the department ID in green if the ID is 200, in red if it is 100, and in black if it is neither:

dw_cust.Modify("dept_id.Color='0~t " &

	 + "If(dept_id=200,65380,If(dept_id=100,255,0))'")

Example 6

The following example uses a complex expression with nested If functions to set the background color of the salary column according to the salary values. Each portion of the concatenated string is shown on a separate line. See the pseudocode in the comments for an explanation of what the nested If functions do. The example also sets the background mode to opaque so that the color settings are visible.

The example includes error checking, which displays Modify’s error message, if any:

string mod_string, err

long color1, color2, color3, default_color

err = dw_emp.Modify("salary.Background.Mode=0")

IF err <> "" THEN

		MessageBox("Status", &

			"Change to Background Mode Failed " + err)

		RETURN

END IF

/* Pseudocode for mod_string:

If salary less than 10000, set the background to red.

If salary greater than or equal to 10000 but less than 20000, set the background to blue.

If salary greater than or equal to 20000 but less than 30000, set the background color to green.

Otherwise, set the background color to white, which is also the default. */

color1 = 255 //red

color2 = 16711680 //blue

color3 = 65280 //green

default_color = 16777215//white

mod_string = &

		"salary.Background.Color = '" &

			+ String(default_color) &

			+ "~tIf(salary < 10000," &

			+ String(color1) &

			+ ",If(salary < 20000," &

			+ String(color2) &

			+ ",If(salary < 30000," &

			+ String(color3) &

			+ "," &

			+ String(default_color) &

			+ ")))'"

err = dw_emp.Modify(mod_string)

IF err <> "" THEN

		MessageBox("Status", &

			"Change to Background Color Failed " + err)

		RETURN

END IF

Example 7

This example sets the text color of a RadioButton column to the value of color1 (red) if the column’s value is Y; otherwise, the text is set to black. As above, each portion of the concatenated string is shown on a separate line:

integer color1, default_color

string mod_string, err

color1 = 255 //red

default_color = 0 //black

mod_string = "yes_or_no.Color ='" &

		+ String(default_color) &

		+ "~tif(yes_or_no=~~'Y~~'," &

		+ String(color1) &

		+ "," &

		+ String(default_color) &

		+ ")'"

err = dw_emp.Modify(mod_string)

IF err <> "" THEN

		MessageBox("Status", &

			"Modify to Text Color " &

			+ "of yes_or_no Failed " + err)

		RETURN

END IF

Example 8

dwcontrolname.Modify ( "textcontrolname.Text='string'" )

Example 9

This statement changes the text in the text control Dept_t in the DataWindow dw_cust to Dept:

dw_cust.Modify("Dept_t.Text='Dept'")

Example 10

This statement sets the displayed text of dept_t in the DataWindow dw_cust to Marketing if the department ID is greater than 201; otherwise it sets the text to Finance:

dw_cust.Modify("dept_t.Text='none~t " + &

		"If(dept_id > 201,~'Marketing~',~'Finance~')'")

Example 11

Example 12

In this example, the DataWindow object joins two tables: department and employee. First department is updated, with status flags not reset. Then employee is made updatable and is updated. If all succeeds, the Update command resets the flags and COMMIT commits the changes. Note that to make the script repeatable in the user’s session, you must add code to make department the updatable table again:

integer rc

string err

/* The SELECT statement for the DataWindow is:

SELECT department.dept_id, department.dept_name,

employee.emp_id, employee.emp_fname,

employee.emp_lname FROM department, employee ;

*/

// Update department, as set up in the DW painter

rc = dw_1.Update(true, false)

IF rc = 1 THEN

		//Turn off update for department columns.

		dw_1.Modify("department_dept_name.Update = No")

		dw_1.Modify("department_dept_id.Update = No")

		dw_1.Modify("department_dept_id.Key = No")

		// Make employee table updatable.

		dw_1.Modify( &

		 "DataWindow.Table.UpdateTable = ~"employee~"")

		//Turn on update for desired employee columns.

		dw_1.Modify("employee_emp_id.Update = Yes")

		dw_1.Modify("employee_emp_fname.Update = Yes")

		dw_1.Modify("employee_emp_lname.Update = Yes")

		dw_1.Modify("employee_emp_id.Key = Yes")

		//Then update the employee table.

		rc = dw_1.Update()

		IF rc = 1 THEN

			COMMIT USING SQLCA;

		ELSE

		ROLLBACK USING SQLCA;

		MessageBox("Status", &

			+ "Update of employee table failed. " &

			+ "Rolling back all changes.")

		END IF

ELSE

		ROLLBACK USING SQLCA;

		MessageBox("Status", &

			+ "Update of department table failed. " &

			+ "Rolling back changes to department.")

END IF

Example 13

Example 14

The original SELECT statement might be:

SELECT employee.emp_id, employee.l_name

		FROM employee

Example 15

Presumably, the application builds a WHERE clause based on the user’s choices. The WHERE clause might be:

WHERE emp_id > 40000

Example 16

The script for the window’s Open event stores the original SELECT statement in original_select, an instance variable:

dw_emp.SetTransObject(SQLCA)

original_select = &

		dw_emp.Describe("DataWindow.Table.Select")

Example 17

The script for a CommandButton’s Clicked event attaches a WHERE clause stored in the instance variable where_clause to original_select and assigns it to the DataWindow’s Table.Select property:

string rc, mod_string

mod_string = "DataWindow.Table.Select='" &

		+ original_select + where_clause + "'"

rc = dw_emp.Modify(mod_string)

IF rc = "" THEN

		dw_emp.Retrieve( )

ELSE

		MessageBox("Status", "Modify Failed" + rc)

END IF

Example 18

In this example, a window that displays a DataWindow control has a menu that includes a selection called Select Data. When the user chooses it, its script displays the DataWindow control in query mode and checks the menu item. When the user chooses it again, the script turns query mode off and retrieves data based on the new WHERE clause specified by the user through query mode. The script also makes a CheckBox labeled Sort data visible, which turns query sort mode on and off.

The script for the Select Data menu item is:

Example 19

string rtn

IF m_selectdata.Checked = false THEN

		// Turn on query mode so user can specify data

		rtn = dw_1.Modify("DataWindow.QueryMode=YES")

		IF rtn = "" THEN

			// If Modify succeeds, check menu to show

			// Query mode is on and display sort CheckBox

			This.Check()

			ParentWindow.cbx_sort.Show()

		ELSE

			MessageBox("Error", &

				"Can't access query mode to select data.")

		END IF

ELSE

		// Turn off Query mode and retrieve data

		// based on user's choices

		rtn = dw_1.Modify("DataWindow.QueryMode=NO")

		IF rtn = "" THEN

			// If Modify succeeds, uncheck menu to show

			// Query mode is off, hide the sort

			// CheckBox, and retrieve data

			This.UnCheck()

			ParentWindow.cbx_sort.Hide()

			dw_1.AcceptText()

			dw_1.Retrieve()

		ELSE

			MessageBox("Error", &

				"Failure exiting query mode.")

		END IF

END IF

Example 20

A simple version of the script for Clicked event of the Sort data CheckBox follows. You could add code as shown in the Menu script above to check whether Modify succeeded:

IF This.Checked = true THEN

		dw_1.Modify("DataWindow.QuerySort=YES")

ELSE

		dw_1.Modify("DataWindow.QuerySort=NO")

END IF

Example 21

For details on how you or the user specifies information in query mode, see the PowerBuilder Users Guide.

Prompt for criteria is another way of letting the user specify retrieval criteria. You set it on a column-by-column basis. When a script retrieves data, PowerBuilder displays the Specify Retrieval Criteria window, which gives the user a chance to specify criteria for all columns that have been set.

In a script that is run before you retrieve data, for example, in the Open event of the window that displays the DataWindow control, the following settings would make the columns emp_name, emp_salary, and dept_id available in the Specify Retrieval Criteria dialog when the Retrieve method is called:

dw_1.Modify("emp_name.Criteria.Dialog=YES")

dw_1.Modify("emp_salary.Criteria.Dialog=YES")

dw_1.Modify("dept_id.Criteria.Dialog=YES")

Example 23

After you have determined that enough rows have been retrieved, the following code in the RetrieveRow event script changes the Retrieve.AsNeeded property, which forces the rest of the rows to be retrieved:

dw_1.Modify("DataWindow.Retrieve.AsNeeded=NO")

Example 24

When you define the DataWindow object, you must define all possible DataWindow retrieval arguments. In this example, the SELECT statement defined in the painter has three arguments, one of type string, one of type number, and one of type date. The stored procedure has two arguments, both of type string. So, in the painter, you need to define four DataWindow arguments, two of type string, one of type number, and one of type date. (Note that you do not have to use all the arguments you define.)

Example 25

string rc, mod_string, name_str = "Watson"

integer dept_num = 100

// Remove the DataWindow's SELECT statement

Dw_1.Modify("DataWindow.Table.Select = ''")

// Set the Procedure property to your procedure

mod_string = "DataWindow.Table.Procedure = &

		'1 execute dbo.emp_arg2;1 @dept_id_arg &

		= :num_arg1, @lname_arg = :str_arg1'"

rc = dw_1.Modify(mod_string)

// If change is accepted, retrieve data

IF rc = "" THEN

		dw_1.Retrieve(dept_num, name_str)

ELSE

		MessageBox("Status", &

			"Change to DW Source Failed " + rc)

END IF

Example 26

Example 27

dw_cust.Modify("destroy logo")

Example 28

This statement deletes the column named salary from the DataWindow dw_cust. Note that this example includes the keyword column, so the column in the DataWindow and the data are both deleted:

dw_cust.Modify("destroy column salary")

Example 29

This example adds a rectangle named rect1 to the header area of the DataWindow dw_cust (with the value of modstring entered as a single line):

string modstring

modstring = 'create rectangle(Band=background X="206" Y="6" height="69" width="1363" brush.hatch="6" brush.color="12632256" pen.style="0" pen.width="14" pen.color="268435584" background.mode="2" background.color="-1879048064" name=rect1 )'

dw_cust.Modify(modstring)

Example 30

These statements add a bitmap named logo to the header area for grouping level 1 in the DataWindow dw_cust (with the value of modstring entered as a single line):

string modstring

modstring = 'create bitmap(band=footer x="37" y="12" height="101" width="1509" filename="C:\PB\BEACH.BMP" border="0" name=bmp1 )'

dw_cust.Modify(modstring)

See also