Skip to content
  • 0 Votes
    5 Posts
    549 Views
    B

    Well, it took a lot of time for me to solve this problem. I solved it not in the way I wanted, but it works fine.
    Firstly, work with MS Access multi-valued fields is quite strange but quite simple: you should clear a field like DELETE Field.Value FROM MyTable WHERE Table.ID = MyID;. Then you can leave it clear or populate with new values performing several INSERT queries: INSERT INTO MyTable (Field.Value) VALUES (MyValue) WHERE MyTable.ID = MyID;.
    And it turned out that it's not that easy to make such code work in OnManualSubmit strategy table model, so I've made a widget which allows you to modify such fields. It is called MultiValueEditor.
    MultiValueEditor.h

    #ifndef MULTIVALUEEDITOR_H #define MULTIVALUEEDITOR_H #include <QDialog> #include <QListWidget> #include <QHash> #include <QSqlRelation> class MultiValueEditor : public QDialog { Q_OBJECT QModelIndex m_projectIndex; QListWidget* m_list; QHash<int, QListWidgetItem*> m_idToItem; public: explicit MultiValueEditor(QModelIndex t_index, const QSqlRelation& t_relation, QWidget* parent = nullptr); ~MultiValueEditor(); public slots: void accept() override; signals: void signalFinished(); }; #endif // MULTIVALUEEDITOR_H

    MultiValueEditor.cpp

    #include "multivalueeditor.h" #include <QSqlQuery> #include <QVBoxLayout> #include <QDialogButtonBox> #include <QPushButton> #include <QMessageBox> #include <QSqlError> #include <QDebug> #include <QSortFilterProxyModel> #include <QSqlTableModel> MultiValueEditor::MultiValueEditor(QModelIndex t_index, const QSqlRelation& t_relation, QWidget *parent) : QDialog(parent) , m_projectIndex(t_index) { const QString queryStr = QString("SELECT [%1], [%2] FROM [%3] ORDER BY [%2];") .arg(t_relation.indexColumn(), t_relation.displayColumn(), t_relation.tableName()); QSqlQuery query(queryStr, QSqlDatabase::database()); m_list = new QListWidget; while (query.next()) { const int id = query.value(0).toInt(); const QString value = query.value(1).toString(); QListWidgetItem* item = new QListWidgetItem(value); m_list->addItem(item); item->setFlags(item->flags() | Qt::ItemIsUserCheckable); item->setCheckState(Qt::Unchecked); m_idToItem[id] = item; } QString value = m_projectIndex.data(Qt::EditRole).toString(); if (!value.isEmpty()) { for (const QString& id : value.split(';')) { m_idToItem[id.toInt()]->setCheckState(Qt::Checked); } } QDialogButtonBox* buttonBox = new QDialogButtonBox(QDialogButtonBox::Ok | QDialogButtonBox::Cancel); connect(buttonBox, &QDialogButtonBox::accepted, this, &MultiValueEditor::accept); connect(buttonBox, &QDialogButtonBox::rejected, this, &MultiValueEditor::reject); QVBoxLayout* mainLayout = new QVBoxLayout; mainLayout->addWidget(m_list); mainLayout->addWidget(buttonBox); setLayout(mainLayout); setWindowTitle(QString("Edit field '%1'").arg(t_relation.tableName())); setAttribute(Qt::WA_DeleteOnClose); } MultiValueEditor::~MultiValueEditor() {} void MultiValueEditor::accept() { // I use QSFPM, but there can be either QSqlTableModel or QSqlRelationalTableModel const QSortFilterProxyModel* proxyModel = qobject_cast<const QSortFilterProxyModel*>(m_projectIndex.model()); QSqlTableModel* sqlModel = qobject_cast<QSqlTableModel*>(proxyModel->sourceModel()); const QString primaryKeyName = sqlModel->headerData(0, Qt::Horizontal).toString(); const QString tableName = sqlModel->tableName(); const QString fieldName = sqlModel->headerData(m_projectIndex.column(), Qt::Horizontal).toString(); QString queryStr = QString("DELETE [%1].Value FROM [%2] WHERE [%3]=:tableId;") .arg(fieldName, tableName, primaryKeyName); QSqlQuery query; query.prepare(queryStr); const int tableId = m_projectIndex.siblingAtColumn(0).data().toInt(); query.bindValue(":tableId", tableId); if (!query.exec()) { QMessageBox msgBox(QMessageBox::Critical, ERROR_TITLE, QString("Error executing SQL-query\n%1") .arg(query.lastError().text()), QMessageBox::Ok, this); msgBox.exec(); } for (auto it = m_idToItem.constBegin(); it != m_idToItem.constEnd(); ++it) { if (it.value()->checkState() == Qt::Checked) { queryStr = QString("INSERT INTO [%1] ([%2].Value) " "VALUES (:fieldId) WHERE [%3]=:tableId;") .arg(tableName, fieldName, primaryKeyName); query.prepare(queryStr); query.bindValue(":fieldId", it.key()); query.bindValue(":tableId", tableId); query.exec(); } } emit signalFinished(); close(); }

    And in a place where you manage your table view (in my case in the MainWindow) you should connect TableView's double click with the execution of the MultiValueEditor. In my case it looks like this:
    MainWindow.cpp

    void MainWindow::setupDatabaseModels() { //... connect(m_projectTableView, &CustomTableView::doubleClicked, this, &MainWindow::slotEditProject); //... } void MainWindow::slotEditProject(const QModelIndex &index) { const int col = index.column(); if (col == 5) { QSqlRelation relation = QSqlRelation("Employees", "ID", "Name"); MultiValueEditor* multivalueEditor = new MultiValueEditor(index, relation, this); connect(multivalueEditor, &MultiValueEditor::signalFinished, [=](){ m_projectTableModel->select(); }); multivalueEditor->open(); } //... }

    Hope this will help somebody.

  • 0 Votes
    4 Posts
    2k Views
    SGaistS

    Did you already checked the bug report system to see if there was something related ?

    If not, please consider opening a new report providing a minimal compilable example showing the behaviour.

  • 0 Votes
    1 Posts
    1k Views
    No one has replied
  • 0 Votes
    2 Posts
    2k Views
    M

    By default a QComboBox reads and saves its currentText property when being mapped to a data model column. I wonder if this is causing your problem?

  • 0 Votes
    1 Posts
    685 Views
    No one has replied