[QODBC-ALL] How to use PowerShell with QODBC to update records one by one

Download Sample

Sample Code for using QODBC DSN:

# Array of ListIDs to be updated

$listIDs = @(
"20",
"21",
"22"
)

# Define the DSN name
#$dsnName = "QuickBooks Data"
#$dsnName = "QuickBooks Online Data"

# Add your DSN name here
$dsnName = "Your DSN Name"

Write-Output "Connecting to $dsnName"

# Build connection string
$connStr = "DSN=$dsnName;"

# Create a new ODBC connection
$conn = New-Object System.Data.Odbc.OdbcConnection
$conn.ConnectionString = $connStr

try {
# Open the connection
$conn.Open()
Write-Output "Connected to DSN: $dsnName"

Write-Output "Updating records."
foreach ($listID in $listIDs) {
#Prepare the SQL statement
# Either use ifnull or use the 2nd SQL statement
$sql = @"
UPDATE Account SET Description = {fn IFNULL(Description, '')} + '.' WHERE ListID = '$listID'
"@

# Prepare the SQL statement
$sql = @"
UPDATE Account SET Description = Description + '.' WHERE ListID = '$listID'
"@

Write-Output "SQL statement: $sql"
# Create command
$cmd = $conn.CreateCommand()
$cmd.CommandText = $sql

try {
# Execute the update
$rowsAffected = $cmd.ExecuteNonQuery()
Write-Output "Updated ListID: $listID ($rowsAffected rows affected)"
} catch {
Write-Error "Error updating ListID: $listID - $_"
}
}
}
catch {
Write-Error "Could not connect to ODBC DSN: $dsnName - $_"
}
finally {
# Close the connection
if ($conn.State -eq 'Open') {
$conn.Close()
Write-Output "Connection closed."
}
}

Script execution & Result:

For 32-Bit Driver

Open Windows PowerShell (x86) from the start menu:

For 64-Bit Driver

Open Windows PowerShell (x64) from the start menu:

or

Open Windows PowerShell from the start menu:

Navigate to your PowerShell script folder:

Run your PowerShell script:

Result: