UltraLite ActiveX User's Guide
  Tutorial: An UltraLite Application for PocketPC
    Lesson 3: Write the sample code

Write code for navigation and data manipulation

The following procedures implement data manipulation and navigation.

To open the table

1. Write code to initialize the table and move to the first row.

   This code assigns the customer table in the database to the CustomerTable variable. The call to Open opens the table so that the table data can be read or manipulated. It also positions the application before the first row in the table.

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

   Set CustomerTable = Connection.GetTable("customer")
   CustomerTable.Open

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

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

   Private Sub DisplayCurrentRow()
     If CustomerTable.RowCount = 0 Then
       txtFname.Text = ""
       txtLname.Text = ""
       txtCity.Text = ""
       txtPhone.Text = ""
       lblID.Caption = ""
     Else
       lblID.Caption = CustomerTable.Columns("ID").Value
       txtFname.Text = CustomerTable.Columns("Fname").Value
       txtLname.Text = CustomerTable.Columns("Lname").Value
       txtCity.Text = CustomerTable.Columns("City").Value
       txtPhone.Text = CustomerTable.Columns("Phone").Value
     End If
   End Sub

3. Call DisplayCurrentRow from the Form_Activate procedure. This call ensures that the fields get updated when the application starts.

   Private Sub Form_Activate()
       DisplayCurrentRow
   End Sub

   At this stage you may want 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. Write code to implement the Insert button.

   Add the following procedure 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
     CustomerTable.InsertBegin
     CustomerTable.Columns("Fname").Value = fname
     CustomerTable.Columns("Lname").Value = lname
     If Len(city) > 0 Then
       CustomerTable.Columns("City").Value = city
     End If
     If Len(phone) > 0 Then
        CustomerTable.Columns("Phone").Value = phone
     End If
     CustomerTable.Insert
     CustomerTable.MoveLast
     DisplayCurrentRow
   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.

2. Run the application.

   After an initial message box, the form is displayed.

3. Insert two rows into the database.

   • Enter a first name of Jane in the first text box and a last name of Doe in the second. 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.

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

4. Click OK to end the program.

To move through the rows of the table

1. Write code to implement the Next and Previous buttons.

   Add the following procedures 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.

To update and delete rows in the table

1. Write code to implement the Update button.

   In the code below, 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.

   Add the following procedure 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
       CustomerTable.UpdateBegin
       CustomerTable.Columns("Fname").Value = _
           fname
       CustomerTable.Columns("Lname").Value = _
           lname
       If Len(city) > 0 Then
           CustomerTable.Columns("City").Value = _
           city
       End If
       If Len(phone) > 0 Then
          CustomerTable.Columns("Phone").Value = _
           phone
       End If
       CustomerTable.Update
       DisplayCurrentRow
       Exit Sub
   End Sub

2. Write code to implement the Delete button.

   In the code below, the call to Delete deletes the current row on which the application is positioned.

   Add the following procedure to the form:

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

3. Run the application.