[SOLVED]Sqlite-view model on combobox Problem

Kyef

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

SGaist

Hi,

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

Kyef

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.

SGaist

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

Kyef

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

SGaist

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

Kyef

@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

Kyef

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);
}

SGaist

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 :)