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.
