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

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("ULCustomer")
   CustomerTable.Open
   If Err.Number <> ULSQLCode.ulSQLE_NOERROR Then
       MsgBox Err.Description
   End If
   CustomerTable.MoveFirst

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
       txtname.Text = ""
       lblID.Caption = ""
     Else
       txtname.Text = CustomerTable.Column("cust_name").StringValue
       lblID.Caption = CustomerTable.Column("cust_id").StringValue
     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

To insert rows into the table

1. Write code to implement the Insert button.

   In the following procedure, 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.

   Add the following procedure to the form.

   Private Sub btnInsert_Click()
       
       On Error GoTo InsertError
       CustomerTable.InsertBegin
       CustomerTable.Column("cust_name").StringValue = txtname.Text
       
       CustomerTable.Insert
       CustomerTable.MoveLast
       DisplayCurrentRow
       Exit Sub
   
   InsertError:
       MsgBox "Error:  " & CStr(Err.Description)
   End Sub

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 End 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()
           
       On Error GoTo UpdateError
       
       CustomerTable.UpdateBegin
       CustomerTable.Column("cust_name").StringValue = txtname.Text
       CustomerTable.Update
       DisplayCurrentRow
       Exit Sub
   
   UpdateError:
       MsgBox "Error:  " & CStr(Err.Description)
   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
       txtname.Text = ""
       lblID.Caption = ""
       DisplayCurrentRow
   End Sub

3. Run the application.