[SOLVED]Sqlite-view model on combobox Problem



  • Hello Members,

    What makes this code incompatible with my QSqlRelational model on Sqlite db:

        parInd = model->fieldIndex("stPar");
        model->setRelation(parInd, QSqlRelation("allParents", "parID", "parName"));
        QSqlTableModel *parModel = model->relationModel(parInd);
        ui->stParent->setModel(parModel);
        ui->stParent->setModelColumn(parModel->fieldIndex("parName"));
    

    The 'allParents' is a view and the column 'parName' adds two columns("parname1||' '||parname2") on the sqlite database ...the code was working with MS SQL Server on the same view. When the form is opened, all comboboxes displays with their foreign key values using the default *QSqlDelegate *but then leaves all lineEdits null. The "moveNext" buttons shows that the main *mapper *navigates between the rows. What is causing this?

    Thanks in Advance


  • Lifetime Qt Champion

    Hi,

    Did you check that the database setup you are using with MS SQL is also valid with SQLite ?



  • Thanks SGaist,

    Both databases and their elements are identical to each other! I just decided to move from MS SQL to SQLite because of the latter's flexibility . If there is a special setting for SQLite please advise. I only changed the connection statement.

    When I change the 'allParents' view to the table 'tblParents on the setRelation line'....all fields are presented with data however, I would love the the combobox to display both 'name1' and 'name2' fields from the table.


  • Lifetime Qt Champion

    Did you check that your view works correctly with the sqlite command line tool ?



  • yes, The view works correctly and I can retrieve the result with navicat and it works as expected!


  • Lifetime Qt Champion

    Can you show a minimal compilable sample code that reproduces this problem ?



  • @SGaist
    Here a sample code different from the other but with the same problem

    #include "usersetup.h"
    #include "ui_usersetup.h"
    
    userSetup::userSetup(QWidget *parent) :
        QDialog(parent),
        ui(new Ui::userSetup)
    {
        ui->setupUi(this);
        setupModel();
        setupMapper();
    //...
    }
    void userSetup::setupModel()
    {
        model = new QSqlRelationalTableModel(this);
        model->setEditStrategy(QSqlTableModel::OnManualSubmit);
        model->setTable("tblUsers");
        model->setFilter("tblUsers.actv1=1");
    
        model->database().transaction();
        if(model->submitAll())
        {
            model->database().commit();
        }
        else
        {
            model->database().rollback();
    
        }
                             //* this works*
    //    userInd = model->fieldIndex("userID");
    //    model->setRelation(userInd, QSqlRelation("tblTeachers", "trID", "trName1"));
    // *tblTeachers is a table*
    //    QSqlTableModel *userModel = model->relationModel(userInd);
    //    ui->user->setModel(userModel);
    //    ui->user->setModelColumn(userModel->fieldIndex("trName1"));
    //*   this does not work*
        userInd = model->fieldIndex("userID");
        model->setRelation(userInd, QSqlRelation("allTeachers", "trID", "trName")); //*allTeachers is a view *
        QSqlTableModel *userModel = model->relationModel(userInd);
        ui->user->setModel(userModel);
        ui->user->setModelColumn(userModel->fieldIndex("trName"));
    
        secInd = model->fieldIndex("security");
        model->setRelation(secInd, QSqlRelation("refSecurity", "seID", "scName"));
        QSqlTableModel *secModel = model->relationModel(secInd);
        ui->level->setModel(secModel);
        ui->level->setModelColumn(secModel->fieldIndex("scName"));
    
        if (!model->select()) {
            showError(model->lastError());
            return;
        }
    }
    void userSetup::setupMapper()
    {
        mapper = new QDataWidgetMapper(this);
        mapper->setModel(model);
        mapper->setItemDelegate(new QSqlRelationalDelegate(mapper));
        mapper->addMapping(ui->userID, model->fieldIndex("usNo"));
        mapper->addMapping(ui->user, userInd);
        mapper->addMapping(ui->name, model->fieldIndex("logName"));
        mapper->addMapping(ui->level, secInd);
        mapper->addMapping(ui->pass, model->fieldIndex("passWord"));
        mapper->addMapping(ui->confirm, model->fieldIndex("confirm"));
        mapper->toFirst;
    }
    void userSetup::showError(const QSqlError &err)
    {
        QMessageBox::critical(this, "Unable to initialize Database",
                              "Error initializing database: " + err.text());
    }
    

    When I use an sqlite view on the combobox...all other widgets loose their contents except comboboxes and I can navigate between the rows...meaning there could be an issue with default sql delegate . But even when I use a custom delegate, the behavior is the same. I dont want to use the table: tblTeachers; because I need both names to be shown on the combobox . The view does the addition of the two names. I am running out of options on this...I will be grateful if you help



  • I had to do it the hard way. This is to every one who could be facing the same challenge. I have come to conclude that Sqlite Views cannot work with QSqlRelationalDelegate when 'edit flags have been raised'. You have to write a custom delegate to handle fields of added text (i.e name1||' '||name2). I tried using another SQlite View without added fields still it couldn't allow editing. This could be due to the fact that SQlite Views are ReadOnly. Conversely, I was using Views with MS SQL Server and it was perfect. Below is my delegate that i used to handle a field from SQlite View

    #ifndef DELSETUSER
    #define DELSETUSER
    #include <QtWidgets>
    #include <QtSql>
    
    class delSetUser : public QSqlRelationalDelegate
    {
        Q_OBJECT
    public:
        delSetUser(QObject *parent=0):QSqlRelationalDelegate(parent){
            QString str("SELECT trID, trName FROM allTch WHERE trNo>0"); 
    //allTch is a View which I created using navicate for Sqlite
            tch = new QSqlQueryModel();
            tch->setQuery(str);
            allt = tch->rowCount();
        }
    
        void setEditorData(QWidget *editor, const QModelIndex &index) const
        {
            if(index.column()==2)
            { QComboBox *combobox = qobject_cast<QComboBox*>(editor);
                Q_ASSERT(combobox);
    
                for(int x = 0;x<allt;++x)
                {
                    QModelIndex ID = tch->index(x,0);
                    QModelIndex name = tch->index(x,1);
                    QString fulname = QString(name.data(Qt::DisplayRole).toString());
                    int id = ID.data().toInt();
    
                    if(index.data().toInt() == id){
                        int cbIndex = combobox->findText(fulname);
                        if(cbIndex >= 0)
                            combobox->setCurrentIndex(cbIndex);
                    }
                }
            }
            else {
                QSqlRelationalDelegate::setEditorData(editor, index);
            }
        }
        void setModelData(QWidget *editor, QAbstractItemModel *model, const QModelIndex &index) const
        {
            if(index.column()==2)
            { QComboBox *combbox = qobject_cast<QComboBox*>(editor);
                Q_ASSERT(combbox);
    
                for(int x = 0;x<allt;++x)
                {
                    QModelIndex ID = tch->index(x,0);
                    QModelIndex name = tch->index(x,1);
                    QString fulname = QString(name.data().toString());
                    int id = ID.data().toInt();
    
                    if(combbox->currentText() == fulname)
                        model->setData(index,id);
                }
            }
           else
                QSqlRelationalDelegate::setModelData(editor, model, index);
        }
    private:
        QSqlQueryModel *tch;
        int allt;
    };
    #endif // DELSETUSER
    
    

    In the constructor of my class where the delegate is used I populated the combobox with the following function:

    void userSetup::userModel()
    {
        QString str("SELECT trID, trName FROM allTch WHERE trNo>0");
        QSqlQueryModel *tch = new QSqlQueryModel();
        tch->setQuery(str);
        ui->user->setModel(tch);
        ui->user->setModelColumn(1);
    }
    

  • Lifetime Qt Champion

    Thanks for sharing your findings !

    It indeeds make sense since the views are read-only

    Since you have a workaround, can you please update the thread title prepending [solved] so other forum users may know a solution has been found :)


Log in to reply
 

Looks like your connection to Qt Forum was lost, please wait while we try to reconnect.