How to implement a read-write QSqlQueryModel

Question

I have subclassed and reimplemented QSqlQueryModel::setData() and QSqlQueryModel::flags() and now I can edit this model, but something is going wrong here:

enter image description here

I edit the fourth field in the record, but subsequently, starting from the fourth field, all fields change their content to the same:

enter image description here

Here is my scoremodel.cpp:

#include <QtSql>
#include "scoremodel.h"
#include "mainwindow.h"    //MainWindow::sqlToQueryScore
#include <QDebug>

ScoreModel::ScoreModel(QObject *parent)
    : QSqlQueryModel(parent)
{
}

Qt::ItemFlags ScoreModel::flags(
        const QModelIndex &index) const
{
    Qt::ItemFlags flags = QSqlQueryModel::flags(index);
    if (index.column() != 0 && index.column() != 11)
        flags |= Qt::ItemIsEditable;
    return flags;
}

bool ScoreModel::setData(const QModelIndex &index, const QVariant &value, int /* role */)
{
    if (index.column() == 0 || index.column() == 11)
        return false;

    QModelIndex primaryKeyIndex = QSqlQueryModel::index(index.row(), 0);
    int id = data(primaryKeyIndex).toInt();
    qDebug()<<"id:"<<id;
    clear();

    bool ok;
    switch(index.column()){
    case 1:
        ok = setYear(id,value.toString());
    case 2:
        ok = setStudentName(id,value.toString());
    case 3:
        ok = setStudentClass(id,value.toString());
    case 4:
        ok = setTestTime(id,value.toString());
    case 5:
        ok = setTestSubject(id,value.toString());
    case 6:
        ok = setTestType(id,value.toString());
    case 7:
        ok = setTestScore(id,value.toString());
    case 8:
        ok = setStudyPeriod(id,value.toString());
    case 9:
        ok = setTestContent(id,value.toString());
    case 10:
        ok = setTeacherRemark(id,value.toString());
    default:
        ok = false;
    }
    refresh();
    return ok;
}


void ScoreModel::refresh()
{
    qDebug()<<"sqlToQueryScore in refresh:"<<MainWindow::sqlToQueryScore;
    setQuery(MainWindow::sqlToQueryScore);
    setHeaderData(0, Qt::Horizontal, tr("序号"));
    setHeaderData(1, Qt::Horizontal, tr("年份"));
    setHeaderData(2, Qt::Horizontal, tr("学生姓名"));
    setHeaderData(3, Qt::Horizontal, tr("学生班级"));
    setHeaderData(4, Qt::Horizontal, tr("测试时间"));
    setHeaderData(5, Qt::Horizontal, tr("测试科目"));
    setHeaderData(6, Qt::Horizontal, tr("测试类型"));
    setHeaderData(7, Qt::Horizontal, tr("测试成绩"));
    setHeaderData(8, Qt::Horizontal, tr("学习周期"));
    setHeaderData(9, Qt::Horizontal, tr("测试内容"));
    setHeaderData(10, Qt::Horizontal, tr("教师评语"));
    setHeaderData(11, Qt::Horizontal, tr("数据插入时间"));

}

bool ScoreModel::setYear(int id, const QString &year){
    QSqlQuery query;
    query.prepare("update test_score set year = ? where id = ?");
    query.addBindValue(year);
    query.addBindValue(id);
    return query.exec();
}
bool ScoreModel::setStudentName(int id, const QString &studentName){
    QSqlQuery query;
    query.prepare("update test_score set student_name = ? where id = ?");
    query.addBindValue(studentName);
    query.addBindValue(id);
    return query.exec();
}
bool ScoreModel::setStudentClass(int id,const QString &studentClass){
    QSqlQuery query;
    query.prepare("update test_score set student_class = ? where id = ?");
    query.addBindValue(studentClass);
    query.addBindValue(id);
    return query.exec();
}
bool ScoreModel::setTestTime(int id,const QString &testTime){
    QSqlQuery query;
    query.prepare("update test_score set test_time = ? where id = ?");
    query.addBindValue(testTime);
    query.addBindValue(id);
    return query.exec();
}
bool ScoreModel::setTestSubject(int id,const QString &testSubject){
    QSqlQuery query;
    query.prepare("update test_score set test_subject = ? where id = ?");
    query.addBindValue(testSubject);
    query.addBindValue(id);
    return query.exec();
}
bool ScoreModel::setTestType(int id,const QString &testType){
    QSqlQuery query;
    query.prepare("update test_score set test_type = ? where id = ?");
    query.addBindValue(testType);
    query.addBindValue(id);
    return query.exec();
}
bool ScoreModel::setTestScore(int id,const QString &testScore){
    QSqlQuery query;
    query.prepare("update test_score set test_score = ? where id = ?");
    query.addBindValue(testScore);
    query.addBindValue(id);
    return query.exec();
}
bool ScoreModel::setStudyPeriod(int id,const QString &studyPeriod){
    QSqlQuery query;
    query.prepare("update test_score set study_period = ? where id = ?");
    query.addBindValue(studyPeriod);
    query.addBindValue(id);
    return query.exec();
}
bool ScoreModel::setTestContent(int id,const QString &testContent){
    QSqlQuery query;
    query.prepare("update test_score set test_content = ? where id = ?");
    query.addBindValue(testContent);
    query.addBindValue(id);
    return query.exec();
}
bool ScoreModel::setTeacherRemark(int id,const QString &teacherRemark){
    QSqlQuery query;
    query.prepare("update test_score set teacher_remark = ? where id = ?");
    query.addBindValue(teacherRemark);
    query.addBindValue(id);
    return query.exec();
}

