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. QTableView displays correct rows and columns but no data in cells.
QtWS25 Last Chance

QTableView displays correct rows and columns but no data in cells.

Scheduled Pinned Locked Moved Solved General and Desktop
20 Posts 6 Posters 5.0k 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.
  • C Offline
    C Offline
    Core2
    wrote on last edited by
    #1

    Hello all,

    I'm having a problem displaying data in a QTableView using the QSqlQueryModel class read-only model. The tableview shows the correct number of rows and columns but contains no data from the query. (Well the headers would be some of that data.)
    I can connect to the db and am successfully collecting information from the db. I know this because I perform this section of code to inspect the data I have collected:

    int rows = model->rowCount();
    int columns = model->columnCount();
    qDebug() << "Number of rows" << rows;
    qDebug() << "Number of columns" << columns;
    
    for(int i=0; i <=rows-1; i++)
    {
        for(int k=0; k <= columns-1; k++)
            {
                QString data = model->record(i).value(k).toString();
                qDebug() << "Record "<< i+1 << "," << k+1 << "of model " << data << "!";
            }
    }
    

    Once I have collected the information from the db, this code is executed to display the data in the QTableView named "tableView"

    QSqlQueryModel *model = new QSqlQueryModel;
    model->setQuery(queryvar);
    ui->tableView->setModel(model);
    

    The perplexing thing is the tableView is updated with the number of rows of data and the correct number of columns with the correct header titles. But no data in the cells.
    I was following this guys tutorial: https://www.youtube.com/watch?v=fJIdMdYJqFk
    and reading up on model view programming: http://doc.qt.io/qt-5/modelview.html
    So far nothing has clicked.

    Does anyone have some advice for me? Thanks!

    1 Reply Last reply
    0
    • C Offline
      C Offline
      Core2
      wrote on last edited by
      #2

      I started a new project and started from scratch doing exactly what VoidRelms did in his video: https://www.youtube.com/watch?v=fJIdMdYJqFk

      It worked, using the exact same query that I was using in my previous project.

      So I have some figuring to do.

      One difference is that with his tutorial i selected dialog. For my original project I selected something different.

      More to come...

      1 Reply Last reply
      0
      • C Offline
        C Offline
        Core2
        wrote on last edited by
        #3

        Since starting with QT I have been using the same project to learn and develop in. It became a Frankenstein monster of a program. I started a new project and cleaned up my code. It is now. working. However I think I need to removed the QSqlQueryModel *model = new QSqlQueryModel; of code. Since I started a new project and removed this line of code everything is working as it should.

        1 Reply Last reply
        0
        • D Offline
          D Offline
          donnpie
          wrote on last edited by
          #4

          It's two years after this thread was first started, but I've just started with Qt and I'm experiencing the exact same issue. My tableView displays the correct number of rows and columns, and it also displays the correct column header text, but the cells in the body of the table are blank. Any ideas what is causing this and how to fix?

          1 Reply Last reply
          0
          • VRoninV Offline
            VRoninV Offline
            VRonin
            wrote on last edited by
            #5

            Can you show us your code?

            "La mort n'est rien, mais vivre vaincu et sans gloire, c'est mourir tous les jours"
            ~Napoleon Bonaparte

            On a crusade to banish setIndexWidget() from the holy land of Qt

            1 Reply Last reply
            0
            • D Offline
              D Offline
              donnpie
              wrote on last edited by
              #6

              Here is the code:

              #ifndef MAINWINDOW_H
              #define MAINWINDOW_H
              //mainwindow.h
              #include <QMainWindow>
              #include <QtSql>
              
              namespace Ui {
              class MainWindow;
              }
              
              class MainWindow : public QMainWindow
              {
                  Q_OBJECT
              public:
                  explicit MainWindow(QWidget *parent = nullptr);
                  ~MainWindow();
              private:
                  Ui::MainWindow *ui;
                  QSqlDatabase db;
                  QSqlQuery *qry;
                  QSqlQueryModel *model;
              };
              #endif // MAINWINDOW_H
              
              //mainwindow.cpp
              #include "mainwindow.h"
              #include "ui_mainwindow.h"
              #include <QString>
              #include <QDebug>
              
              MainWindow::MainWindow(QWidget *parent) :
                  QMainWindow(parent),
                  ui(new Ui::MainWindow)
              {
                  ui->setupUi(this);
              
                  //The following code was adapted from the following examples:
                  //https://youtu.be/OrHitqwJu8Q
                  //https://youtu.be/fJIdMdYJqFk
                  
                  //Set up the connection
                  QString dbName = "Plants"; //Name of the database
                  QString serverName = "DESKTOP-F7RC79B\\SQLEXPRESS"; //Name of host machine and server
                  db = QSqlDatabase::addDatabase("QODBC"); //Specify the driver
                  db.setConnectOptions(); //AFAIK this sets up a default connection
                  //Connection string:
                  QString conString = QString("DRIVER={SQL Server};SERVER=%1;DATABASE=%2;"
                                                 "Trusted_Connection=Yes;").arg(serverName).arg(dbName);
                  db.setDatabaseName(conString); //Pass connection string to db object
              
                  if (db.open())
                  {
                      qDebug() << "Connection opened successfully!";
              
                      qry = new QSqlQuery(db); //instantiate new query object and bind db object to query object
                      qry->prepare("SELECT * FROM Batches"); //Pass query statement to query object
                      qry->exec(); //Run the query
              
                      model = new QSqlQueryModel(this); //instantiate new query model object
                      model->setQuery(*qry); //Transfer query results to model
                      qDebug() << "Number of records returned: " << model->rowCount();
                      //Should return 4 rows
                      ui->tableView->setModel(model); //Bind the model to the tableview object
              
                  }
                  else {
                      qDebug() << "Connection error: " << db.lastError().text();
                  }
              
                  db.close();
                  qDebug() << "Connection closed successfully!";
              
              
              }
              
              MainWindow::~MainWindow()
              {
                  delete ui;
                  delete qry;
                  delete model;
              }
              
              //main.cpp
              #include "mainwindow.h"
              #include <QApplication>
              //The purpose of this program is to demonstrate how to display query results in a table view
              
              int main(int argc, char *argv[])
              {
                  QApplication a(argc, argv);
                  MainWindow w;
                  w.show();
              
                  return a.exec();
              }
              
              

              When I run the program, this is what I see:
              0_1563563951567_b97cb463-824d-47ab-ab88-ce9da085f0b6-image.png

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

                Hi,

                @donnpie said in QTableView displays correct rows and columns but no data in cells.:

                qry = new QSqlQuery(db); //instantiate new query object and bind db object to query object
                qry->prepare("SELECT * FROM Batches"); //Pass query statement to query object
                qry->exec(); //Run the query
                
                model->setQuery(*qry); //Transfer query results to model
                

                That's wrong:

                • Don't allocate that QSqlQueryObject on the heap
                • Don't execute it before setting it on the model

                Base on your code, you only have one connection so:

                model->setQuery("SELECT * FROM Batches");
                

                is enough.

                Or you can also use a QSqlTableModel since you want to grab the content of the table anyway.

                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
                1
                • D Offline
                  D Offline
                  donnpie
                  wrote on last edited by donnpie
                  #8

                  Hi @SGaist, thanks for the advice. However, I tried it and it did not work. I still get the same result. I modified the code like so:

                  //old code
                          /*
                          qry = new QSqlQuery(db); //instantiate new query object and bind db object to query object
                          qry->prepare("SELECT * FROM Batches"); //Pass query statement to query object
                          qry->exec(); //Run the query
                          model = new QSqlQueryModel(this); //instantiate new query model object
                          model->setQuery(*qry); //Transfer query results to model
                          qDebug() << "Number of records returned: " << model->rowCount();
                          //Should return 4 rows
                          ui->tableView->setModel(model); //Bind the model to the tableview object
                          */
                  
                  //new code
                          model = new QSqlQueryModel(this); //Without this line code crashes
                          model->setQuery("SELECT * FROM Batches");
                          ui->tableView->setModel(model);
                  

                  What am I doing wrong?

                  JonBJ 1 Reply Last reply
                  0
                  • D donnpie

                    Hi @SGaist, thanks for the advice. However, I tried it and it did not work. I still get the same result. I modified the code like so:

                    //old code
                            /*
                            qry = new QSqlQuery(db); //instantiate new query object and bind db object to query object
                            qry->prepare("SELECT * FROM Batches"); //Pass query statement to query object
                            qry->exec(); //Run the query
                            model = new QSqlQueryModel(this); //instantiate new query model object
                            model->setQuery(*qry); //Transfer query results to model
                            qDebug() << "Number of records returned: " << model->rowCount();
                            //Should return 4 rows
                            ui->tableView->setModel(model); //Bind the model to the tableview object
                            */
                    
                    //new code
                            model = new QSqlQueryModel(this); //Without this line code crashes
                            model->setQuery("SELECT * FROM Batches");
                            ui->tableView->setModel(model);
                    

                    What am I doing wrong?

                    JonBJ Offline
                    JonBJ Offline
                    JonB
                    wrote on last edited by JonB
                    #9

                    @donnpie
                    Frankly I would have expected code as shown to work. You ought put those qDebug() statements back in, and perhaps check model.lastError().isValid()), so that we are sure it's returning what you think.

                    Your code executes the query on the model (setQuery("...")) and then attaches it to the view. I think that's OK, but it's just possible that the view does not see rows arrive that way and so shows nothing, I can't recall. If you swap the order:

                            ui->tableView->setModel(model);
                            model->setQuery("SELECT * FROM Batches");
                    

                    does that make any difference to the view display?

                    EDIT Rethink. I don't think it's the order (though you might as well try it)?. You said "the model returns 4 rows". Your QTableView is showing in your picture that it has 4 rows, but no visible data in the cells. I think you need to show us just what values are in the columns of the model rows (qDebug() them out via model->data()), so we can be sure they have a non-empty string representation for the QTableView.

                    1 Reply Last reply
                    1
                    • D Offline
                      D Offline
                      donnpie
                      wrote on last edited by
                      #10

                      Hi @JonB ,
                      model.lastError().isValid()) returns false, so it seems there are no errors. (BTW, how do you make that inline code pink?) When I swap the two lines of code as you have suggested, I still get the same result.

                      There are definitely 4 rows of data in the table; see the SSMS screenshot below:
                      0_1563626466556_6e371415-8b7a-421a-a7bc-845bcf311185-image.png

                      The data also seems to pull correctly into the model. I've modified the code like so:

                      //new code
                              model = new QSqlQueryModel(this); //Without this line code crashes
                              model->setQuery("SELECT * FROM Batches");
                              ui->tableView->setModel(model);
                      
                              qDebug() << "LastError is valid?: " << model->lastError().isValid();
                              qDebug() << "First column output: ";
                              for (int i = 0; i < model->rowCount(); i++)
                              {
                                  qDebug() << model->data(model->index(i, 0)).toInt();
                              }
                      

                      and this is the output I get in QDebug:

                      Connection opened successfully!
                      LastError is valid?: false
                      First column output:
                      1
                      2
                      3
                      4
                      Connection closed successfully!

                      JonBJ 1 Reply Last reply
                      0
                      • D donnpie

                        Hi @JonB ,
                        model.lastError().isValid()) returns false, so it seems there are no errors. (BTW, how do you make that inline code pink?) When I swap the two lines of code as you have suggested, I still get the same result.

                        There are definitely 4 rows of data in the table; see the SSMS screenshot below:
                        0_1563626466556_6e371415-8b7a-421a-a7bc-845bcf311185-image.png

                        The data also seems to pull correctly into the model. I've modified the code like so:

                        //new code
                                model = new QSqlQueryModel(this); //Without this line code crashes
                                model->setQuery("SELECT * FROM Batches");
                                ui->tableView->setModel(model);
                        
                                qDebug() << "LastError is valid?: " << model->lastError().isValid();
                                qDebug() << "First column output: ";
                                for (int i = 0; i < model->rowCount(); i++)
                                {
                                    qDebug() << model->data(model->index(i, 0)).toInt();
                                }
                        

                        and this is the output I get in QDebug:

                        Connection opened successfully!
                        LastError is valid?: false
                        First column output:
                        1
                        2
                        3
                        4
                        Connection closed successfully!

                        JonBJ Offline
                        JonBJ Offline
                        JonB
                        wrote on last edited by JonB
                        #11

                        @donnpie
                        OK, at this point you seem to be in inexplicable territory, from what I have seen & others we're spotting anything obvious. You seem to know what you're doing in your code/answers, so that's good!

                        The one bit I don't know about is your ui->tableView. I don't do any Creator design-time stuff, make absolutely sure there isn't something "funny" about that table view.

                        So, if it were me, it's time to start divide-and-conquer. Split it up till you spot something, e.g.

                        • Move it to a standalone project, with the bare-minimum code. Get rid of all the extraneous lines that clutter up the code, so you can paste entirety here if you have to.

                        • Replace the SQL model with a QStandardItemModel, populated by hand. That tells you whether it's a SQL thing or a model thing.

                        • Replace the QTableView with a QComboBox tied to one column, That tells you whether it's a table view thing.

                        By the time you've done the above, it must be working...!

                        BTW, if you are able to sub-class the model you're using, you can override its data() method, and there you should be seeing all the calls the view is making to display stuff and what is being returned from the model.

                        (BTW, how do you make that inline code pink?)

                        There's no pinko in what I write, only red! backtick-word-backtick == word.

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

                          When and why are you closing the connection to the database ?

                          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
                          1
                          • D Offline
                            D Offline
                            donnpie
                            wrote on last edited by
                            #13

                            OK, so I've created a QStandardItemModel and passed that to the QTableView, and things are working well. I can see the text in the body of the table. My QTableView object is bog-standard - I did not change any of its properties.

                            0_1563693333788_d56953dc-53cf-4294-a24a-bc15e32f0002-image.png

                            I changed the query to return a single column and passed it to a new QListView (below the table). As you can see, the listview came up blank. When I hover my mouse over the listview, the rows shade blue, which is telling me that either there are empty rows there or the font of the rows is the same colour as the background. However I do not think it is a font colour issue, because when I click on a row, the backround colour changes to blue, but the text is still not visible. So at this stage I think we can rule out the tableview. It looks like there is an issue with my query/model. For some reason, the model ends up with empty rows.

                            I thought maybe it had something to do with the fact that I created the SQLQueryModel on the heap, but that does not seem to be the case since the stdItemModel was also created on the heap and seems to be working just fine.

                            My only working theory at this stage: Maybe there is a timing issue and the model is passed to the ui before the results are properly populated in the model, so effectively I'm passing an empty model to the ui.

                            Here is the code I used:

                            MainWindow::MainWindow(QWidget *parent) :
                                QMainWindow(parent),
                                ui(new Ui::MainWindow)
                            {
                                ui->setupUi(this);
                            
                                //SQL code
                                //Set up the connection
                                QString dbName = "Plants"; //Name of the database
                                QString serverName = "DESKTOP-F7RC79B\\SQLEXPRESS"; //Name of host machine and server
                                db = QSqlDatabase::addDatabase("QODBC"); //Specify the driver
                                db.setConnectOptions(); //AFAIK this sets up a default connection
                                //Connection string:
                                QString conString = QString("DRIVER={SQL Server};SERVER=%1;DATABASE=%2;"
                                                               "Trusted_Connection=Yes;").arg(serverName).arg(dbName);
                                db.setDatabaseName(conString); //Pass connection string to db object
                            
                                if (db.open())
                                {
                                    qDebug() << "Connection opened successfully!";
                            
                                    //SQL query model
                                    model = new QSqlQueryModel(this);
                                    model->setQuery("SELECT BatchID FROM Batches"); //Changed query to return single column
                                    //ui->tableView->setModel(model);
                                    ui->listView->setModel(model); //test code: pass the model to the listview
                            
                                    qDebug() << "LastError is valid?: " << model->lastError().isValid();
                                    qDebug() << "First column output: ";
                                    for (int i = 0; i < model->rowCount(); i++)
                                    {
                                        qDebug() << model->data(model->index(i, 0)).toInt();
                                    }
                            
                            
                                }
                                else {
                                    qDebug() << "Connection error: " << db.lastError().text();
                                }
                            
                                db.close();
                                qDebug() << "Connection closed successfully!";
                            
                                //Create a std item model by hand
                                QStandardItemModel *stdModel = new QStandardItemModel(this);
                                int rowCount = 3;
                                int colCount = 3;
                                stdModel->setRowCount(rowCount);
                                stdModel->setColumnCount(colCount);
                            
                                for (int i = 0; i < rowCount; i++)
                                {
                                    for (int j = 0; j < colCount; j++)
                                    {
                                        QStandardItem *stdItem = new QStandardItem();
                                        stdItem->setText(QString::number(i+j));
                                        stdModel->setItem(i, j, stdItem);
                                    }
                                }
                            
                                ui->tableView->setModel(stdModel); //Pass the stdModel to the tableview
                            }
                            
                            1 Reply Last reply
                            0
                            • D Offline
                              D Offline
                              donnpie
                              wrote on last edited by
                              #14

                              On second thought, the model can't be empty because I am able to successfully output the data to qDebug. I've changed the sequence of the code to print the data to qDebug first and then to listview. It prints to qDebug but still does not show up in the listview. So the model does have data in it, but the data does not show up in the listview.

                              1 Reply Last reply
                              0
                              • D Offline
                                D Offline
                                donnpie
                                wrote on last edited by
                                #15

                                I thought maybe it is a machine issue, so I tried it on a different machine with a different database. I still get the same results: the header text shows up but the body text is missing. Could it be a Windows issue? The first machine is running Windows 10 Pro and the second machine is running Windows 10 Enterprise.

                                Other system information:
                                Both machines are 64-bit. Both machines run Qt Creator 4.9.1 (Based on Qt 5.12.3 (MSVC 2017, 32 bit), Built on May 26 2019 21:17:08, From revision 168e91b618). Compilers for both machines are MinGW 7.3.0, 32-bit. Qt versions for both machines are 5.13.0. First machine is running SSMS v17.8.1 and second machine is running SSMS v18.1.

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

                                  Again: why are you closing the connection to the database ?

                                  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
                                  2
                                  • D Offline
                                    D Offline
                                    donnpie
                                    wrote on last edited by
                                    #17

                                    Again: why are you closing the connection to the database ?

                                    Hi @SGaist, I figured I was done with the connection, so no further need for it.

                                    Holy crap, I just commented out db.close(); and it worked!!! Thank you very much @SGaist.

                                    Although this fixes the problem I don't understand why. Would you mind explaining why the db connection should remain open, and when to close it?

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

                                      So your model can grab more data on demand. If you use a QSqlTableModel, it won't load 100% of the table content in one go for exemple.

                                      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
                                      1
                                      • D Offline
                                        D Offline
                                        donnpie
                                        wrote on last edited by
                                        #19

                                        Ok, I see. Makes sense. Thank you all for the help.

                                        1 Reply Last reply
                                        0
                                        • D donnpie

                                          Again: why are you closing the connection to the database ?

                                          Hi @SGaist, I figured I was done with the connection, so no further need for it.

                                          Holy crap, I just commented out db.close(); and it worked!!! Thank you very much @SGaist.

                                          Although this fixes the problem I don't understand why. Would you mind explaining why the db connection should remain open, and when to close it?

                                          M Offline
                                          M Offline
                                          mathieu92
                                          wrote on last edited by
                                          #20

                                          @donnpie removing the close connection worked for me as well. Thanks!!!

                                          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