Help newbie with QTableView...

[pnunn]

Hi folks... firstly let me appologize for such a newbie question.

I am transitioning from C++ Builder on Windows and am struggling a bit with the way things are done in QT.

I have a simple form with a QTableView on it and want to display some data from my database in the View.

I have followed some code from "The Book fo QT4" and have the data coming out of the query OK, but the View remains totally empty.

The code I'm using is as follows

MainWindow::MainWindow(QWidget *parent)
    : QMainWindow(parent), ui(new Ui::MainWindowClass)
{
    ui->setupUi(this);
 
    QSqlQuery query("select p.pricedate, p.valuecol, p.totalvalue, s.sharename from pricedata p inner join shareid s on p.shareid = s.shareid");
    QSqlRecord record = query.record();
    while(query.next())
    {
        //loop
        QString res;
        res = query.value(record.indexOf("sharename")).toString() + " : ";
        res += query.value(record.indexOf("totalvalue")).toString();
        qDebug() << query.at() << ": " << res;
    }
 
    QSqlQueryModel queryModel;
    queryModel.setQuery("select p.pricedate, p.valuecol, p.totalvalue, s.sharename from pricedata p inner join shareid s on p.shareid = s.shareid");
    if(queryModel.lastError().isValid())
        qDebug() << queryModel.lastError();
 
    ui->tableView->setModel(&queryModel);
    queryModel.setHeaderData(0, Qt::Horizontal, QObject::tr("heading 1"));
 
}

MainWindow::MainWindow(QWidget *parent)
    : QMainWindow(parent), ui(new Ui::MainWindowClass)
{
    ui->setupUi(this);

    QSqlQuery query("select p.pricedate, p.valuecol, p.totalvalue, s.sharename from pricedata p inner join shareid s on p.shareid = s.shareid");
    QSqlRecord record = query.record();
    while(query.next())
    {
        //loop
        QString res;
        res = query.value(record.indexOf("sharename")).toString() + " : ";
        res += query.value(record.indexOf("totalvalue")).toString();
        qDebug() << query.at() << ": " << res;
    }

    QSqlQueryModel queryModel;
    queryModel.setQuery("select p.pricedate, p.valuecol, p.totalvalue, s.sharename from pricedata p inner join shareid s on p.shareid = s.shareid");
    if(queryModel.lastError().isValid())
        qDebug() << queryModel.lastError();

    ui->tableView->setModel(&queryModel);
    queryModel.setHeaderData(0, Qt::Horizontal, QObject::tr("heading 1"));

}

To copy to clipboard, switch view to plain text mode 

I know the query is working because the first loop to qDebug is putting out the correct data.

Now.. I know I'm not doing this correctly and its going to be stupidly easy.. but.. I simply can't work out how to play with widgets that i've put on the main window using QtCreator's GUI maker (or whatever its called).

Any advice please??

Thanks

Peter Nunn

[jpn]

Hi, allocate the model on the heap with C++ keyword "new" so that it won't go out of the scope immediately after the constructor.

[pnunn]

JP thankyou so much.... I'm slapping myself in the head.. such an obvious problem..

Just out of interest.. is the code layout I'm using correct? Should I be doing all of this manipulation in mainwindow.cpp or should I be doing the code in some other module? Just a matter of Style I guess.

Thanks again.

Peter.

[pnunn]

Sorry... more lack of understanding...

I have now progressed a bit with this project.

I have a comboBox that I'm populating with the share name correctly and have hooked up a signal with the row count from that to a slot for the listview query to try and limit the data being displayed.

void MainWindow::updatePriceData(int idx)
{
    QSqlRecord record = comboQueryModel->record(idx);
    int shareidx = record.value("shareid").toInt();
 
    priceQueryModel = new QSqlQueryModel;
    query = new QSqlQuery;
    query->prepare("select p.pricedate, p.valuecol, p.totalvalue, s.sharename from pricedata p inner join shareid s on p.shareid = s.shareid where p.shareid = :shareidx");
    query->bindValue(":shareidx", shareidx);
    priceQueryModel->setQuery(*query);
    if(priceQueryModel->lastError().isValid())
        qDebug() << priceQueryModel->lastError();
 
    ui->tableView->setModel(priceQueryModel);
    priceQueryModel->setHeaderData(0, Qt::Horizontal, QObject::tr("heading 1"));
}
 
