Lesson 5: Adding data manipulation and navigation

This lesson describes how to fill out your application with data manipulation and navigation logic.

 Initialize the table
  1. Initialize the CustomerTable representing the Customer table in your database by adding the following code to the end of the Connect function in tutorial.js:

    try {
      CustomerTable = Connection.getTable( "customer", null );
      CustomerTable.open();
    } catch( ex3 ) {
      alert("Error: " + ex3.getMessage() );
    }
  2. Add variables to move data between the database and the web form.

    Add the following variable declarations to the top of tutorial.js for the customer data:

    var GivenName = "";
    var Surname = "";
    var Street = "";
    var City = "";
    var Phone = "";
    var ID = "";
  3. Create functions to fetch and display customer data.

    Add the following function to tutorial.js. This function fetches the current row of the customer data and also ensures that NULL columns are displayed as empty strings:



    function Fetch() 
    {
        if( CustomerTable.getRowCount() == 0 || CustomerTable.isBOF() ) {
     GivenName = "";
     Surname = "";
     Street = "";
     City = "";
     Phone = "";
     ID = "";
     return;
        } 
        ID = CustomerTable.getString( CustomerTable.schema.getColumnID( "ID" ) );
        GivenName = CustomerTable.getString( CustomerTable.schema.getColumnID( "GivenName" ) );
        Surname = CustomerTable.getString( CustomerTable.schema.getColumnID( "Surname" ) );
        Street = CustomerTable.getString( CustomerTable.schema.getColumnID( "Street" ) );
        if( CustomerTable.isNull( CustomerTable.schema.getColumnID( "City" ) ) ) {
     City = "";
        } else {
     City = CustomerTable.getString( CustomerTable.schema.getColumnID( "City" ) );
        }
        if( CustomerTable.isNull( CustomerTable.schema.getColumnID( "Phone" ) ) ) {
     Phone = "";
        } else {
     Phone = CustomerTable.getString( CustomerTable.schema.getColumnID( "Phone" ) );
        }
    }

    Add the following to main.htm immediately after the <script> tag. DisplayRow takes the values from the database and displays them in the web form. FetchForm takes the current values in the web form and makes them available to the database code.



    <script>
    function DisplayRow() {
        Fetch();
        document.form.ID.value = ID;
        document.form.GivenName.value = GivenName;
        document.form.Surname.value = Surname;
        document.form.Street.value = Street;
        document.form.City.value = City;
        document.form.Phone.value = Phone;
    }
    
    function FetchForm() {
        GivenName = document.form.GivenName.value;
        Surname = document.form.Surname.value;
        Street = document.form.Street.value;
        City = document.form.City.value;
        Phone = document.form.Phone.value;
    }
    </script>
  4. Call DisplayRow to display the current row when the application is loaded. Modify the body tag at the top of main.htm as follows:

    <body onload="Connect(); DisplayRow();">

Although there is no data in the tutorial database up to this point, this is a good place to synchronize the channel to ensure that the application is running properly.

 Add code to insert rows
  • Create functions to insert customer data.

    In the following procedure, the call to insertBegin puts the application into insert mode and sets all values in the current row to their defaults. For example, the ID column receives the next autoincrement value. The column values are set and the new row is inserted.

    Add the following function to tutorial.js:



    function Insert()
    {
        try {
     CustomerTable.insertBegin();
     CustomerTable.setString( CustomerTable.schema.getColumnID( "GivenName" ), GivenName );
     CustomerTable.setString( CustomerTable.schema.getColumnID( "Surname" ), Surname );
     CustomerTable.setString( CustomerTable.schema.getColumnID( "Street" ), Street );
     if( City.length > 0 ) {
         CustomerTable.setString( CustomerTable.schema.getColumnID( "City" ), City );
     }
     if( Phone.length > 0 ) {
         CustomerTable.setString( CustomerTable.schema.getColumnID( "Phone" ), Phone );
     }
     CustomerTable.insert();
     CustomerTable.moveLast();
        } catch( ex ) {
     alert( "Error: cannot insert row: " + ex.Message() );
        }
    }

    Add the following function to main.htm after the FetchForm function and before the </script> tag:

    function ClickInsert()
    {
        FetchForm();
        Insert();
        DisplayRow();
    }