Skip to content
  • Categories
  • Recent
  • Tags
  • Popular
  • Users
  • Groups
  • Search
  • Get Qt Extensions
  • Unsolved
Collapse
Brand Logo
  1. Home
  2. Qt Development
  3. General and Desktop
  4. [SOLVED]Sqlite-view model on combobox Problem
QtWS25 Last Chance

[SOLVED]Sqlite-view model on combobox Problem

Scheduled Pinned Locked Moved General and Desktop
sqlite viewcombobox model
9 Posts 2 Posters 3.7k Views
  • Oldest to Newest
  • Newest to Oldest
  • Most Votes
Reply
  • Reply as topic
Log in to reply
This topic has been deleted. Only users with topic management privileges can see it.
  • KyefK Offline
    KyefK Offline
    Kyef
    wrote on last edited by Kyef
    #1

    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

    Kyef Elliot
    If Can Imagine It...I Can Do It!!

    1 Reply Last reply
    0
    • SGaistS Offline
      SGaistS Offline
      SGaist
      Lifetime Qt Champion
      wrote on last edited by
      #2

      Hi,

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

      Interested in AI ? www.idiap.ch
      Please read the Qt Code of Conduct - https://forum.qt.io/topic/113070/qt-code-of-conduct

      1 Reply Last reply
      0
      • KyefK Offline
        KyefK Offline
        Kyef
        wrote on last edited by
        #3

        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.

        Kyef Elliot
        If Can Imagine It...I Can Do It!!

        1 Reply Last reply
        0
        • SGaistS Offline
          SGaistS Offline
          SGaist
          Lifetime Qt Champion
          wrote on last edited by
          #4

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

          Interested in AI ? www.idiap.ch
          Please read the Qt Code of Conduct - https://forum.qt.io/topic/113070/qt-code-of-conduct

          1 Reply Last reply
          0
          • KyefK Offline
            KyefK Offline
            Kyef
            wrote on last edited by
            #5

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

            Kyef Elliot
            If Can Imagine It...I Can Do It!!

            1 Reply Last reply
            0
            • SGaistS Offline
              SGaistS Offline
              SGaist
              Lifetime Qt Champion
              wrote on last edited by
              #6

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

              Interested in AI ? www.idiap.ch
              Please read the Qt Code of Conduct - https://forum.qt.io/topic/113070/qt-code-of-conduct

              KyefK 1 Reply Last reply
              0
              • SGaistS SGaist

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

                KyefK Offline
                KyefK Offline
                Kyef
                wrote on last edited by Kyef
                #7

                @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 Elliot
                If Can Imagine It...I Can Do It!!

                1 Reply Last reply
                0
                • KyefK Offline
                  KyefK Offline
                  Kyef
                  wrote on last edited by
                  #8

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

                  Kyef Elliot
                  If Can Imagine It...I Can Do It!!

                  1 Reply Last reply
                  0
                  • SGaistS Offline
                    SGaistS Offline
                    SGaist
                    Lifetime Qt Champion
                    wrote on last edited by
                    #9

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

                    Interested in AI ? www.idiap.ch
                    Please read the Qt Code of Conduct - https://forum.qt.io/topic/113070/qt-code-of-conduct

                    1 Reply Last reply
                    0

                    • Login

                    • Login or register to search.
                    • First post
                      Last post
                    0
                    • Categories
                    • Recent
                    • Tags
                    • Popular
                    • Users
                    • Groups
                    • Search
                    • Get Qt Extensions
                    • Unsolved