Inserting, updating, and deleting rows

UltraLite ActiveX User's Guide
Understanding UltraLite ActiveX Development
Accessing and manipulating data using the table API

Inserting, updating, and deleting rows

UltraLite exposes the rows in a table to your application one at a time. The ULTable object has a current position, which may be on a row, before the first row, or after the last row of the table.

When your application changes its row, UltraLite makes a copy of the row in a buffer. Any operations to get or set values affect only the copy of data in this buffer. They do not affect the data in the database. For example, the following statement changes the value of the ID column in the buffer to 3.

' eMbedded Visual Basic
colID.Value = 3

// JScript
colID.Value = 3;

Using UltraLite modes

UltraLite uses the values in the buffer for a different purpose, depending on the kind of operation you are carrying out. UltraLite has four different modes of operation, in addition to the default mode.

Insert mode The data in the buffer is added to the table as a new row when the ULTable.Insert method is called.
Update mode The data in the buffer replaces the current row when the ULTable.Update method is called.
Find mode The data in the buffer is used to locate rows when one of the ULTable.Find methods is called.
Lookup mode The data in the buffer is used to locate rows when one of the ULTable.Lookup methods is called.

To update a row

Move to the row you wish to update.

You can move to a row by scrolling through the table or by searching using Find and Lookup methods.
Enter Update mode.

For example, the following instruction enters Update mode on the table tCustomer.
```
' eMbedded Visual Basic
tCustomer.UpdateBegin
```
```
// JScript
tCustomer.UpdateBegin();
```
Set the new values for the row to be updated.

For example, the following instruction sets the new value to Elizabeth.
```
' eMbedded Visual Basic
ColFirstName.Value = "Elizabeth"
```
```
// JScript
ColFirstName.Value = "Elizabeth";
```

Execute the Update.

'eMbedded Visual Basic
tCustomer.Update

// JScript
tCustomer.Update();

After the update operation, the current row is the row that was just updated. If you changed the value of a column in the index specified when the ULTable object was opened, there are some subtleties to the positioning.

By default, UltraLite operates in AutoCommit mode, so that the update is immediately applied to the row in permanent storage. If you have disabled AutoCommit mode, the update is not applied until you execute a commit operation. For more information, see Transaction processing in UltraLite.

Caution Do not update the primary key of a row: delete the row and add a new row instead.

Inserting rows

The steps to insert a row are very similar to those for updating rows, except that there is no need to locate any particular row in the table before carrying out the insert operation. Rows are automatically sorted by the index specified when opening the table.

To insert a row

Enter Insert mode.

For example, the following instruction enters Insert mode on the table CustomerTable.
```
' eMbedded Visual Basic
CustomerTable.InsertBegin
```
```
// JScript
CustomerTable.InsertBegin();
```
Set the values for the new row.

If you do not set a value for one of the columns, and that column has a default, the default value is used. If the column has no default, NULL is used. If the column does not allow NULL, the following defaults are used:
- For numeric columns, zero.
- For character columns, an empty string.
To set a value to NULL explicitly, use the setNull method.
```
' eMbedded Visual Basic
CustomerTable.Columns("Fname").Value = fname
CustomerTable.Columns("Lname").Value = lname
```
```
// JScript
CustomerTable.Columns("Fname").Value = fname;
CustomerTable.Columns("Lname").Value = lname;
```
Execute the insertion.

The inserted row is permanently saved to the database when a Commit is carried out. In AutoCommit mode, a Commit is carried out as part of the Insert method.
```
'eMbedded Visual Basic
CustomerTable.Insert
```
```
// JScript
CustomerTable.Insert();
```

Deleting rows

There is no delete mode corresponding to the insert or update modes.

The following procedure deletes a row.

To delete a row

Move to the row you wish to delete.

Execute the deletion.

'eMbedded Visual Basic
tCustomer.Delete

// JScript
tCustomer.Delete();