Failed to change the value in QTableView connected to a database if too much items

[Seven.Zeng]

Hi all,

I made a program as the front end to manage a database using the QTableView widget, here is the init code:

    mDB = QSqlDatabase::addDatabase("QSQLITE");
    QTableView* pView;

    mpContractModel = new QSqlRelationalTableModel(this, mDB);
    pView = ui->tvContract;
    pView->setModel(mpContractModel);
    pView->setItemDelegate(new QSqlRelationalDelegate(this));
    pView->setSortingEnabled(true);
    connect(pView->selectionModel(), SIGNAL(currentRowChanged(const QModelIndex &, const QModelIndex &)), this, SLOT(onContractTableClicked(const QModelIndex &, const QModelIndex &)));

    mpContractDetailModel = new QSqlRelationalTableModel(this, mDB);
    pView = ui->tvContractDetail;
    pView->setModel(mpContractDetailModel);
    pView->setItemDelegate(new QSqlRelationalDelegate(this));
    pView->setSortingEnabled(true);

.......

    if (mCurrentContractType == QString::fromLocal8Bit("采购") || mCurrentContractType == QString::fromLocal8Bit("采购合同") || mCurrentContractType == "S") {
        mpContractDetailModel->setTable("Purchase");
    } else {
        mpContractDetailModel->setTable("Sales");
    }
    mpContractDetailModel->select();
    UpdateTableHeaderMap(*mpContractDetailModel, mContractDetailTableHeaderMap);
    mpContractDetailModel->setFilter("ContractID='" + mCurrentContractID+"'");

    if (mCurrentContractType == QString::fromLocal8Bit("销售") || mCurrentContractType == QString::fromLocal8Bit("销售合同") || mCurrentContractType == "P") {
        mpContractDetailModel->setRelation(mContractDetailTableHeaderMap["ItemID"], QSqlRelation("Purchase", "ItemID", "ItemID"));
    } else {
        mpContractDetailModel->setRelation(mContractDetailTableHeaderMap["ItemID"], QSqlRelation("Sales", "ItemID", "ItemID"));
    }
    mpContractDetailModel->setRelation(mContractDetailTableHeaderMap["ProductID"], QSqlRelation("ProductID", "ID", "ID"));
    mpContractDetailModel->setRelation(mContractDetailTableHeaderMap["ProductSeries"], QSqlRelation("ProductSeries", "Name", "Name"));
    mpContractDetailModel->setRelation(mContractDetailTableHeaderMap["ContractID"], QSqlRelation("Contract", "ID", "ID"));

Basically, the intent of the program is that, one table view (contract view) is used to display the contracts, once one of the contract is selected (clicked), the detail view will list all the details (sub items attached to this contract), and the detail view is editable and can be comitted.

QSqlRelation is used for some of the columns so that they can be editted by simply selecting items form a combobox.

For contract detail view, there is a column 'ProductID', which is connected to the 'ProductID' table of the database with QSqlRelation. Here is the problem: there are lots of items in that table (1000+), I can select any item from the combobox, but if the item index inside the combobox exceed a certain value (seems to be 255), once the combobox lost focus, the value will be reverted back to the previous one.

This problem really bothered me alot, any comments are appreciated!

[dheerendra]

Atleast we did not face any issue with these many items in combobox. It could be something else. I have simple program and can you try to see if you have the same issue ? Just to check your Qt version has issue.

============= Delegate ===========
QWidget *MyDelegate::createEditor(QWidget *parent, const QStyleOptionViewItem &option, const QModelIndex &index) const
{
    QComboBox *bx = new QComboBox(parent);
    for(int i=0;i<1000;i++){
        bx->addItem(QString::number(i));
    }
    return bx;
}

=========MyWidget.cpp ========

MyWidget::MyWidget(QWidget *parent) :
    QWidget(parent),
    ui(new Ui::MyWidget)
{
    ui->setupUi(this);
    QStandardItemModel *model = new QStandardItemModel(4, 4);
    for (int row = 0; row < 4; ++row) {
        for (int column = 0; column < 4; ++column) {
            QStandardItem *item = new QStandardItem(QString("row %0, column %1").arg(row).arg(column));
            model->setItem(row, column, item);
        }
    }
    ui->tableView->setModel(model);
    ui->tableView->setItemDelegateForColumn(1,new MyDelegate);
}

