UltraLite ActiveX User's Guide
  Tutorial: Using Dynamic SQL in an UltraLite Application for PocketPC
    Lesson 3: Write the eMbedded Visual Basic sample code

Write code for navigation and data manipulation

The following procedures implement data manipulation and navigation.

To open the table

1. Create a new function called DisplayCurrentRow that initializes the table and moves to the first row.

   This code creates a ULPreparedStatement, executes the statement on the database, and moves to the first row of the result set. 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.

   Private Sub DisplayCurrentRow()
     If MyResultSet.RowCount <> 0 Then
         lblID.Caption = MyResultSet.Value(1)
         txtName.Text = MyResultSet.Value(2)
     Else
         lblID.Caption = ""
         txtName.Text = ""
     End If
   End Sub

2. Call DisplayCurrentRow from the Form_Activate event. 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 PrepStmt As ULPreparedStatement
     If txtName.Text <> "" Then
       Set PrepStmt = Connection.PrepareStatement("INSERT INTO names(name) values(?)")
       PrepStmt.SetParameter 1, txtName.Text
       PrepStmt.ExecuteStatement
       MyResultSet.MoveRelative(0)
       MyResultSet.MoveLast
       DisplayCurrentRow
     Else
       MsgBox "Enter a name to insert in the database"
     End If
   End Sub

2. Run the application.

   After an initial message box, the form is displayed.

3. Insert two rows into the database.

   • Enter the name Jane in the text box. Click Insert.

   • A row is added to the table with this 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 the name John in the text box. 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 btnPrevious_Click()
     If Not MyResultSet.MovePrevious Then
       MyResultSet.MoveFirst
     End If
     DisplayCurrentRow
   End Sub
   Private Sub btnNext_Click()
     If Not MyResultSet.MoveNext Then
       MyResultSet.MoveLast
     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.

3. 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.

   Add the following procedure to the form:

   Private Sub btnUpdate_Click()
     Dim PrepStmt As ULPreparedStatement
     Dim ID As Integer
     Dim NewName As String
     ID = lblID.Caption
     NewName = txtName.Text
     Set PrepStmt = Connection.PrepareStatement("UPDATE names SET name = (?) WHERE id = (?)")
     PrepStmt.SetParameter 1, NewName
     PrepStmt.SetParameter 2, ID
     PrepStmt.ExecuteStatement
     MyResultSet.MoveRelative(0)
     DisplayCurrentRow
     MsgBox "Row updated"
   End Sub

2. Write code to implement the Delete button.

   Add the following procedure to the form:

   Private Sub btnDelete_Click()
     Dim PrepStmt As ULPreparedStatement
     Dim ID As Integer
     ID = lblID.Caption
     Set PrepStmt = Connection.PrepareStatement("DELETE FROM names WHERE id = (?)")
     PrepStmt.SetParameter 1, ID
     PrepStmt.ExecuteStatement
     MyResultSet.MoveRelative(0)
     Call btnPrevious_Click
     MsgBox "Row deleted"
   End Sub

3. Run the application.