[SOLVED] [QtSql] Create and configure a QTableView with Qt Creator

valandil211

Hello again Qt Devs!

I am trying to do what I think should be a simple thing.

I have created a simple GUI with Qt Creator (hence Qt Designer) and dragged a QTableView as the central widget of my MainWindow. My mainwindow.h looks like this:

@#ifndef MAINWINDOW_H
#define MAINWINDOW_H

#include <QMainWindow>
#include <QSqlQueryModel>
#include <QTableView>

namespace Ui {
class MainWindow;
}

class MainWindow : public QMainWindow
{
Q_OBJECT

public:
explicit MainWindow(QWidget *parent = 0);
~MainWindow();

private:
Ui::MainWindow *ui;
QSqlQueryModel *model;

};

#endif // MAINWINDOW_H
@

In the constructor of my MainWindow, I have prepared a QSqlQueryModel to populate my QTableView:

@#include "mainwindow.h"
#include "ui_mainwindow.h"

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

QSqlQueryModel *model = new QSqlQueryModel;
model->setQuery("SELECT * FROM ""v_EmpAff""");
model->setHeaderData(0, Qt::Horizontal, tr("Nom"));
model->setHeaderData(1, Qt::Horizontal, tr("Prénom"));
model->setHeaderData(2, Qt::Horizontal, tr("Abbr."));
model->setHeaderData(3, Qt::Horizontal, tr("Affiliation"));

}

MainWindow::~MainWindow()
{
QSqlDatabase db = QSqlDatabase::database();
db.close();

delete ui;

}
@

I think this is correct (I have tried many variations of this). Now, the question is, since my QTableView is initialized in ui_mainwindow.h, where and how do I put set its model? Am I missing something?

Thanks for your time, everyeone!

DenisKormalev

add to your constructor next line (assuming your QTableView is named tableView)
@
ui->tableView->setModel(model);
@

jim_kaiser

@
ui->tableview->setModel(model)
@

??

Also, i don't think your table view is the centralWidget, if you dragged it onto the main window. It will be a child of the centralWidget. But that shouldn't change the above. You just access by ui-><object name here>

[Edit: Denis has made my post redundant :P, but the additional info is probably useful to clear some misunderstanding you might have about the centralWidget. ]

HuXiKa

Try adding this after line 15:

@ui->tableView->setModel(model); // "tableView" is the name of the table@

valandil211

Thanks for your amazingly fast response, but this is not working. The QTableView still is empty.

No error message appears, so I am assuming that my db connection is fine, but I could be wrong. Here's my connection.h:

@#ifndef CONNECTION_H
#define CONNECTION_H

#include <QMessageBox>
#include <QtSql>

/* Création d'une connection à la BD
"qaimagerie", source des données
pour l'interface. */

static bool createConnection()
{
QSqlDatabase db = QSqlDatabase::addDatabase("QPSQL"); // Définition du driver

// Set db name, username and password to access DB
db.setDatabaseName("******");
db.setUserName("******");
db.setPassword("******");

// Displays error if database connection fails
if(!db.open())
{
    QMessageBox::critical(0, QObject::tr("Erreur de connection à la base de données"),
                          db.lastError().text());
    return false;
}
return true;

}

#endif // CONNECTION_H
@

Moreover, the table v_EmpAff is a view. Would that change the code in any way? I guess I could also use a QTableModel, no?

Thanks again,

jim_kaiser

Use a QStandardItemModel (used for all views (table, tree, list etc..)) and set it as model on the view. It could be your sql doesn't return values? You could test the table view by creating a dummy QStandardItemModel and setting it.

HuXiKa

This should fix it:

@model->setQuery(sql,DB); // DB is your QSqlDatabase object@

valandil211

[quote author="HuXiKa" date="1307969159"]This should fix it:

@model->setQuery(sql,DB); // DB is your QSqlDatabase object@[/quote]

I'm not sure where you want me to put this. As I understand it, the db variable I used in my header connection.h is a local variable, and can't be used in another function.

So what do I write in db? I tried including connection.h in mainwindow.cpp, but it didn't help.

Thanks!

jim_kaiser

When you create database, if you provide a name, that could be passed in this call to get the db.

@
QSqlDatabase db = QSqlDatabase::database("QPSQL"); // like in your destructor
@

But, when you call setQuery without the db argument, it uses
@
void QSqlQueryModel::setQuery ( const QString & query, const QSqlDatabase & db = QSqlDatabase() )
@

the default QSqlDatabase constructor creates an invalid database.

From Documentation

