Contents Index Write code to connect to your database Write code to synchronize

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.

  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.

Note 
You can now run this application as a standalone application without SQL Anywhere Studio. To synchronize your UltraLite database with an Adaptive Server Anywhere database, you can complete the remainder of this lesson.

Contents Index Write code to connect to your database Write code to synchronize