Updating a Record in a Data Source

In this section:

How to:

Your next step is to create the code that will update the fan information for the fan shown in Update_Form. Generating this code using the Language Wizard is fairly straightforward.


Top of page

x
Procedure: How to Update a Record in a Data Source
  1. Create a new function in the Start procedure named UpdateFan.
  2. In the Procedure Editor, open UpdateFan.
  3. Make sure your cursor is between Case UpdateFan and EndCase and open the Language Wizard.
  4. In the initial Language Wizard window, select Update records in a data source and click Next.
  5. When the Language Wizard asks you how you would like to update records in a data source, select Update certain data source columns, one or more records at a time and click Next.
  6. Expand the fannames data source to see the segments and fields.
  7. In the list of available fields, select LASTNAME, FIRSTNAME, COMPANY, ADDRESS, CITY, STATE, ZIP, PHONE, EMAIL, TITLE, and ENROLLMENT_DATE (everything but SSN and USER).

    You must select individual fields that you want to update in a data source Unlike when you generated the ADD command using the Language Wizard. This gives you the flexibility to update just the fields that you want.

    You cannot update SSN because it is a key field.

    Notice that you can also select the CUSTOMER segment and move it over. The Language Wizard knows to move only updatable fields, as shown in the following image.

    Maintain Language Wizard dialog box

  8. Click Next.
  9. You now need to specify the record that will be updated in the fannames data source. This record corresponds to the current row of GetFanStack.

    Select the Stack radio button and select GetFanStack from the list of data source stacks in your procedure.

  10. Remove the 1 in the Starting from row box and type in the word FocIndex.
  11. Select the A specific number radio button and make sure that 1 is entered in the box, as shown in the following image.

    Mainitain Language Wizard dialog box

  12. Click Finish.

    The Language Wizard generates the following code:

    Update fannames.CUSTOMER.LASTNAME fannames.CUSTOMER.FIRSTNAME 
    fannames.CUSTOMER.COMPANY fannames.CUSTOMER.ADDRESS 
    fannames.CUSTOMER.CITY fannames.CUSTOMER.STATE fannames.CUSTOMER.ZIP 
    fannames.CUSTOMER.PHONE fannames.CUSTOMER.EMAIL 
    fannames.CUSTOMER.TITLE fannames.CUSTOMER.ENROLLMENT_DATE
    from GetFanStack(GetFanStack.FocIndex);

    For more information on the UPDATE command, see the Command Reference in the Maintain Language Reference manual.

  13. In Update_Form, double-click UpdateButton to open the Event Handler editor.
  14. Assign the UpdateFan function to the Click event for UpdateButton.

Top of page

x
Deleting a Record From a Data Source

How to:

Your next step is to create the code that will delete the fan shown in the Update/Delete form from the fannames data source. Generating this code using the Language Wizard is fairly straightforward.

However, think about the flow of forms in your application. After you delete the fan from the data source, you do not want to display the information about the fan in the Update_Form, so you should close the Update_Form.

Instead of closing using the Close form button in the Event Handler editor, you will use the Winform command to close the form. The Winform command enables you to manipulate forms at run time, including opening forms, closing forms, determining properties of forms and their controls, and setting properties of forms and controls.



x
Procedure: How to Delete a Record From a Data Source
  1. Create a new function in the Start procedure named DeleteFan.
  2. Open DeleteFan in the Procedure Editor.
  3. Make sure your cursor is between Case DeleteFan, EndCase. Open the Language Wizard.
  4. In the initial Language Wizard window, select Update records in a data source and click Next.
  5. When the Language Wizard asks you how you would like to update records in a data source, select Delete one or more records from the data source and click Next.
  6. Expand the fannames data source to see the segments and fields.
  7. Select the fields in the CUSTOMER segment of the fannames data source or the CUSTOMER segment itself. Notice that when you select one field, the rest of the fields are also copied. This is because when you delete a record from a data source, you want to delete the contents of all of the fields associated with that record.
  8. Click Next.
  9. You now need to specify the record that will be deleted from the fannames data source. This record corresponds to the current row of GetFanStack.

    Select the Stack radio button and select GetFanStack from the list of data source stacks in your procedure.

  10. Remove the 1 in the Starting from row box and type in the word FocIndex.
  11. Select the A specific number radio button and make sure that 1 is entered in the box, as shown in the following image.

    The Language Wizard generates the code

    Delete fannames.CUSTOMER.SSN from GetFanStack(GetFanStack.FocIndex) ;

    which means, Delete the record that corresponds to the current row of GetFanStack from the fannames data source. For more information on the DELETE command, see the Command Reference in the Maintain Language Reference manual.

    Mainitain Language Wizard dialog box

  12. Click Finish.
  13. You now need to insert the code that will close Update_Form. Make sure your cursor is between the code you just generated and EndCase and open the Language Wizard again.
  14. In the initial Language Wizard window, select Operate on a form and click Next.
  15. When the Language Wizard asks you what form operation you would like to perform, select Close a form and click Next.
  16. When the Language Wizard asks you to select a form to close, select Update_Form and click Finish.

    The Language Wizard generates the following syntax:

    Winform Close Update_Form;

    For more information on the Winform command, see the Command Reference in the Maintain Language Reference manual.

  17. In Update_Form, double-click DeleteButton to open the Event Handler editor, and assign DeleteFan to the Click event for DeleteButton.

