iAnywhere.com - Lesson 5: Add inserts to your application

Click here to view and discuss this page in DocCommentXchange. In the future, you will be sent there automatically.

SQL Anywhere 10.0.1 » UltraLite - M-Business Anywhere Programming » Tutorial: A Sample Application for M-Business Anywhere

Lesson 5: Add inserts to your application

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

Open the table

Write code to initialize the CustomerTable that represents the Customer table in your database.

Add 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() );
}
```
Add variables to move data between the database and the web form.

Add the following declarations to the top of tutorial.js, before the Connect function.
```
var Cust_FName = "";
var Cust_LName = "";
var Cust_City = "";
var Cust_Phone = "";
var Cust_Id = "";
```

Create procedures to fetch and display customer data.

Add the following function to tutorial.js, immediately after the Connect function. It fetches the current row of the customer and also ensures that NULL columns display as empty strings:

function FetchCurrentRow()
{
  var id;
  if( CustomerTable.getRowCount() == 0 ) {
    Cust_FName = "";
    Cust_LName = "";
    Cust_City = "";
    Cust_Phone = "";
    Cust_Id = "";
  } else {
    id = CustomerTable.schema.getColumnID("FName");
    Cust_FName = CustomerTable.getString(id);
    id = CustomerTable.schema.getColumnID("LName");
    Cust_LName = CustomerTable.getString(id);
    id = CustomerTable.schema.getColumnID("city");
    if( CustomerTable.isNull(id) ) {
      Cust_City = "";
    } else {
      Cust_City = CustomerTable.getString(id);
    }
    id = CustomerTable.schema.getColumnID("phone");
    if( CustomerTable.isNull(id) ) {
      Cust_Phone = "";
    } else {
      Cust_Phone = CustomerTable.getString(id);
    }

    id = CustomerTable.schema.getColumnID("id");
    Cust_Id = CustomerTable.getString(id);
  }
}

Add the following JavaScript to main.html, immediately before the closing </body> tag. DisplayCurrentRow 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 DisplayCurrentRow()
{
  FetchCurrentRow();
  document.custForm.fname.value = Cust_FName;
  document.custForm.lname.value = Cust_LName;
  document.custForm.city.value = Cust_City;
  document.custForm.phone.value = Cust_Phone;
  document.custForm.custid.value = Cust_Id;
}
function FetchForm()
{
  Cust_FName = document.custForm.fname.value;
  Cust_LName = document.custForm.lname.value;
  Cust_City = document.custForm.city.value;
  Cust_Phone = document.custForm.phone.value;
}
</script>

Call DisplayCurrentRow when the application is loaded.

Modify the body tag at the top of main.html as follows:
```
<body onload="Connect(); DisplayCurrentRow();">
```

Although there is no data in your database and no rows are displayed, this is a good place to synchronize M-Business Client to ensure that you have not introduced bugs.

Add code to insert rows

Write code to implement the Insert button.

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, immediately after FetchCurrentRow:

function Insert()
{
  var id;

  try {
    CustomerTable.insertBegin();
    id = CustomerTable.schema.getColumnID("FName");
    CustomerTable.setString(id, Cust_FName);
    id = CustomerTable.schema.getColumnID("LName");
    CustomerTable.setString(id, Cust_LName);
    id = CustomerTable.schema.getColumnID("city");
    if( Cust_City.length > 0 ) {
      CustomerTable.setString(id, Cust_City);
    }
    id = CustomerTable.schema.getColumnID("phone");
    if( Cust_Phone.length > 0 ) {
      CustomerTable.setString(id, Cust_Phone);
    }
    CustomerTable.insert();
    CustomerTable.moveLast();
  } catch( ex ) {
    alert( "Insert error: " + ex.getMessage() );
  }
}

Add the following function to main.html, immediately after the FetchForm function:

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

You can now test your application.

Test your application

Synchronize your M-Business Client.
Run the application.

After an initial message box, the form is displayed.
Insert two rows into the table:
1. Enter a first name of Jane in the first text box and a last name of Doe in the second text box. Click Insert.
  
  A row is added to the table with these values. The application moves to the last row of the table and displays the row. The label displays the automatically incremented value of the ID column that UltraLite assigned to the row.
2. Enter a first name of John in the first text box and a last name of Smith in the second. Click Insert.

The next step is to add navigation buttons