UltraLite for MobileVB User's Guide
  Tutorial: An UltraLite for MobileVB Application for Palm OS
    Lesson 3: Write connection, synchronization, and table code

Write code for data manipulation

The next step is to write code for data manipulation and navigation.

To open the table

1. Write code that initializes the table and moves to the first row.

   Add the following code to the Form_Load routine, just before the End Sub instruction:

   Set CustomerTable = Connection.GetTable("customer")
   CustomerTable.Open
   CustomerTable.MoveBeforeFirst
   If Err.Number <> ULSQLCode.ulSQLE_NOERROR Then
       MsgBox Err.Description
   End If

   This code assigns the CustomerTable variable and opens the table so data can be read or manipulated. The call to MoveBeforeFirst positions the application before the first row of data in the table - but note that it is not strictly speaking, required, because after you call open, you are already positioned before the first row. There are no rows in the table at the moment.

2. Create a new function called DisplayCurrentRow and implement it as shown below.

   Private Sub DisplayCurrentRow()
       If CustomerTable.RowCount = 0 Then
           txtFname.Text = ""
           txtLname.Text = ""
           txtCity.Text = ""
           txtPhone.Text = ""
           lblID.Caption = ""
       Else
           lblID.Caption = _
           CustomerTable.Column("Id").StringValue
           txtFname.Text = _
           CustomerTable.Column("Fname").StringValue
           txtLname.Text = _
           CustomerTable.Column("Lname").StringValue
           If CustomerTable.Column("City").IsNull Then
               txtCity.text=""
           Else
               txtCity.Text = _
               CustomerTable.Column("City").StringValue
           End If
           If CustomerTable.Column("City").IsNull Then
               txtcity.Text = ""
           Else
               txtphone.Text = _
               CustomerTable.Column("Phone").StringValue
           End If
       End If
   End Sub

   If the table has no rows, the application displays empty controls. Otherwise, it displays the values stored in each of the columns of the current row of the database.

3. Call this function from the Form's Activate function.

   Private Sub Form_Activate()
       DisplayCurrentRow
   End Sub

   This call ensures the fields get updated when the application starts.

   At this stage you may wish to run the application to check that you have entered the code correctly. As there are no rows in the table, the controls are all empty.

To insert rows into the table

1. Implement the code for the Insert button.

   Add the following routine to the form:

   Private Sub btnInsert_Click()
       Dim fname As String
       Dim lname As String
       Dim city As String
       Dim phone As String
   
       fname = txtFname.Text
       lname = txtLname.Text
       city = txtCity.Text
       phone = txtPhone.Text
   
       On Error GoTo InsertError
       CustomerTable.InsertBegin
       CustomerTable.Column("Fname").StringValue = _
            fname
       CustomerTable.Column("Lname").StringValue = _
            lname
       If Len(city) > 0 Then
           CustomerTable.Column("City").StringValue = _
            city
       End If
       If Len(phone) > 0 Then
          CustomerTable.Column("Phone").StringValue = _
          phone
       End If
       CustomerTable.Insert
       CustomerTable.MoveLast
       DisplayCurrentRow
       Exit Sub
   
   InsertError:
       MsgBox "Error:  " & CStr(Err.Description)
   End Sub

   The call to InsertBegin puts the application into insert mode and sets all the values in the row to their defaults (for example, the ID column receives the next autoincrement value). The column values are set and then the new row is inserted. Note that if an error occurs during the insert, a message box will display the error number.

2. Run the application.

   After the initial message box, the form is displayed.

   • Enter a first name of Jane in the top text box and a last name of Doe in the second.

   • Click the Insert button. 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 autoincremented value of the ID column that UltraLite assigned to the row.

   • Enter a first name of John in the top text box and a last name of Smith in the second.

   • Click Insert to add this row to the table.

   • Click End to end the program.

With two rows in the table, it is now time to implement the code to scroll through the rows and display each.

To move through the rows of the table

1. Implement the code for the Next and Previous buttons:

   Add the following routines to the form:

   Private Sub btnNext_Click()
       If Not CustomerTable.MoveNext Then
           CustomerTable.MoveLast
       End If
       DisplayCurrentRow
   End Sub

   Private Sub btnPrevious_Click()
       If Not CustomerTable.MovePrevious Then
           CustomerTable.MoveFirst
       End If
       DisplayCurrentRow
   End Sub

2. Run the application.

   When the form is first displayed, the controls are empty as the current position is before the first row.

   After the form is displayed, click Next and Previous to move through the rows of the table.

The next step is to modify the data in a row by updating or deleting it.

To update and delete rows in the table

1. Implement the code for the Update button.

   Add the following routine to the form:

   Private Sub btnUpdate_Click()
       Dim fname As String
       Dim lname As String
       Dim city As String
       Dim phone As String
   
       fname = txtFname.Text
       lname = txtLname.Text
       city = txtCity.Text
       phone = txtPhone.Text
       On Error GoTo UpdateError
       CustomerTable.UpdateBegin
       CustomerTable.Column("Fname").StringValue = _
           fname
       CustomerTable.Column("Lname").StringValue = _
           lname
       If Len(city) > 0 Then
           CustomerTable.Column("City").StringValue = _
           city
       Else
           CustomerTable.Column("City").SetNull
       End If
       If Len(phone) > 0 Then
          CustomerTable.Column("Phone").StringValue = _
           phone
       End If
       CustomerTable.Update
       DisplayCurrentRow
       Exit Sub
   
   UpdateError:
       MsgBox "Error:  " & CStr(Err.Description)
   End Sub

   The call to UpdateBegin puts the application into update mode. The column values are updated and then the row itself is updated with a call to Update.

2. Implement the code for the Delete button.

   Add the following routine to the form:

   Private Sub btnDelete_Click()
       If CustomerTable.RowCount = 0 Then
           Exit Sub
       End If
       CustomerTable.Delete
       CustomerTable.MoveRelative 0
       DisplayCurrentRow
   End Sub

   The call to Delete deletes the current row on which the application is positioned.

3. Run the application.

   The data manipulation and display part of the application is now complete. Try inserting, updating, and deleting rows. Also, use the Next and Previous buttons to move through the rows. Check the label to see which row you are on.