iAnywhere.com - Write code for navigation and data manipulation

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

SQL Anywhere 10.0.1 » UltraLite - AppForge Programming » Tutorial: A Sample Application for AppForge Crossfire » Lesson 3: Write the sample code

Write code for navigation and data manipulation

The following procedures implement data manipulation and navigation. The code uses the Table API, which provides methods for moving through and changing the rows of a table, one row at a time. For more complex applications, UltraLite provides an implementation of SQL.

To open the table

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 Form1_Load event, just before the End Sub instruction.
```
Try
   CustomerTable = Connection.GetTable("Customer")
   CustomerTable.Open()
Catch
   If Err.Number <> UltraLiteAFLib.ULSQLCode.ulSQLE_NOERROR _
   Then
      MsgBox(Err.Description)
   End If
End Try
```

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
    txtFname.Text = ""
    txtLname.Text = ""
    txtCity.Text = ""
    txtPhone.Text = ""
    lblID.Caption = ""
  Else
    lblID.Caption = _
         CustomerTable.Column("ID").StringValue
    txtFname.Text = _
         CustomerTable.Column("FName").StringValue
    txtLname.Text = _
         CustomerTable.Column("LName").StringValue
    If CustomerTable.Column ("City").IsNull Then
      txtCity.text =""
    Else
      txtCity.Text = _
      CustomerTable.Column("City").StringValue
    End If
    If CustomerTable.Column("Phone").IsNull Then
      txtphone.Text = ""
    Else
      txtphone.Text = _
      CustomerTable.Column("Phone").StringValue
    End If
  End If
End Sub

Call DisplayCurrentRow from the Form's Activated event. This call ensures that the fields get updated when the application starts.
```
DisplayCurrentRow
```

To insert rows into the table

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 Click event of the Insert button (btnInsert).

Dim fname As String
Dim lname As String
Dim city As String
Dim phone As String

fname = txtFname.Text
lname = txtLname.Text
city = txtCity.Text
phone = txtPhone.Text

Try
    CustomerTable.InsertBegin
    CustomerTable.Column("FName").StringValue = _
      fname
    CustomerTable.Column("LName").StringValue = _
      lname
    If Len(city) > 0 Then
        CustomerTable.Column("City").StringValue = _
      city
    End If
    If Len(phone) > 0 Then
       CustomerTable.Column("Phone").StringValue = _
       phone
    End If
    CustomerTable.Insert
    CustomerTable.MoveLast
    DisplayCurrentRow
    Exit Sub
Catch
    MsgBox "Error:  " & CStr(Err.Description)
End Try

Run the application.

After an initial message box, the form is displayed.
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.
Click End to end the program.

To move through the rows of the table

Write code to implement the Next and Previous buttons.

Add the following code to the Click event of the Next button (btnNext).
```
If Not CustomerTable.MoveNext Then
    CustomerTable.MoveLast
End If
DisplayCurrentRow
```
Add the following code to the Click event of the Previous button (btnPrevious).
```
If Not CustomerTable.MovePrevious Then
    CustomerTable.MoveFirst
End If
DisplayCurrentRow
```
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.

At this stage you can enter data and scroll through the rows of the table.

To update and delete rows in the table

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 code to the Click event of the Update button (btnUpdate):

Dim fname As String
Dim lname As String
Dim city As String
Dim phone As String

fname = txtFname.Text
lname = txtLname.Text
city = txtCity.Text
phone = txtPhone.Text
Try
    CustomerTable.UpdateBegin
    CustomerTable.Column("FName").StringValue = fname
    CustomerTable.Column("LName").StringValue = lname
    If Len(city) > 0 Then
        CustomerTable.Column("City").StringValue = city
    Else
        CustomerTable.Column("City").SetNull
    End If
    If Len(phone) > 0 Then
       CustomerTable.Column("Phone").StringValue = phone
    End If
    CustomerTable.Update
    DisplayCurrentRow
    Exit Sub
Catch
    MsgBox "Error:  " & CStr(Err.Description)
End Try

Write code to implement the Delete button.

In the code below, the call to Delete deletes the current row (the application displays the row data at the current position).

Add the following code to the Click event of the Delete button (btnDelete):
```
If CustomerTable.RowCount = 0 Then
    Exit Sub
End If
CustomerTable.Delete
CustomerTable.MoveRelative 0
DisplayCurrentRow
```
Run the application.