Xaris Xaris - 11 months ago 51
C++ Question

How to edit field values in sqLite using Qt Gui app?

I am writting a library application using Qt 5.7 and C++ backed by a sqLite v3 database. I want to implement a loan/return functionality. I have enabled a bool field in my database called isLoaned and i want to change its value of the corresponding record to true whenever the user clicks the loan button. I get a proper connection to the database and no errors, but the database is not updated and field value remain the same. I followed Qt documenation about the use of these classes, but i cant find a solution.
Here is my code

void loanBookDialog::on_loanBookBtn_clicked()
MainWindow mw;
QString id, title;
id = ui->idLnEdit->text();
title = ui->titleLnEdit->text();
QSqlQuery qry(mw.myDb);
qry.prepare("select * from Books where (id='"+id+"' OR title='"+title+"') AND isLoaned=0");
if (qry.exec()) {
QSqlRecord loanRec(qry.record());
QSqlField loanField = loanRec.field(5);
qDebug() << loanField.isNull();
qDebug() << loanField.isReadOnly();
qDebug() << loanField.isValid();
qDebug() << loanField.isGenerated();
ui->wrong5Lbl->setStyleSheet("QLabel {color: green}");
ui->wrong5Lbl->setText("Loaned succesfuly.");
else {
ui->wrong5Lbl->setStyleSheet("QLabel {color: red}");
ui->wrong5Lbl->setText("Oops! Something went wrong, try again.");

Debug output:

Field value: QVariant(bool, true)
isNull: false
isReadOnly: false
isValid: true
isGenerated: true

Answer Source

SQL queries, in any database, return the results by value. The QSqlRecord is a copy of the database, not a reference to it. Therefore loanField.setValue(true); achieves absolutely nothing.

To modify the content of the database, you need to execute a data modification statement, in this case an update.

Also, please keep in mind that composing queries using string composition is fraught with peril due to quoting issues and should never be used where parameters can be.

QSqlQuery loanQuery(mw.myDb);
loanQuery.prepare("update Books set isLoaned where id=:id");
// note: you can re-use the prepared query with different bindings to improve performance
loanQuery.bindValue(":id", id);

Also, don't forget to wrap it in transaction. Do:


before and


after. And check errors! If commit fails, the data was not written!