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 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 and to INSERT.
b. Create a event routine for the INSERT button.
Note: The controls on a form (fields, push buttons etc) have a property. You as you add controls to the form you should ensure their is set to an appropriate value. For example the three fields should be = 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 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 button to retrieve an existing record.
d. Press the 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 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.