bq. QSqlDatabase::QSqlDatabase ()
Creates an empty, invalid QSqlDatabase object. Use addDatabase(), removeDatabase(), and database() to get valid QSqlDatabase objects.

valandil211

Ok. So I edited the line setQuery to become:

@model->setQuery("SELECT * FROM v_EmpAff", QSqlDatabase::database());@

However, my tableView still is empty.

jim_kaiser

Hehe... you are still missing something, try giving the db name too.

If you create database using @QSqlDatabase::addDatabase("MyDB");@

access it using @QSqlDatabase::database("MyDB");@

If it still doesn't work, could be some other problem in querying the data. The table view itself will work correctly.

[Edit: "SELECT * FROM ""v_EmpAff""" -- Too many quotes?? If you need quotes in the string.. use " (\ being the escape character)..
should be setQuery("SELECT * FROM v_EmpAff") or setQuery("SELECT * FROM "v_EmpAff"").. don't need the quotes there in SQL as far as i know..]

valandil211

Still doesn't work. I'm reposting my most recent files.

This is my connection.h

@#ifndef CONNECTION_H
#define CONNECTION_H

#include <QMessageBox>
#include <QtSql>

/* Création d'une connection à la BD
"qaimagerie", source des données
pour l'interface. */

static bool createConnection()
{
QSqlDatabase db = QSqlDatabase::addDatabase("QPSQL", "qaimagerie"); // Définition du driver

// Set db name, username and password to access DB
db.setDatabaseName("qaimagerie");
db.setUserName("*****");
db.setPassword("*****");

// Displays error if database connection fails
if(!db.open())
{
    QMessageBox::critical(0, QObject::tr("Erreur de connection à la base de données"),
                          db.lastError().text());
    return false;
}
return true;

}

#endif // CONNECTION_H
@

And my mainwindow.cpp:

@#include "mainwindow.h"
#include "ui_mainwindow.h"

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

QSqlQueryModel *model = new QSqlQueryModel;
model->setQuery("SELECT * FROM ""v_EmpAff""", QSqlDatabase::database("qaimagerie"));
model->setHeaderData(0, Qt::Horizontal, tr("Nom"));
model->setHeaderData(1, Qt::Horizontal, tr("Prénom"));
model->setHeaderData(2, Qt::Horizontal, tr("Abbr."));
model->setHeaderData(3, Qt::Horizontal, tr("Affiliation"));

ui->tableView->setModel(model);

}

MainWindow::~MainWindow()
{
QSqlDatabase db = QSqlDatabase::database();
db.close();

delete ui;

}
@

As I understand it, when I add the DB with

@QSqlDatabase::addDatabase("QPSQL", "qaimagerie");@

I'm identifying the driver "QPSQL" and defining a connection name, right?

The SQL code queries an existing view in my DB, I have checked. What could be wrong?

valandil211

[quote author="jim_kaiser" date="1307971155"]Hehe... you are still missing something, try giving the db name too.

If you create database using @QSqlDatabase::addDatabase("MyDB");@

access it using @QSqlDatabase::database("MyDB");@

If it still doesn't work, could be some other problem in querying the data. The table view itself will work correctly.

[Edit: "SELECT * FROM ""v_EmpAff""" -- Too many quotes?? If you need quotes in the string.. use " (\ being the escape character)..
should be setQuery("SELECT * FROM v_EmpAff") or setQuery("SELECT * FROM "v_EmpAff"").. don't need the quotes there in SQL as far as i know..][/quote]

YES! YOU RULE! I thought I needed double quotes, because when I queried the DB using pure SQL, it complained about my capitalization and I had to make use of double quotes.

THANKS! I've been at it for a couple of hours, only for stupid double quotes!

On the other hand, sorting does not seem to work, but my view already specifies an ordering scheme, so I guess that's why it doesn't work.

jim_kaiser

My bad, the first argument to addDatabase is the connection type and second is the name. You have that correct. You want to use PostgreSQL right? I have to test it myself to check. Can test the code with Mysql.

I still don't understand how this works

@
model->setQuery("SELECT * FROM ""v_EmpAff""", QSqlDatabase::database("qaimagerie"));
@

the first argument, is effectively "SELECT * FROM " "v_EmpAff" "" (3 strings)

Anyways, i will test your code and let you know whats wrong.

[Edit: Thanks, and yeah the quotes caught my eye. Not sure about the sorting and all though :P]

valandil211

No, it's okay. It's working now.

Thanks a lot for your time!

jim_kaiser

Thats great :) Glad to help. Could you edit the title and put [Solved] in it. That's the general flow around here.

Happy hacking!