UltraLite ActiveX User's Guide
Tutorial: Using Dynamic SQL in an UltraLite Application for PocketPC
Lesson 3: Write the eMbedded Visual Basic sample code
The following procedures implement data manipulation and navigation.
To open the table
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
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
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
Run the application.
After an initial message box, the form is displayed.
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.
Click OK to end the program.
To move through the rows of the table
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
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.
To update and delete rows in the table
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
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
Run the application.
NoteYou 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. |