使用NodeJS执行Access数据库插入查询时遭遇“this operation is not authorized if the object is closed”错误

Hey there, let's work through this Access insert issue you're hitting. It's super frustrating when SELECT works but INSERT throws a weird error—here's what's likely going on and how to fix it:

Common Causes & Solutions #

1. You're using query() for a non-query operation #

node-adodb's query() method is built for operations that return a result set (like SELECT). When you run an INSERT with it, since there's no result set to return, the ADODB object can close prematurely, triggering that "object is closed" error.

Instead, use execute()—it's designed specifically for non-query statements like INSERT, UPDATE, and DELETE.

2. Your string-concatenated SQL is risky (and possibly breaking things) #

Building your INSERT string by hand is a recipe for syntax errors (think: if any of your values has a single quote, like O'Neil, it'll break the SQL) and SQL injection vulnerabilities. Switch to parameterized queries to avoid both issues.

Modified Working Code #

Here's how to rewrite your insert logic correctly:

// Connection code stays the same (since SELECT works, this is solid)
const ADODB = require('node-adodb'); 
const connection = ADODB.open("Driver={Microsoft Access Driver (*.mdb, *.accdb)};Dbq=\\\\HP-8000\\db\\db.accdb;Uid=Admin;Pwd=xxx;");

// Replace your old insert line with this parameterized execute call
const sql = `
  INSERT INTO Variants (CatalogCode, Variant, MeleePrice, StonePrice, RingPrice, PrRing, PrMelee, PrStone)
  VALUES (?, ?, ?, ?, ?, ?, ?, ?)
`;

await connection.execute(sql, [
  variantsToAdd[i].CatalogCode,
  variantsToAdd[i].Variant,
  variantsToAdd[i].MeleePrice,
  variantsToAdd[i].StonePrice,
  variantsToAdd[i].RingPrice,
  variantsToAdd[i].PrRing,
  variantsToAdd[i].PrMelee,
  variantsToAdd[i].PrStone
]);

Why This Works #

• execute() handles non-query operations properly: It doesn't expect a result set, so the ADODB object won't close early.
• Parameterized queries avoid syntax breaks: No more worrying about special characters in your data breaking the SQL string. It also keeps your code secure against SQL injection.

Give this a shot—this should resolve that annoying "object is closed" error.

内容的提问来源于stack exchange，提问作者Cheesus