iAnywhere.com - Data manipulation: INSERT, UPDATE, and DELETE

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 » Understanding UltraLite Development with AppForge » Working with data using dynamic SQL

Data manipulation: INSERT, UPDATE, and DELETE

With UltraLite, you can perform SQL Data Manipulation Language operations. These operations are performed using the ExecuteStatement method, a member of the ULPreparedStatement class.

See ULPreparedStatement class.

It is important for applications to free up resources after using prepared statements by calling the Close method.

Using parameters in your prepared statements

Placeholders for parameters are identified using the ? character. For any INSERT, UPDATE, or DELETE, each ? is referenced according to its ordinal position in the prepared statement. For example, the first ? is referred to as parameter 1, and the second as parameter 2.

To INSERT a row

Declare a ULPreparedStatement object.

'MobileVB using VB6
Dim PrepStmt As ULPreparedStatement
'Crossfire using vb.net
Dim PrepStmt As UltraLiteAFLib.ULPreparedStatement
// Crossfire using C#
 ULPreparedStatement PrepStmt = null;

Assign an INSERT statement to your prepared statement object. In the following code, TableName and ColumnName are the names of a table and column.

'MobileVB using VB6
Set PrepStmt = Connection.PrepareStatement( _
    "INSERT INTO TableName(ColumnName) VALUES ( ? )")
'Crossfire using vb.net
PrepStmt = Connection.PrepareStatement( _
    "INSERT INTO TableName(ColumnName) VALUES( ? )")
// CrossFire using C#
try {
        PrepStmt = Connection.PrepareStatement("INSERT INTO ...", null);
} catch ( Exception ){
}
if (PrepStmt == null) // failed

Assign parameter values for the statement.
```
PrepStmt.SetStringParameter (1, "Bob")
```
Execute the statement and free resources after the command is completed.
```
PrepStmt.ExecuteStatement
PrepStmt.Close()
```

To UPDATE a row

Declare a ULPreparedStatement object.
```
Dim PrepStmt As ULPreparedStatement
```
Assign an UPDATE statement to your prepared statement object. In the following code, TableName and ColumnName are the names of a table and column.
```
Set PrepStmt = Connection.PrepareStatement( _
    "UPDATE TableName SET ColumnName = ? WHERE ID = ?")
```

Assign parameter values for the statement.

PrepStmt.SetStringParameter (1, "newvalue")
PrepStmt.SetStringParameter (2, "oldvalue")

Execute the statement and free resources after the command is completed.
```
PrepStmt.ExecuteStatement
PrepStmt.Close()
```

To DELETE a row

Declare a ULPreparedStatement object.

'MobileVB using VB6
Dim PrepStmt As ULPreparedStatement
'Crossfire using vb.net
Dim PrepStmt As UltraLiteAFLib.ULPreparedStatement

Assign a DELETE statement to your prepared statement object.

'MobileVB using VB6
Set PrepStmt = Connection.PrepareStatement( _ 
    "DELETE FROM customer WHERE ID = ?")
'Crossfire using vb.net
PrepStmt = Connection.PrepareStatement( _ 
    "DELETE FROM customer WHERE ID = ?")

Assign parameter values for the statement.

PrepStmt.SetStringParameter (1, "oldvalue")

Execute the statement and free resources after the command is completed.
```
PrepStmt.ExecuteStatement
PrepStmt.Close()
```