Top of page

x
Deleting a Record From a Data Source Stack

How to:

After you close Update_Form, you return to the ShowFan form, which contains an HTML Table that displays the contents of GetFanStack. Unless you do something, the fan that you just deleted from the fannames data source will still be in GetFanStack and visible to the end user.

So, in addition to deleting the fan from fannames, you also have to delete the fan from GetFanStack.

Your first inclination might be to reload the data from fannames into GetFanStack. However, data source operations such as this one are often the bottlenecks in application design. Your application will run much more quickly if you simply delete the row from the stack, especially if your data source has a lot of records or resides on another computer).

Take a look at how to delete a row from a stack. In the following figure, you can see a diagram of what a stack looks like. The current row, the one you want to delete, is numbered FocIndex, and the number of rows in GetFanStack is FocCount.

You can leave the rows from 1 to FocIndex–1 alone, as shown in the following image.

GetFanStack example image

The rows from FocIndex+1 to FocCount each need to move up one. To do this, you will copy the contents of the FocIndex+1 row into the FocIndex row. This discards the data in the FocIndex row, but that is fine because you wanted to delete it. You also have two copies of the data in the FocIndex+1 row.

Next, copy the contents of the FocIndex+2 row into the FocIndex+1 row, thus overwriting the original copy of the data in the FocIndex+1 row (but you now have the copy in the FocIndex row). You also have two copies of the data in the FocIndex+2 row.

You continue copying rows until you reach the end of the stack and have two copies of the data in the FocCount, or last row. At this point, you redefine the FocCount of the stack to be FocCount–1, thus discarding the extra copy of the data in the FocCount row.

There is a special case when FocIndex is the last row of the stack. In this case, all you need to do is redefine both FocIndex and FocCount to be FocIndex–1 and FocCount–1, respectively, as shown in the following image.

GetFanStack example image



x
Procedure: How to Delete a Row From a Stack
  1. Create a variable in the Start procedure by right-clicking Start, clicking New in the shortcut menu, and then clicking Variable (Declare).
  2. In the New Variable dialog box, give your variable the name CNT.
  3. Click the ellipsis button Ellipsis button to open the Type Wizard. (The Type Wizard helps you assign a data type to your new column.)
  4. Leave the Built-in Type and Simple and Integer choices, but change the size to 2, as shown in the following image.

    Type Wizard dialog box

    Notice at the bottom of the dialog box, that the Type Wizard uses an abbreviation of I2 to represent your data type choice.

  5. Click OK to return to the New Variable dialog box.

    Notice that the Type Wizard has transferred the I2 to the Type box. If you know the WebFOCUS abbreviation for your data type, you can type it directly here, as shown in the following image. You will also notice, as you continue to work in a project, that WebFOCUS Maintain saves the data types you have applied and lists them.

    New Variable dialog box

  6. Click OK to close the New Variable dialog box.

    Note: If you double-click the CNT variable, you will see your new CNT variable listed under the $$Declarations line at the top of the Start procedure under the MAINTAIN command line.

  7. Open the DeleteFan function.
  8. Place your cursor between the line with the DELETE command and the line with the Winform command. This placement is important, because you want to delete the record from GetFanStack after you delete it from fannames, but before you redisplay GetFanStack.
  9. Enter the following code:
    IF GetFanStack.FocCount=GetFanStack.FocIndex THEN
      BEGIN
        COMPUTE GetFanStack.FocCount = GetFanStack.FocCount-1;
        COMPUTE GetFanStack.FocIndex = GetFanStack.FocIndex-1;
      ENDBEGIN
    ELSE
      BEGIN
        COMPUTE CNT = GetFanStack.FocIndex;
        Stack copy from GetFanStack(CNT+1) into GetFanStack(CNT) ;
        COMPUTE GetFanStack.FocCount = GetFanStack.FocCount-1;
      ENDBEGIN

    Note: You can use the Language Wizard to generate the STACK COPY line of code. You can also generate the syntax for any variables by dragging them from the Project Explorer. For example, to generate the syntax for GetFanStack.FocCount, expand GetFanStack in the Project Explorer and move to the bottom of the list of columns. You will see FocCount and FocIndex at the bottom. You can now drag them into the Procedure Editor.

    For the complete COPY command syntax, see the Command Reference chapter in the Maintain Language Reference manual.

  10. Close the Procedure Editor.
  11. Deploy and run your application. Try updating and deleting a fan in the fannames data source. Be sure to close the application when you are done by clicking the Exit button and closing the Developer Studio Viewer.

WebFOCUS