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

Core2

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!

Core2

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...

Core2

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.

donnpie

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?

VRonin

Can you show us your code?

donnpie

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

SGaist

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.

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?

JonB

@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.

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!

JonB

@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.

SGaist

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

donnpie

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
}

donnpie

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.

donnpie

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.

SGaist

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

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?

SGaist

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.

donnpie

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

mathieu92

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