Skip to content
  • Categories
  • Recent
  • Tags
  • Popular
  • Users
  • Groups
  • Search
  • Get Qt Extensions
  • Unsolved
Collapse
Brand Logo
  1. Home
  2. Qt Development
  3. QML and Qt Quick
  4. Handeling CheckBoxes in ListView via SQLite
QtWS25 Last Chance

Handeling CheckBoxes in ListView via SQLite

Scheduled Pinned Locked Moved Solved QML and Qt Quick
7 Posts 2 Posters 674 Views
  • Oldest to Newest
  • Newest to Oldest
  • Most Votes
Reply
  • Reply as topic
Log in to reply
This topic has been deleted. Only users with topic management privileges can see it.
  • N Offline
    N Offline
    Nima Ghorab
    wrote on last edited by Nima Ghorab
    #1

    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!

    1 Reply Last reply
    0
    • N Offline
      N Offline
      Nima Ghorab
      wrote on last edited by
      #2

      Can't anyone help?

      1 Reply Last reply
      0
      • fcarneyF Offline
        fcarneyF Offline
        fcarney
        wrote on last edited by
        #3

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

        C++ is a perfectly valid school of magic.

        N 1 Reply Last reply
        0
        • fcarneyF Offline
          fcarneyF Offline
          fcarney
          wrote on last edited by
          #4

          In other words. I think you have spelling issues.

          C++ is a perfectly valid school of magic.

          1 Reply Last reply
          0
          • fcarneyF fcarney

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

            N Offline
            N Offline
            Nima Ghorab
            wrote on last edited by Nima Ghorab
            #5

            @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!

            1 Reply Last reply
            0
            • fcarneyF Offline
              fcarneyF Offline
              fcarney
              wrote on last edited by
              #6

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

              C++ is a perfectly valid school of magic.

              1 Reply Last reply
              0
              • N Offline
                N Offline
                Nima Ghorab
                wrote on last edited by
                #7

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

                1 Reply Last reply
                0

                • Login

                • Login or register to search.
                • First post
                  Last post
                0
                • Categories
                • Recent
                • Tags
                • Popular
                • Users
                • Groups
                • Search
                • Get Qt Extensions
                • Unsolved