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

Write code for navigation and data manipulation

The following procedure implements 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 event, 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 customer table to CustomerTable 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. This call is not necessarily required because the call to open positions the application before the first row. There are currently no rows in the table.

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 want to run the application to verify 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 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
   
       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.

The following procedure implements 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 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.

   Click Next and Previous to move through the rows of the table.

The following procedure modifies 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 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
       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 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

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

3. Run the application.

   The data manipulation and display portion of the application is now complete.