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

Working with data using dynamic SQL Next Page

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
  1. 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;
    
  2. 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
  3. Assign parameter values for the statement.

    PrepStmt.SetStringParameter (1, "Bob")
  4. Execute the statement and free resources after the command is completed.

    PrepStmt.ExecuteStatement
    PrepStmt.Close()
    
To UPDATE a row
  1. Declare a ULPreparedStatement object.

    Dim PrepStmt As ULPreparedStatement
  2. 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 = ?")
  3. Assign parameter values for the statement.

    PrepStmt.SetStringParameter (1, "newvalue")
    PrepStmt.SetStringParameter (2, "oldvalue")
  4. Execute the statement and free resources after the command is completed.

    PrepStmt.ExecuteStatement
    PrepStmt.Close()
To DELETE a row
  1. Declare a ULPreparedStatement object.

    'MobileVB using VB6
    Dim PrepStmt As ULPreparedStatement
    'Crossfire using vb.net
    Dim PrepStmt As UltraLiteAFLib.ULPreparedStatement
  2. 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 = ?")
  3. Assign parameter values for the statement.

    PrepStmt.SetStringParameter (1, "oldvalue")
  4. Execute the statement and free resources after the command is completed.

    PrepStmt.ExecuteStatement
    PrepStmt.Close()