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



  • 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!



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



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



  • Try adding this after line 15:

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



  • 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,



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



  • This should fix it:

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



  • [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!



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



  • Ok. So I edited the line setQuery to become:

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

    However, my tableView still is empty.



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



  • 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?



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



  • 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]



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

    Thanks a lot for your time!



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

    Happy hacking!


Log in to reply
 

Looks like your connection to Qt Forum was lost, please wait while we try to reconnect.