How can I solve this?

CPPUIX · Accepted Answer · 2024-06-20 12:55:06Z

I have tried to reduce the code that shows besides implementing the solution you want as I show below, on the other hand I would recommend using QSqlTableModel^(*).

#include <QApplication>
#include <QMessageBox>
#include <QSqlQuery>
#include <QSqlQueryModel>
#include <QSqlRecord>
#include <QSqlError>
#include <QSqlTableModel>
#include <QTableView>

class SqlQueryModel: public QSqlQueryModel{
public:
    using QSqlQueryModel::QSqlQueryModel;
    Qt::ItemFlags flags(const QModelIndex &index) const {
        return QSqlQueryModel::flags(index) | Qt::ItemIsEditable;
    }
    bool setData(const QModelIndex &index, const QVariant &value, int role){
        if (index.isValid() && role == Qt::EditRole) {
            QSqlQuery qry;
            qry.prepare(QString("update test_score set %1 = ? where id = ?").arg(record().fieldName(index.column())));
            qry.addBindValue(value);
            qry.addBindValue(query().value(record().indexOf("id")));
            if(!qry.exec())
                return false;
            setQuery(query().lastQuery());
            return true;
        }
        return false;
    }
};

static bool createConnection()
{
    QSqlDatabase db = QSqlDatabase::addDatabase("QSQLITE");
    db.setDatabaseName(":memory:");
    if (!db.open()) {
        QMessageBox::critical(nullptr, QObject::tr("Cannot open database"),
                              QObject::tr("Unable to establish a database connection.\n"
                                          "This example needs SQLite support. Please read "
                                          "the Qt SQL driver documentation for information how "
                                          "to build it.\n\n"
                                          "Click Cancel to exit."), QMessageBox::Cancel);
        return false;
    }

    QSqlQuery query;
    query.exec("create table test_score (id int primary key, student_name varchar(20), student_class varchar(20))");
    query.exec("insert into test_score values(101, 'name1', 'Young')");
    query.exec("insert into test_score values(102, 'name2', 'Holand')");
    query.exec("insert into test_score values(103, 'Lars', 'Gordon')");
    query.exec("insert into test_score values(104, 'Roberto', 'Robitaille')");
    query.exec("insert into test_score values(105, 'Maria', 'Papadopoulos')");
    return true;
}


int main(int argc, char *argv[])
{
    QApplication a(argc, argv);
    if(!createConnection())
        return -1;
    QTableView view;
    SqlQueryModel model;
    model.setQuery("SELECT * FROM test_score");
    view.setModel(&model);
    view.show();
    return a.exec();
}

Looking more closely at your code, the error is caused by the absence of a break within each case.

...
switch(index.column()){
case 1:
    ok = setYear(id,value.toString());
    break; // <---
case 2:
    ok = setStudentName(id,value.toString());
    break; // <---
case 3:
...

^{(*): from the documentation of QSQlQueryModel: The model is read-only by default. To make it read-write, you must subclass it and reimplement setData() and flags(). Another option is to use QSqlTableModel, which provides a read-write model based on a single database table.}

Collectives™ on Stack Overflow

How to implement a read-write QSqlQueryModel

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related