Search code examples
sqlqtms-accessms-access-2010qtsql

QtSQL with MS Access : UPDATE/DELETE queries not updating data


I'm currently facing an issue using QtSQL along with an MS Access database, using the QOBDC driver.

SELECT and INSERT statement work, and read/insert data properly.

UPDATE/DELETE statements are correctly processed (according to QSqlQuery and the OBDC tracer), but in MS Access, the data is not edited.

I'm currently using '?' bindings for the request, but replacing these bindings with hardcoded data didn't work either.

The OBDC tracer sees the UPDATE request, as well as the 2 bindings.

Is there a reason why these UPDATE statements do not modify MS Access' data ?

Database

db = QSqlDatabase::addDatabase("QODBC");
db.setDatabaseName(QString("Driver={Microsoft Access Driver (*.mdb, *.accdb)};DSN='';DBQ=") + file);

DELETE function

bool MSAccessReader::deleteRecord(QString table, QString where)
{
    QString req;
    req = QString("DELETE FROM ") + table + QString(" WHERE ") + where;
    QSqlQuery query;
    bool ok = query.exec(req);
    return ok;
}

UPDATE function

bool MSAccessReader::update(QList<QString> headers, QList<QVariant> data, QString where, QString table)
{
    if(headers.isEmpty() || data.isEmpty())
    {
        qDebug() << "Headers or data are empty";
        return false;
    }
    else if(headers.size() != data.size())
    {
        qDebug() << "Number of headers and data fields mismatches";
        return false;
    }
    else if(table.isEmpty())
    {
        qDebug() << "Table isn't specified";
        return false;
    }
    else if(where.isEmpty())
    {
        qDebug() << "WHERE isn't specified";
        return false;
    }

    QSqlQuery query;
    QString req;

    req = QString("UPDATE ") + table + QString(" SET ");

    foreach(QString header, headers)
    {
        req = req + header + "=?,";
    }

    req = req.left(req.size()-1);

    /*WHERE*/
    req = req + QString(" WHERE ") + where + QString(";");

    query.prepare(req);


    foreach(QVariant var,data)
    {
        query.addBindValue(var);
    }


    qDebug() << query.lastQuery() << query.boundValues();
    bool ok = query.exec();
    return ok;
}

Request String and Data

"UPDATE Table1 SET Nom=?,Prenom=? WHERE ID=1;" QMap((":a", QVariant(QString, "Jean"))(":bb", QVariant(QString, "Marc")))

Data table

enter image description here


Solution

  • Found the problem.

    I needed to exectue a transaction() and a commit() around the query :

    db.transaction();
    bool ok = query.exec();
    db.commit();