Execute Operations

This document details the data modification operations of the cDataBase class, including INSERT, UPDATE, DELETE, etc.

---

Table of Contents

• Exec Method
• INSERT Operations
• UPDATE Operations
• DELETE Operations
• Get Affected Rows
• Get Last Insert ID
• Batch Operations

---

Exec Method

Basic Syntax

The Exec method executes non-query SQL statements like INSERT, UPDATE, DELETE.

Syntax

Function Exec(Optional RecordsAffected, Optional Options As Long = -1) As Boolean

Parameters

Parameter	Type	Description
RecordsAffected	Variant	Returns number of affected rows (optional)
Options	Long	Execution options (optional, default -1)

Return Value

• True - Execution successful
• False - Execution failed (view error via LastErr)

Example

' Execute INSERT statement
If db.Sql("INSERT INTO users (name, age) VALUES ('John', 25)").Exec Then
    Debug.Print "Insert successful"
Else
    Debug.Print "Insert failed: " & db.LastErr
End If

---

INSERT Operations

Basic Insert

' Insert single record
If db.Sql("INSERT INTO users (name, age, email) VALUES ('John', 25, 'john@example.com')").Exec Then
    Debug.Print "Insert successful"
End If

Insert Multiple Records

' Insert multiple using VALUES clause
Dim sSql As String
sSql = "INSERT INTO users (name, age) VALUES " & _
       "('John', 25), " & _
       "('Mary', 30), " & _
       "('Bob', 28)"
If db.Sql(sSql).Exec Then
    Debug.Print "Batch insert successful"
End If

Using Parameterized Insert

' Use parameterized query (recommended, prevents SQL injection)
If db.Sql("INSERT INTO users (name, age, email) VALUES (?, ?, ?)") _
    .Param("name", "John", VBMAN.adVarWChar) _
    .Param("age", 25, VBMAN.adInteger) _
    .Param("email", "john@example.com", VBMAN.adVarWChar) _
    .ExecParam Then

    Debug.Print "Insert successful, ID: " & db.LastInsertId
End If

INSERT SELECT

' Insert data from other table
If db.Sql("INSERT INTO users_backup SELECT * FROM users WHERE age > 30").Exec Then
    Debug.Print "Data copy successful"
End If

---

UPDATE Operations

Basic Update

' Update single record
If db.Sql("UPDATE users SET age = 26 WHERE id = 1").Exec Then
    Debug.Print "Update successful"
End If

Update Multiple Records

' Batch update
If db.Sql("UPDATE users SET status = 'active' WHERE age > 18").Exec Then
    Debug.Print "Batch update successful"
End If

Using Parameterized Update

' Use parameterized query
If db.Sql("UPDATE users SET age = ?, email = ? WHERE id = ?") _
    .Param("age", 26, VBMAN.adInteger) _
    .Param("email", "newemail@example.com", VBMAN.adVarWChar) _
    .Param("id", 1, VBMAN.adInteger) _
    .ExecParam Then

    Debug.Print "Update successful"
End If

Conditional Update

' Complex conditional update
Dim sSql As String
sSql = "UPDATE users SET status = 'inactive' " & _
       "WHERE last_login < DATEADD(day, -30, GETDATE())"
If db.Sql(sSql).Exec Then
    Debug.Print "Expired users marked"
End If

---

DELETE Operations

Basic Delete

' Delete single record
If db.Sql("DELETE FROM users WHERE id = 1").Exec Then
    Debug.Print "Delete successful"
End If

Batch Delete

' Delete multiple records
If db.Sql("DELETE FROM users WHERE age < 18").Exec Then
    Debug.Print "Batch delete successful"
End If

Using Parameterized Delete

' Use parameterized query
If db.Sql("DELETE FROM users WHERE id = ?") _
    .Param("id", 1, VBMAN.adInteger) _
    .ExecParam Then

    Debug.Print "Delete successful"
End If

Truncate Table

' Clear table (note: deletes all data)
If db.Sql("DELETE FROM users").Exec Then
    Debug.Print "Table cleared"
End If

' Or use TRUNCATE (faster, but cannot be rolled back)
If db.Sql("TRUNCATE TABLE users").Exec Then
    Debug.Print "Table cleared"
End If

---

Get Affected Rows

RecordsAffected Parameter

' Get number of affected rows
Dim lAffected As Long
If db.Sql("UPDATE users SET status = 'active' WHERE age > 18").Exec(lAffected) Then
    Debug.Print "Updated " & lAffected & " records"
End If

Examples

' INSERT operation
Dim lAffected As Long
If db.Sql("INSERT INTO users (name) VALUES ('John')").Exec(lAffected) Then
    Debug.Print "Inserted " & lAffected & " records"
End If

' UPDATE operation
If db.Sql("UPDATE users SET status = 'active'").Exec(lAffected) Then
    Debug.Print "Updated " & lAffected & " records"
End If

' DELETE operation
If db.Sql("DELETE FROM users WHERE age < 18").Exec(lAffected) Then
    Debug.Print "Deleted " & lAffected & " records"
End If

---

Get Last Insert ID

LastInsertId Method

The LastInsertId method gets the auto-increment primary key ID of the last insert.

Syntax

Function LastInsertId() As Variant

