UltraLite for MobileVB User's Guide
  Tutorial: Using Dynamic SQL in an UltraLite Application for PocketPC
    Lesson 3: Write connection, synchronization, and table code

Write code for 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:

   On Error GoTo 0
   Set MyPrepStmt = _
     MyConnection.PrepareStatement("SELECT ID, Name FROM Names")
   Set MyResultSet = MyPrepStmt.ExecuteQuery
   If MyResultSet.MoveFirst then
      DisplayCurrentRow
   End If

   Setting the prepared statement gives you a result set from a SELECT statement.

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

   Private Sub DisplayCurrentRow()
       If MyResultSet.RowCount = 0 Then
           lblID.Caption = ""
           txtName.Text = ""
       Else
           MyResultSet.MoveRelative (0)
           lblID.Caption = MyResultSet.GetInteger(1)
           txtName.Text = MyResultSet.GetString(2)
       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. Note how the type for each Get statement is specific to the column data type.

   The MoveRelative( 0 ) method is called to refresh the contents of the current buffer from the result set, so that the effects of any data modification are included.

3. 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. Implement the code for the Insert button.

   Add the following procedure to the form:

   Private Sub btnInsert_Click()
     Dim InsertStmt As ULPreparedStatement
     Set InsertStmt = MyConnection.PrepareStatement( _
          "INSERT INTO names(name) VALUES(?)")
     InsertStmt.SetStringParameter 1, txtName.Text
     InsertStmt.ExecuteStatement
   End Sub

   The column values are set and then the new row is inserted.

2. Run the application.

   After the initial message box, the form is displayed.

   • Enter a name in the text box.

   • Click the Insert button. A row is added to the table with this value.

   • Enter another name in the text box.

   • Click Insert to add this row to the table.

   • Click End to end the program.

The following procedure implements the code to scroll through and display the rows.

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()
       MyResultSet.MoveNext
       DisplayCurrentRow
   End Sub
   
   Private Sub btnPrevious_Click()
       MyResultSet.MovePrevious
       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.

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 MyUpdateStmt as ULPreparedStatement
     Set MyUpdateStmt = MyConnection.PrepareStatement("UPDATE Names SET Name = ? WHERE ID = ?")
     MyUpdateStmt.SetStringParameter 1, txtName.Text
     MyUpdateStmt.SetIntegerParameter 2, CLng(lblID.Caption)
     MyUpdateStmt.ExecuteStatement
   
     MsgBox "Row updated"
     DisplayCurrentRow
   End Sub

   The column values are updated.

2. Implement the code for the Delete button.

   Add the following procedure to the form:

   Private Sub btnDelete_Click()
     Dim MyDeleteStmt As ULPreparedStatement
     Set MyDeleteStmt = MyConnection.PrepareStatement( _
       "DELETE FROM Names WHERE ID = ?")
     MyDeleteStmt.SetIntegerParameter 1, CLng( lblID.Caption )
     MyDeleteStmt.ExecuteStatement
     DisplayCurrentRow
   End Sub

   The call to Delete deletes the current row according to the value set in the WHERE clause.

3. Run the application.

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