Step 3 Insert Data to a File

Visual LANSA

Step 3. Insert Data to a File

FRM035 - Maintain a Simple Database Table

In this step you will let the user insert new data into the DEPTAB file.

You will add an Insert push button to the form along with the STD_NUM field. The STD_NUM field is not used in the  DEPTAB file but will used to help demonstrate program level validation rules. You will also use a GROUP_BY  statement to simplify the code in your form.

1.  Drag and drop the STD_NUM field to the form.

2.  Drag and drop a push button to the form.

a.  Set the button Name and Caption to INSERT.

b.  Create a Click event routine for the INSERT button.

     Note: The controls on a form (fields, push buttons etc) have a TabPosition property. You as you add controls to the form you should ensure their TabPosition is set to an appropriate value. For example the three fields should be TabPosition = 1,2 and 3

3.  Your form should appear like this:

4.  For each of the database commands, you will be including a list of fields to fetch, insert, update and delete. To simplify your I/O statements, add a GROUP_BY command after your DEFINE_COM statements so that you can refer to all the fields by the group name:

GROUP_BY NAME(#FORMDATA) FIELDS(#DEPTMENT #DEPTDESC #STD_NUM)
 

     Once added, you change the FETCH command as follows:

FETCH FIELDS(#FORMDATA) FROM_FILE(DEPTAB) WITH_KEY(#DEPTMENT)
 

     Notice that the STD_NUM field can be included in the GROUP_BY used by the FETCH even though it is not a field in the DEPTAB file. In database operations the STD_NUM field will be ignored, but used in other operations where the group is used.

5.  In the INSERT.Click event routine, add an INSERT command to add a new record to the file.

     Remember to add the appropriate error checking. Once the INSERT completes successfully, all fields on the form should be reset to their repository defaults.

     Your finished code should appear as follows:

EVTROUTINE HANDLING(#INSERT.Click)
INSERT FIELDS(#FORMDATA) TO_FILE(DEPTAB)
IF_STATUS IS(*OKAY)

MESSAGE MSGTXT(
'Department inserted successfully')
#FORMDATA := *DEFAULT

ELSE

IF_STATUS IS(*ERROR)

MESSAGE MSGTXT(
'Error inserting Department')
ENDIF

ENDIF

ENDROUTINE

 

     Note that the INSERT command has many parameters which are not shown in the editor when their default values are used. The INSERT command you have created looks like this with default values shown:

INSERT FIELDS(#FORMDATA) TO_FILE(DEPTAB) IO_STATUS(*STATUS) IO_ERROR(*ABORT)

VAL_ERROR(*LASTDIS) ISSUE_MSG(*NO) RETURN_RRN(*NONE) CHECK_ONLY(*NO)

AUTOCOMMIT(*FILEDEF)

6.  Compile and execute the form.

a.  Leave the Department Code and Description blank and press the Insert button.

     Notice that fields in error are highlighted and error messages are displayed.

b.  Review the error messages. They are caused by the validation rules in the Repository. When the INSERT fails due to these repository errors, the program automatically returns to the last display. This is controlled by the default VAL_ERROR(*LASTDIS) parameter on the INSERT command.

c.  Enter a Department Code of ADM and press the Fetch button to retrieve an existing record.

d.  Press the Insert button to try to duplicate this data in the database.

     Notice that no fields are highlighted in error as the repository validation rules have been satisfied. The error message is automatically generated by LANSA.

e.  Enter a Department Code of III (where iii = your initials) and enter your name for the Department Description. Enter the number 999 into Standard Number. Press the Insert button.

     (Remember STD_NUM is not used in the DEPTAB file.)

     The new record should be inserted into the database, a message displayed, and all fields reset to their default.

7.  Close the form.