Supported Databases

• SQL Server - Uses SCOPE_IDENTITY()
• MySQL - Uses LAST_INSERT_ID()
• Access - Uses @@IDENTITY

Example

' Insert data
If db.Sql("INSERT INTO users (name, age) VALUES ('John', 25)").Exec Then
    ' Get last inserted ID
    Dim lId As Variant
    lId = db.LastInsertId
    Debug.Print "New user ID: " & lId
End If

Notes

1. Must call immediately after INSERT

   ' Correct
   db.Sql("INSERT INTO users (name) VALUES ('John')").Exec
   Dim lId As Variant
   lId = db.LastInsertId
   
   ' Wrong: Other operations in between
   db.Sql("INSERT INTO users (name) VALUES ('John')").Exec
   db.Sql("SELECT * FROM users").Query  ' This resets the ID
   Dim lId As Variant
   lId = db.LastInsertId  ' May not get the correct ID

2. Only supports auto-increment primary key

   ' Table must have auto-increment primary key
   CREATE TABLE users (
       id INT IDENTITY(1,1) PRIMARY KEY,  -- SQL Server
       name NVARCHAR(50)
   )

---

Batch Operations

BatchInsert Method

The BatchInsert method inserts data in batches using transactions to ensure data consistency.

Syntax

Function BatchInsert(ByVal TableName As String, ByVal Data As Collection) As Boolean

Parameters

Parameter	Type	Description
TableName	String	Table name (required)
Data	Collection	Data collection, each element is Dictionary (required)

Example

' Prepare data
Dim colData As New Collection
Dim dictRow As Scripting.Dictionary

' First row
Set dictRow = New Scripting.Dictionary
dictRow.Add "name", "John"
dictRow.Add "age", 25
dictRow.Add "email", "john@example.com"
colData.Add dictRow

' Second row
Set dictRow = New Scripting.Dictionary
dictRow.Add "name", "Mary"
dictRow.Add "age", 30
dictRow.Add "email", "mary@example.com"
colData.Add dictRow

' Batch insert
If db.BatchInsert("users", colData) Then
    Debug.Print "Batch insert successful"
Else
    Debug.Print "Batch insert failed: " & db.LastErr
End If

Batch Update

' Use transaction for batch update
db.TransBegin

Dim i As Long
For i = 1 To 100
    Dim sSql As String
    sSql = "UPDATE users SET status = 'active' WHERE id = " & i
    If Not db.Sql(sSql).Exec Then
        db.TransRollback
        Exit For
    End If
Next

If db.TransCommit Then
    Debug.Print "Batch update successful"
End If

---

Asynchronous Execution

Async Property

Use the Async property to execute SQL statements asynchronously.

' Asynchronous execution
db.Sql("INSERT INTO users (name) VALUES ('John')").Async.Exec

' Listen for completion event
Private Sub db_AsyncExecuteComplete(ByVal RecordsAffected As Long, _
                                    ByVal pError As ADODB.Error, _
                                    adStatus As ADODB.EventStatusEnum, _
                                    ByVal pCommand As ADODB.Command, _
                                    ByVal pRecordset As ADODB.Recordset, _
                                    ByVal pConnection As ADODB.Connection)
    If pError Is Nothing Then
        Debug.Print "Async execution successful, rows affected: " & RecordsAffected
    Else
        Debug.Print "Async execution failed: " & pError.Description
    End If
End Sub

---

Error Handling

' Execution error handling
If Not db.Sql("INSERT INTO users (name) VALUES ('John')").Exec Then
    Debug.Print "Execution failed"
    Debug.Print "Error code: " & db.LastErrNumber
    Debug.Print "Error description: " & db.LastErrDescription
    Debug.Print "Full info: " & db.LastErr
    Exit Sub
End If

---

Best Practices

1. Use Parameterized Queries

' Recommended: Use parameterized queries
db.Sql("INSERT INTO users (name, age) VALUES (?, ?)") _
    .Param("name", txtName.Text, VBMAN.adVarWChar) _
    .Param("age", CLng(txtAge.Text), VBMAN.adInteger) _
    .ExecParam

' Not recommended: Direct SQL concatenation (vulnerable to SQL injection)
db.Sql("INSERT INTO users (name, age) VALUES ('" & txtName.Text & "', " & txtAge.Text & ")").Exec

2. Use Transactions for Consistency

' Recommended: Use transactions
db.TransBegin
db.Sql("INSERT INTO users (name) VALUES ('John')").Exec
db.Sql("INSERT INTO posts (user_id, title) VALUES (1, 'Title')").Exec
If Not db.TransCommit Then
    Debug.Print "Transaction failed, rolled back"
End If

3. Check Return Values

' Recommended: Always check return values
If db.Sql("INSERT INTO users (name) VALUES ('John')").Exec Then
    ' Success handling
Else
    ' Error handling
End If

4. Get Affected Rows

' Recommended: Check affected rows
Dim lAffected As Long
If db.Sql("UPDATE users SET status = 'active'").Exec(lAffected) Then
    If lAffected > 0 Then
        Debug.Print "Updated " & lAffected & " records"
    Else
        Debug.Print "No records were updated"
    End If
End If

---

Last Updated: 2026-01-21