Important: Please read the Qt Code of Conduct - https://forum.qt.io/topic/113070/qt-code-of-conduct

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.



  • In other words. I think you have spelling issues.



  • @fcarney Thank you so much!
    checkedState gets an enumeration for checkbox's state.
    It is no matter weather I used checkState or only checked both of them have the same problem!



  • Did you get it working? Maybe post what you changed so other people can see how it should work.



  • 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...


Log in to reply