Handeling CheckBoxes in ListView via SQLite
-
Hey guys!
I have encountred with an strange problem in QML listview and SQLite!
I have a checkbox item for each record (checked as an integer field) in database and I want to manipulate checked field whenever user select the proper checkbox item and also show in listview that the item is checked.
The problem is that whenever I select any checkboxes it doesn't select and always remain untoggled!
Here is my codes:listmodel.h is:
// listmodel.h #ifndef LISTMODEL_H #define LISTMODEL_H #include <QObject> #include <QSqlQueryModel> class ListModel : public QSqlQueryModel { Q_OBJECT public: explicit ListModel(QObject *parent = nullptr); virtual QVariant data(const QModelIndex &index, int role = Qt::DisplayRole) const override; protected: QHash<int, QByteArray> roleNames() const override; signals: public slots: void updateModel(); int getId(int row); }; #endif // LISTMODEL_H
listmodel.cpp is:
//listmodel.cpp #include "listmodel.h" #include "DatabaseColumns.h" #include <QtDebug> #include <QSqlError> ListModel::ListModel(QObject *parent) : QSqlQueryModel(parent) { //updateModel(); { QString str; int counter = 0; for(const QString& tempStr: DATABASE_COLUMNS){ if (counter != DATABASE_LENGTH - 1) str += tempStr + ", "; else str += tempStr; ++counter; } qDebug() << "SELECT " + str + " FROM " TABLE; setQuery("SELECT " + str + " FROM " TABLE); } } QVariant ListModel::data(const QModelIndex &inputIndex, int role) const { int columnId = role - Qt::UserRole - 1; QModelIndex modelIndex = index(inputIndex.row(), columnId); return QSqlQueryModel::data(modelIndex, Qt::DisplayRole); } QHash<int, QByteArray> ListModel::roleNames() const { QHash<int, QByteArray> roles; int i = Qt::UserRole + 1; for(const QString& str: DATABASE_COLUMNS){ roles[i] = str.toUtf8(); ++i; } return roles; } void ListModel::updateModel() { QString str; int counter = 0; for(const QString& tempStr: DATABASE_COLUMNS){ if (counter != DATABASE_LENGTH - 1) str += tempStr + ", "; else str += tempStr; ++counter; } setQuery("SELECT " + str + " FROM " TABLE); //setQuery("SELECT * FROM " TABLE); } int ListModel::getId(int row) { return data(index(row, 0), Qt::UserRole + 1).toInt(); }
database.h is:
//database.h #ifndef Database_H #define Database_H #include <QObject> #include <QSql> #include <QSqlQuery> #include <QSqlError> #include <QSqlDatabase> #include <QFile> #include <QDate> #include <QDebug> class Database : public QObject { Q_OBJECT public: explicit Database(QObject *parent = nullptr); ~Database(); void connectToDatabase(); private: QSqlDatabase db; private: bool openDatabase(); bool restoreDatabase(); void closeDatabase(); bool createTable(); public slots: bool inserIntoTable(const QStringList &data); // Adding entries to the table bool modifyTable(int id, bool selection); bool removeRecord(int id); // Removing records from the table on its id }; #endif // Database_H
database.cpp is:
//database.cpp #include "database.h" #include "DatabaseColumns.h" Database::Database(QObject *parent) : QObject(parent){} Database::~Database(){} void Database::connectToDatabase() { !QFile{DATABASE_NAME}.exists() ? this->restoreDatabase() : this->openDatabase(); } bool Database::restoreDatabase() { if(this->openDatabase()){ return (this->createTable()) ? true : false; } else { qDebug() << "Failed to restore the Database"; return false; } } bool Database::openDatabase() { db = QSqlDatabase::addDatabase("QSQLITE"); db.setHostName(DATABASE_HOSTNAME); db.setDatabaseName(DATABASE_NAME); if(db.open()){ return true; } return false; } void Database::closeDatabase() { db.close(); } bool Database::createTable() { QString str; int counter = 0; for (const QString& tempStr: DATABASE_COLUMNS){ switch (counter) { case 0: str += tempStr + " " + DATABASE_ID_TYPE ", "; break; case 1: str += tempStr + " " + DATABASE_CHECKED_TYPE ", "; break; case DATABASE_LENGTH - 1: str += tempStr + " VARCHAR(255) NOT NULL"; break; default: str += tempStr + " VARCHAR(255) NOT NULL, "; } ++counter; } QSqlQuery query; if(!query.exec( "CREATE TABLE " TABLE " (" + str + ")")) { qDebug() << "Database: error of create " << TABLE; qDebug() << query.lastError().text(); return false; } return true; } bool Database::inserIntoTable(const QStringList &data) { QSqlQuery query; QString str{"INSERT INTO " TABLE " ("}; int counter = 0; for (const QString& tempStr: DATABASE_COLUMNS) { switch (counter) { case 0: break; case DATABASE_LENGTH - 1: str += tempStr; break; default: str += tempStr + ", "; } ++counter; } str += ") VALUES ("; counter = 0; for (const QString& tempStr: DATABASE_COLUMNS) { switch (counter) { case 0: break; case DATABASE_LENGTH - 1: str += ":" + tempStr + ")"; break; default: str += ":" + tempStr + ", "; } ++counter; } query.prepare(str); counter = 0; for (const QString& tempStr: DATABASE_COLUMNS) { if (tempStr != "id") { query.bindValue(":" + tempStr, data[counter]); ++counter; } } if(!query.exec()){ qDebug() << "error insert into " << TABLE; qDebug() << query.lastError().text(); return false; } return true; } bool Database::modifyTable(int id, bool selection) { QSqlQuery query; //qDebug() << "UPDATE " TABLE " SET checked = " + QString::number(selection) + " WHERE id = " + QString::number(id); if(!query.exec("UPDATE " TABLE " SET checked = " + QString::number(selection) + " WHERE id = " + QString::number(id))){ qDebug() << "error delete row " << TABLE; qDebug() << query.lastError().text(); return false; } return true; } bool Database::removeRecord(int id) { QSqlQuery query; // query.prepare("DELETE FROM " TABLE " WHERE id = :ID"); // query.bindValue(":ID", id); if(!query.exec("DELETE FROM " TABLE " WHERE id = " + QString::number(id))){ qDebug() << "error delete row " << TABLE; qDebug() << query.lastError().text(); return false; } else { return true; } }
DatabaseColumns.h is:
//DatabaseColumns.h #ifndef DATABASECOLUMNS_H #define DATABASECOLUMNS_H #include <array> #include <QString> #define DATABASE_HOSTNAME "localhost" #define DATABASE_NAME "mydatabase.db" #define TABLE "MyTable" #define DATABASE_LENGTH 18 #define DATABASE_ID_TYPE "INTEGER PRIMARY KEY AUTOINCREMENT" #define DATABASE_CHECKED_TYPE "INTEGER" #define DATABASE_COLUMNS std::array<QString, DATABASE_LENGTH>{\ "id",\ "checked",\ "firstname",\ "lastname",\ "gender",\ "nationality",\ "town",\ "mobile",\ "job",\ "birthdate",\ "purchasedate",\ "footsize",\ "shoemodel",\ "cost",\ "address",\ "automaticgrade",\ "specificgrade",\ "staffcode"} #endif // DATABASECOLUMNS_H
Here is how I used it in QML:
import QtQuick 2.14 import QtQuick.Controls 2.14 Item { property alias contentWidth: tableView.contentWidth ListView { id: tableView clip: true property int textWidth: 100 Component.onCompleted: contentX = Math.abs(contentItem.childrenRect.width - parent.width) contentWidth: 1500//contentItem.childrenRect.width contentHeight: 500//contentItem.childrenRect.height headerPositioning: ListView.OverlayHeader flickableDirection: Flickable.HorizontalAndVerticalFlick anchors.fill: parent highlight: Rectangle { color: "Blue" opacity: 0.2 } header: Rectangle { property alias checkState: headerCheckBox.checkState height: tableHead.implicitHeight + seperator.height width: tableHead.implicitWidth z: 2 Row { id: tableHead spacing: 20 layoutDirection: Qt.RightToLeft CheckBox {id: headerCheckBox; indicator.width: 20; indicator.height: 20} TableHeadText { text: "نام" } TableHeadText { text: "نام خانوادگی" } TableHeadText { text: "جنسیت" } TableHeadText { text: "همراه" } TableHeadText { text: "تاریخ تولد" } TableHeadText { text: "تاریخ خرید" } TableHeadText { text: "اندازه پا" } TableHeadText { text: "مدل کفش" } TableHeadText { text: "قیمت" } } ToolSeparator { id: seperator height: 7 padding: 0 topPadding: 0 bottomPadding: 0 anchors { top: tableHead.bottom; right: parent.right } orientation: Qt.Horizontal width: tableHead.implicitWidth } } model: myModel delegate: Item{ width: parent.width height: rowId.implicitHeight MouseArea{ anchors.fill: parent onClicked: tableView.currentIndex = index } Row { id: rowId spacing: 20 layoutDirection: Qt.RightToLeft TableText { text: checked } CheckBox { id: checkBox indicator.width: 20; indicator.height: 20 checked: parseInt(model.checked, 10) onCheckedChanged: { if (checkState === Qt.Checked){ console.log("Currently checked!") database.modifyTable(myModel.getId(tableView.currentIndex), true) myModel.updateModel() }else{ console.log("Unchecked!") database.modifyTable(myModel.getId(tableView.currentIndex), false) myModel.updateModel() } } } TableText { text: firstname } TableText { text: lastname } TableText { text: gender } TableText { text: mobile } TableText { text: birthdate } TableText { text: purchasedate } TableText { text: footsize } TableText { text: shoemodel } TableText { text: cost } } } } Button{ text: "OK" onClicked: myModel.updateModel() } }
Any help is greatly appreciated!
thank you so much! -
Can't anyone help?
-
@Nima-Ghorab said in Handeling CheckBoxes in ListView via SQLite:
onCheckedChanged: {
if (checkState === Qt.Checked){In my code I am checking against "checked" and not "checkedState". What is "checkState"? Also, does your code update the checked state when written to the database? I am guessing "checkState" is undefined so it does not match Qt.Checked.
-
Finally I used onClicked signal rather than using onStateChanged signal!
Using onClicked signal prevents items to fall in infinite loop.
But I'm still curious for a better way...