void MainWindow::populateGroupBox(void)
{
    comboQueryModel = new QSqlQueryModel;
    comboQueryModel->setQuery("select shareid, sharename from shareid");
    comboQueryModel->removeColumn(0);
    ui->comboBox->setModel(comboQueryModel);
}

void MainWindow::updatePriceData(int idx)
{
    QSqlRecord record = comboQueryModel->record(idx);
    int shareidx = record.value("shareid").toInt();

    priceQueryModel = new QSqlQueryModel;
    query = new QSqlQuery;
    query->prepare("select p.pricedate, p.valuecol, p.totalvalue, s.sharename from pricedata p inner join shareid s on p.shareid = s.shareid where p.shareid = :shareidx");
    query->bindValue(":shareidx", shareidx);
    priceQueryModel->setQuery(*query);
    if(priceQueryModel->lastError().isValid())
        qDebug() << priceQueryModel->lastError();

    ui->tableView->setModel(priceQueryModel);
    priceQueryModel->setHeaderData(0, Qt::Horizontal, QObject::tr("heading 1"));
}

void MainWindow::populateGroupBox(void)
{
    comboQueryModel = new QSqlQueryModel;
    comboQueryModel->setQuery("select shareid, sharename from shareid");
    comboQueryModel->removeColumn(0);
    ui->comboBox->setModel(comboQueryModel);
}

To copy to clipboard, switch view to plain text mode 

I can see the idx value changing correctly (the signal is working) but the shareidx value is always 0.

What am I missing please??

Ta

Peter.

[aamer4yu]

In line 4, is the attribute shareid ? or shud it be shareidx ??
just a guess

[pnunn]

No sadly the attribute is shareid (that is the name of the field in the table).

Peter.

[Lesiok]

But You don't get them from db. You have specified only this columns p.pricedate, p.valuecol, p.totalvalue, s.sharename. Just add 5'th column p.shareid and set them in model invisible.

[pnunn]

Thanks Lesiok but I think I am getting the value I'm trying to use in the where clause in line 21. The problem seems to be that I'm not able to recover the value from the combobox model's record based on the selected line number.


Actually, it might be something to do with the prepare statement. I tried replacing the parameter with a fixed number and it still failed to work. I've removed the text for the query and put it in the QSqlQuery constructor, with a fixed value for the parameter and it works...

So it seems there is a problem with the parameter as well perhaps.

Peter.

[pnunn]

Got some more questions with this learning if I may.

I managed to get the program working to some extent, however, I suspect I'm missing something fundamental here.

I found that removing a column in one query caused me not to be able to recover the value in another part. My understanding was that removecolumn simply hid the data from the UI, apparently this isn't the case. I fixed this by reordering the data retrieval put in the combo box model thus

void MainWindow::populateGroupBox(void)
{
    comboQueryModel = new QSqlQueryModel;
    comboQueryModel->setQuery("select sharename, shareid from shareid");
    //comboQueryModel->removeColumn(0);
    ui->comboBox->setModel(comboQueryModel);
}

void MainWindow::populateGroupBox(void)
{
    comboQueryModel = new QSqlQueryModel;
    comboQueryModel->setQuery("select sharename, shareid from shareid");
    //comboQueryModel->removeColumn(0);
    ui->comboBox->setModel(comboQueryModel);
}

To copy to clipboard, switch view to plain text mode 

Now.. the remaining issue is to do with parameterised queries.

The following works (building the string by hand each time)

void MainWindow::updatePriceData(int idx)
{
    QSqlRecord record = comboQueryModel->record(idx);
    if(record.isEmpty())
    {
        qDebug() << "Error! Record is empty";
        return;
    }
    QVariant shareidx = record.value("shareid");
    priceQueryModel = new QSqlQueryModel;
    QString string = "select p.shareid, p.pricedate, p.valuecol, p.totalvalue, s.sharename from pricedata p inner join shareid s on p.shareid = s.shareid where p.shareid =";
    string = string + shareidx.toString();
    query = new QSqlQuery(string); 
    priceQueryModel->setQuery(*query);
    if(priceQueryModel->lastError().isValid())
        qDebug() << priceQueryModel->lastError();
 
    ui->tableView->setModel(priceQueryModel);
    priceQueryModel->removeColumn(0);
    priceQueryModel->setHeaderData(0, Qt::Horizontal, QObject::tr("Closing Date"));
    priceQueryModel->setHeaderData(1, Qt::Horizontal, QObject::tr("Share Price"));
    priceQueryModel->setHeaderData(2, Qt::Horizontal, QObject::tr("Total Value"));
    priceQueryModel->setHeaderData(3, Qt::Horizontal, QObject::tr("Company"));
}

