bindValue is not working

icoicqico

bool DbManager::add_value(const QString& col, const auto& value){
        bool success = false;
        QSqlQuery query;  
        query.prepare("INSERT INTO idcard_data (Accountid) Values (:ac)");
        query.bindValue(":ac","001"); 
  
        if (query.exec()){
            success = true;
        }
        else qDebug()<<"INSERT error"<<query.lastError();
        qDebug() << query.executedQuery();
        return success;
    }

debug return shows the value I want to bind is ?, I have tried pass Qstring& variable, still the same, but it works when I just run query.prepare with manually input data and not using bindvalue.
"INSERT INTO idcard_data (Accountid) Values (?)"

I have searched many posts that reported to have this issue but couldn't find any legit solution, I hope if someone know what happening can give me some helps, thanks a lot.

Edit: if I use Qstring& variable like this,

query.prepare("INSERT INTO idcard_data (:A) Values (:ac)");
        query.bindValue(":A",col);
        query.bindValue(":ac","hello");

it gives me this error:

INSERT error QSqlError("", "Parameter count mismatch", "")
"INSERT INTO idcard_data (?) Values (?)"

Christian Ehrlicher

Please adjust your post so the code can be read by others - please use the code tags.

And for your code - please check the return value of query.prepare(). The statement with '?' is correct. What does query.lastError() print?

icoicqico

@Christian-Ehrlicher Thanks for the reply, no last error is printed, and I have checked the database no correct value is added. But if I just manually input the data to the statement without bindValue the data is correctly update.

Christian Ehrlicher

So when no error is printed then query.exec() succeeded and all is fine.

You still not check the return value of query.prepare()

JonB

@icoicqico
Firstly, as @Christian-Ehrlicher says. Put in error checking.

query.prepare("INSERT INTO idcard_data (:A) Values (:ac)");

Don't waste time on this, it is wrong and will not work.

    query.prepare("INSERT INTO idcard_data (Accountid) Values (:ac)");
    query.bindValue(":ac","001");

I just run query.prepare with manually input data

What type is AccountId in your database? Have you really chosen a (3-character) string as your Accountid? Show your code for this "manually input data" case.

icoicqico

@JonB It is not the type problem, ok let's use 'hello' as an example,

query.prepare("INSERT INTO idcard_data (Accountid) Values ('hello')");

return:

"INSERT INTO idcard_data (Accountid) Values ('hello')"

And I check my database the value "hello" is updated.

But if I do it this way, col is "Accountid"

query.prepare("INSERT INTO idcard_data (:A) Values ('hello')");
query.bindValue(":A",col);

INSERT error QSqlError("", "Parameter count mismatch", "")
"INSERT INTO idcard_data (?) Values ('hello')"

and no update on the databse.

JonB

@icoicqico
You reply to me, where I just wrote:

query.prepare("INSERT INTO idcard_data (:A) Values (:ac)");

Don't waste time on this, it is wrong and will not work.

There is no point you doing this again and show the error it produces given that this is not allowed. Did you read that I wrote this?

Both @Christian-Ehrlicher and I have now said repeatedly to check the result from any prepare() statements. If you want help why do you simply ignore this? It's pretty frustrating for responders to ask you questions or tell you what to do/not do if you're not going to act on these.

The only one we are interested in at present is your original:

        query.prepare("INSERT INTO idcard_data (Accountid) Values (:ac)");
        query.bindValue(":ac","001");

icoicqico

@icoicqico said in bindValue is not working:

"INSERT INTO idcard_data (?) Values ('hello')"

This is the result of query.executedQuery(), or you want something else?

Christian Ehrlicher

A column can not be bound, only values, I'm pretty sure query.exec() returns false but you ignore me.

miabaker

Hi, I think you need to use the QSqlQuery::bindValue() method instead of QSqlQuery::addBindValue(). The former binds a value to a named placeholder, while the latter binds a value to a positional placeholder. See the documentation for more details: https://doc.qt.io/qt-5/qsqlquery.html#bindValue

Regards, Mia from CodeIT

JonB

@miabaker said in bindValue is not working:

Hi, I think you need to use the QSqlQuery::bindValue() method instead of QSqlQuery::addBindValue().

So far the OP has only used bindValue(), which is correct for named parameters, not addBindValue() at all which is only for positional parameters. So don't know what you are saying here.

JonB

@icoicqico
If you want help, e.g. from @Christian-Ehrlicher or myself, let us know when you are prepared to make the changes we suggested, and not continue with something else which will never work. Up to you.