[Seven.Zeng]

@dheerendra

Hi dheerendra,

Thanks for your reply, I've tested your code with my IDE and found it working correctly, then what would the problem be?

[dheerendra]

Since you are suspecting large number of values, can you try your project with less number of items inside the combobox and see how it behaves. It is possible that item from combox itself is not selected and it is clicked/consumed somewhere else.

[Seven.Zeng]

@dheerendra

less items is working ok, as firstly there is another combobox with less items woking properly, secondly, the front items of the concerned combobox can be selected, just has problem to select the back ones.

as shown on the pic, the 'ProductSeries' combo is working correctly, as it has less items, but the 'ProductID' one is not.

[dheerendra]

Is it that your click on combobox is consumed by some other control ? Can you try increasing the width of ProductID cell ?

[Seven.Zeng]

@dheerendra

I made a example to show the issue so that you can run to have a see

[0_1543910609867_DelegateTest.7z](Uploading 100%)[0_1543910660455_DelegateTest.7z](Uploading 100%)

Failed to upload the pack T_T as I don't have the previlage...

[JonB]

@Seven.Zeng
I don't know why your example fails on 255 items, or whether that's only on a QSqlRelationalDelegate. "255" looks suspiciously like a one-byte-storage for the saved current item, maybe....

If you're struggling to resolve this: I don't much like combo boxes with like > 255 items in them. I know QSqlRelationalDelegate does a combobox. Have you considered/would it work for your users if you wrote your own QItemDelegate (can't be much work) to use QCompleter instead? Then the issue of saving an index would go away.

[dheerendra]

@Seven-Zeng can you put your code in pastebin or somewhere else ? I can check and update you.

[Seven.Zeng]

@dheerendra

Hi, dheerendra

see if you can download the code from here

https://pan.baidu.com/s/13h2FJHTcoRhR8hlFFTNyyg

[Seven.Zeng]

@JonB

Hi JonB,

Thanks for your suggestion, I'll have a try, but I prefer to solve this issue if possible as it makes the code more simple and clean,

and you can have a look at my code to see if you have any further suggestion:

https://pan.baidu.com/s/13h2FJHTcoRhR8hlFFTNyyg

[dheerendra]

I tried download. Since it was in unknown language, i did not where to click & what to click. Can you upload in pastebin or somewhere else ?

[Seven.Zeng]

@dheerendra

I was hoping to paste the whole package here which contains a database file for test, it seems that the pastebin can only accept text, so let me just paste the code here, it's very simple:

---------------main.cpp---------------------

#include "mainwindow.h"
#include <QApplication>

int main(int argc, char *argv[])
{
    QApplication a(argc, argv);
    MainWindow w;
    w.show();

    return a.exec();
}

--------------mainwindow.h-------------------

#ifndef MAINWINDOW_H
#define MAINWINDOW_H

#include <QMainWindow>
#include <QSqlDatabase>
#include <QSqlRelationalTableModel>

namespace Ui {
class MainWindow;
}

class MainWindow : public QMainWindow
{
    Q_OBJECT

public:
    explicit MainWindow(QWidget *parent = nullptr);
    ~MainWindow();

protected:
    QSqlDatabase mDB;
    QSqlRelationalTableModel* mModel;

private:
    Ui::MainWindow *ui;
};

#endif // MAINWINDOW_H

----------------------mainwindow.cpp----------------------

#include "mainwindow.h"
#include "ui_mainwindow.h"
#include <QSqlRelationalDelegate>

MainWindow::MainWindow(QWidget *parent) :
    QMainWindow(parent),
    ui(new Ui::MainWindow)
{
    ui->setupUi(this);

    mDB = QSqlDatabase::addDatabase("QSQLITE");

    mModel = new QSqlRelationalTableModel(this, mDB);
    QTableView* pView;
    pView = ui->tableView;
    pView->setModel(mModel);
    pView->setItemDelegate(new QSqlRelationalDelegate(pView));
    pView->setSortingEnabled(true);

    mDB.setDatabaseName("D:\\Projects\\ContractManager\\QtDev\\build-ContractManager-Desktop_Qt_5_11_2_MSVC2017_64bit-Debug\\dummy.db");
    mDB.open();
    mModel->setTable("MainTable");
    mModel->setEditStrategy(QSqlTableModel::OnManualSubmit);
    mModel->setRelation(0, QSqlRelation("Type", "Name", "Name"));
    mModel->setRelation(1, QSqlRelation("ID", "Name", "Name"));

    mModel->select();
}

MainWindow::~MainWindow()
{
    delete ui;
}

-----------------mainwindow.ui-----------------

<?xml version="1.0" encoding="UTF-8"?>
<ui version="4.0">
 <class>MainWindow</class>
 <widget class="QMainWindow" name="MainWindow">
  <property name="geometry">
   <rect>
    <x>0</x>
    <y>0</y>
    <width>684</width>
    <height>448</height>
   </rect>
  </property>
  <property name="windowTitle">
   <string>MainWindow</string>
  </property>
  <widget class="QWidget" name="centralWidget">
   <layout class="QHBoxLayout" name="horizontalLayout">
    <item>
     <widget class="QTableView" name="tableView"/>
    </item>
   </layout>
  </widget>
  <widget class="QMenuBar" name="menuBar">
   <property name="geometry">
    <rect>
     <x>0</x>
     <y>0</y>
     <width>684</width>
     <height>23</height>
    </rect>
   </property>
  </widget>
  <widget class="QToolBar" name="mainToolBar">
   <attribute name="toolBarArea">
    <enum>TopToolBarArea</enum>
   </attribute>
   <attribute name="toolBarBreak">
    <bool>false</bool>
   </attribute>
  </widget>
  <widget class="QStatusBar" name="statusBar"/>
 </widget>
 <layoutdefault spacing="6" margin="11"/>
 <resources/>
 <connections/>
</ui>

----------------database schema--------------------------

Database: [dummy]
    File name: D:\Projects\ContractManager\QtDev\build-ContractManager-Desktop_Qt_5_11_2_MSVC2017_64bit-Debug\dummy.db
    File size: 40960 bytes
    Page size: 4096
    Encoding: UTF-8
    Auto vacuum: 0
    Tables: 4
    Views: 0
    Virtual Tables: 0
------------------------------------------------------------
Table [ID]
    Fields: 1
        [Name]: TEXT
    Indexes: 0
    Triggers: 0
    Table constraints: 
        Primary Key: 
            Fields: [Name]
            On Conflict: 
        Foreign Keys: 0
        Unique constraints: 0
        Check constraints: 0
Table [ID] end
------------------------------------------------------------
Table [MainTable]
    Fields: 2
        [Type]: TEXT
        [ID]: TEXT
    Indexes: 0
    Triggers: 0
    Table constraints: 
        Primary Key: 
            Fields: 
            On Conflict: 
        Foreign Keys: 2
          [] ([Type]) REFERENCES [Type]([Name])
          [] ([ID]) REFERENCES [ID]([Name])
        Unique constraints: 0
        Check constraints: 0
Table [MainTable] end
------------------------------------------------------------
Table [sqlite_master]
    Fields: 5
        [type]: TEXT
        [name]: TEXT
        [tbl_name]: TEXT
        [rootpage]: INTEGER
        [sql]: TEXT
    Indexes: 0
    Triggers: 0
    Table constraints: 
        Primary Key: 
            Fields: 
            On Conflict: 
        Foreign Keys: 0
        Unique constraints: 0
        Check constraints: 0
Table [sqlite_master] end
------------------------------------------------------------
Table [Type]
    Fields: 1
        [Name]: TEXT
    Indexes: 0
    Triggers: 0
    Table constraints: 
        Primary Key: 
            Fields: [Name]
            On Conflict: 
        Foreign Keys: 0
        Unique constraints: 0
        Check constraints: 0
Table [Type] end
------------------------------------------------------------