void MainWindow::updatePriceData(int idx)
{
    QSqlRecord record = comboQueryModel->record(idx);
    if(record.isEmpty())
    {
        qDebug() << "Error! Record is empty";
        return;
    }
    QVariant shareidx = record.value("shareid");
    priceQueryModel = new QSqlQueryModel;
    QString string = "select p.shareid, p.pricedate, p.valuecol, p.totalvalue, s.sharename from pricedata p inner join shareid s on p.shareid = s.shareid where p.shareid =";
    string = string + shareidx.toString();
    query = new QSqlQuery(string); 
    priceQueryModel->setQuery(*query);
    if(priceQueryModel->lastError().isValid())
        qDebug() << priceQueryModel->lastError();

    ui->tableView->setModel(priceQueryModel);
    priceQueryModel->removeColumn(0);
    priceQueryModel->setHeaderData(0, Qt::Horizontal, QObject::tr("Closing Date"));
    priceQueryModel->setHeaderData(1, Qt::Horizontal, QObject::tr("Share Price"));
    priceQueryModel->setHeaderData(2, Qt::Horizontal, QObject::tr("Total Value"));
    priceQueryModel->setHeaderData(3, Qt::Horizontal, QObject::tr("Company"));
}

To copy to clipboard, switch view to plain text mode 

This however does not work

void MainWindow::updatePriceData(int idx)
{
    QSqlRecord record = comboQueryModel->record(idx);
    if(record.isEmpty())
    {
        qDebug() << "Error! Record is empty";
        return;
    }
    QVariant shareidx = record.value("shareid");
    priceQueryModel = new QSqlQueryModel;
    query = new QSqlQuery;
    query->prepare("select p.shareid, p.pricedate, p.valuecol, p.totalvalue, s.sharename from pricedata p inner join shareid s on p.shareid = s.shareid where p.shareid = :shareidx");
    query->bindValue(":shareidx", shareidx.toInt());
    priceQueryModel->setQuery(*query);
    if(priceQueryModel->lastError().isValid())
        qDebug() << priceQueryModel->lastError();
 
    ui->tableView->setModel(priceQueryModel);
    priceQueryModel->removeColumn(0);
    priceQueryModel->setHeaderData(0, Qt::Horizontal, QObject::tr("Closing Date"));
    priceQueryModel->setHeaderData(1, Qt::Horizontal, QObject::tr("Share Price"));
    priceQueryModel->setHeaderData(2, Qt::Horizontal, QObject::tr("Total Value"));
    priceQueryModel->setHeaderData(3, Qt::Horizontal, QObject::tr("Company"));
}

void MainWindow::updatePriceData(int idx)
{
    QSqlRecord record = comboQueryModel->record(idx);
    if(record.isEmpty())
    {
        qDebug() << "Error! Record is empty";
        return;
    }
    QVariant shareidx = record.value("shareid");
    priceQueryModel = new QSqlQueryModel;
    query = new QSqlQuery;
    query->prepare("select p.shareid, p.pricedate, p.valuecol, p.totalvalue, s.sharename from pricedata p inner join shareid s on p.shareid = s.shareid where p.shareid = :shareidx");
    query->bindValue(":shareidx", shareidx.toInt());
    priceQueryModel->setQuery(*query);
    if(priceQueryModel->lastError().isValid())
        qDebug() << priceQueryModel->lastError();

    ui->tableView->setModel(priceQueryModel);
    priceQueryModel->removeColumn(0);
    priceQueryModel->setHeaderData(0, Qt::Horizontal, QObject::tr("Closing Date"));
    priceQueryModel->setHeaderData(1, Qt::Horizontal, QObject::tr("Share Price"));
    priceQueryModel->setHeaderData(2, Qt::Horizontal, QObject::tr("Total Value"));
    priceQueryModel->setHeaderData(3, Qt::Horizontal, QObject::tr("Company"));
}

To copy to clipboard, switch view to plain text mode 

and I'm not sure why. The parameterised query would, I'm guessing be much more efficient.

I also found that when I had the shareidx value as an integer and tried to add that to the query string in the first instance that it added a \ infront of the number each time for some reason. Why is that?

ie.. the string would become s.shareid where p.shareid = \7 for example instead of =7.

Thanks in anticipation. I'm slowly getting there.

Peter.