Qt SQL Update Parameter count mismatch while insert works

BambusFan

Hey,
i have no idea why this error occurs:

QString=name;
name = ui->txt_name->toPlainText();
QSqlQuery query;
if(idd==0){
    query.prepare("INSERT INTO Table (name) VALUES(:name)");
}
else{
    query.prepare("UPDATE Table SET (name) VALUES (:name) WHERE id=1");
}
query.bindValue(":name", name);
query.exec();
qDebug() << query.lastError();

Insert works, but update does not and I get the message: QSqlError("", "Parameter count mismatch", "")

Could someone please tell me where the mistak is?

eyllanesc

This post is deleted!

BambusFan

@eyllanesc Thank you, but was only in this post - accordingly not the problem

eyllanesc

@BambusFan The update syntax is:

UPDATE table
SET column_1 = new_value_1,
    column_2 = new_value_2
WHERE
    search_condition 
ORDER column_or_expression
LIMIT row_count OFFSET offset;

In your case:

query.prepare("UPDATE Table SET name = :name  WHERE id=1");

BambusFan

@eyllanesc I used bindValue over prepare and used placeholder:
https://doc.qt.io/qt-5/qsqlquery.html#qsqlquery-examples

query.prepare("UPDATE Table SET name = :name  WHERE id=1");

is therefore the same as:

query.prepare("UPDATE Table SET (name) VALUES (:name) WHERE id=1");
query.bindValue(":name", name);

Accordingly, also the same error message
But thank you, you've given me a few ideas.

eyllanesc

@BambusFan I know you use bindValue-prepare with placeholders but that doesn't mean the syntax changes. In the following demo I verify that my suggestion works.

#include <QCoreApplication>
#include <QSqlDatabase>
#include <QSqlError>
#include <QSqlQuery>

#include <QDebug>

int main(int argc, char *argv[])
{
    QCoreApplication a(argc, argv);

    QSqlDatabase db = QSqlDatabase::addDatabase("QSQLITE");
    db.setDatabaseName(":memory:");
    if(!db.open()){
        qDebug() << db.lastError().text();
        return EXIT_FAILURE;
    }

    QSqlQuery query;
    if(!query.exec("CREATE TABLE FooTable(id INTEGER PRIMARY KEY, name TEXT NOT NULL )")){
        qDebug() << query.lastError().text();
        return EXIT_FAILURE;
    }

    query.prepare("INSERT INTO FooTable (id, name) VALUES (?, ?)");
    query.addBindValue(1);
    query.addBindValue("Foo");
    if(!query.exec()){
        qDebug() << query.lastError().text();
        return EXIT_FAILURE;
    }

    query= QSqlQuery("SELECT id, name FROM FooTable");
    if(!query.exec()){
        qDebug() << query.lastError().text();
        return EXIT_FAILURE;
    }
    while (query.next()) {
        qDebug() << query.value("id").toString() << query.value("name").toString();;
    }

    query.prepare("UPDATE FooTable SET name = :name  WHERE id=1");
    query.bindValue(":name", "Bar");
    if(!query.exec()){
        qDebug() << query.lastError().text();
        return EXIT_FAILURE;
    }
    query= QSqlQuery("SELECT id, name FROM FooTable");
    if(!query.exec()){
        qDebug() << query.lastError().text();
        return EXIT_FAILURE;
    }
    while (query.next()) {
        qDebug() << query.value("id").toString() << query.value("name").toString();;
    }

    return EXIT_SUCCESS;
}

Output:

"1" "Foo"
"1" "Bar"

BambusFan

@eyllanesc Thank you very much, you are right and it works now. I thought it was a function of prepare, so it would be identical.

Solved Qt SQL Update Parameter count mismatch while insert works