QTableView / QSqlTableModel / Navigation with TAB Problem

[paulj]

I have created a simple database to aid with my understanding of the use of QSqlTableModel and QTableView. I have set up the code to allow inserting of additional data either at the current row in the View, or at the end if there is no row currently selected. I have not changed the default TabNavigation for the view.

If I add a new record then complete the details in the view pressing <return> has the correct effect. If I <tab> past the end of the record, it starts messing with the view, and the newly entered record details. I have not changed the edit strategy from the default QSqlTableModel::OnRowChange.

So I am not entirely clear if this is an interaction issue between QSqlTableModel and QTableView, or whether I have missed something in my code (or inadvertently changed the behaviour). I would appreciate your comments, hints and tips!

Here are the files:

database.h:

#ifndef DATABASE_H
#define DATABASE_H
 
#include <QtGui>
#include <QtSql>
 
bool createConnection(void);
 
#endif // DATABASE_H

#ifndef DATABASE_H
#define DATABASE_H

#include <QtGui>
#include <QtSql>

bool createConnection(void);

#endif // DATABASE_H

To copy to clipboard, switch view to plain text mode 

database.cpp: - the routine to connect to the database:

#include "database.h"
 
 
bool createConnection(void)
{
    QSqlDatabase db = QSqlDatabase::addDatabase("QMYSQL");
 
    db.setHostName("localhost");
    db.setDatabaseName("QtTrials");
    db.setUserName("paul");
    db.setPassword("");
    if (!db.open()) {
        QMessageBox::critical(0, QObject::tr("Database Error"), db.lastError().text());
        return false;
    }
 
    return true;
}

#include "database.h"


bool createConnection(void)
{
    QSqlDatabase db = QSqlDatabase::addDatabase("QMYSQL");

    db.setHostName("localhost");
    db.setDatabaseName("QtTrials");
    db.setUserName("paul");
    db.setPassword("");
    if (!db.open()) {
        QMessageBox::critical(0, QObject::tr("Database Error"), db.lastError().text());
        return false;
    }

    return true;
}

To copy to clipboard, switch view to plain text mode 

main.cpp:

#include <QApplication>
#include "database.h"
#include <iostream>
#include "nametablewindow.h"
 
int main(int argc, char *argv[])
{
    QApplication a(argc, argv);
 
 
    // The database connection needs to be made before any of the models are set
    // up, otherwise you will get "Database not open" errors and no data
 
    if (!createConnection()) {
        std::cerr << "Unable to open database" << std::endl;
        return 1;
    }
 
    NameTableWindow namesWindow;
    namesWindow.show();
 
    return a.exec();
}

#include <QApplication>
#include "database.h"
#include <iostream>
#include "nametablewindow.h"

int main(int argc, char *argv[])
{
    QApplication a(argc, argv);


    // The database connection needs to be made before any of the models are set
    // up, otherwise you will get "Database not open" errors and no data

    if (!createConnection()) {
        std::cerr << "Unable to open database" << std::endl;
        return 1;
    }

    NameTableWindow namesWindow;
    namesWindow.show();
    
    return a.exec();
}

To copy to clipboard, switch view to plain text mode 

nametablewindow.h:

#ifndef NAMETABLEWINDOW_H
#define NAMETABLEWINDOW_H
 
#include <QtGui>
#include <QtSql>
 
enum {      // Field names
    names_id = 0,
    names_firstname = 1,
    names_surname = 2,
    names_address_id = 3,
    names_comment = 4
};
 
class NameTableWindow: public QWidget
{
    Q_OBJECT
public:
    NameTableWindow(QWidget *parent = 0);
 
signals:
 
public slots:
    void insert(void);
    void remove(void);
 
private:
    QSqlTableModel *namesModel;
 
    QTableView *namesView;
    QLabel *namesLabel;
    QDialogButtonBox *buttonBox;
};
 
#endif // NAMETABLEWINDOW_H

#ifndef NAMETABLEWINDOW_H
#define NAMETABLEWINDOW_H

#include <QtGui>
#include <QtSql>

enum {      // Field names
    names_id = 0,
    names_firstname = 1,
    names_surname = 2,
    names_address_id = 3,
    names_comment = 4
};

class NameTableWindow: public QWidget
{
    Q_OBJECT
public:
    NameTableWindow(QWidget *parent = 0);

signals:

public slots:
    void insert(void);
    void remove(void);

private:
    QSqlTableModel *namesModel;

    QTableView *namesView;
    QLabel *namesLabel;
    QDialogButtonBox *buttonBox;
};

#endif // NAMETABLEWINDOW_H

To copy to clipboard, switch view to plain text mode 

nametablewindow.cpp:

#include "nametablewindow.h"
 
NameTableWindow::NameTableWindow(QWidget *parent) :
    QWidget(parent)
{
    // Create model and load data query
    namesModel = new QSqlTableModel(this);
 
    namesModel->setTable("names");
 
    // Set up the headers
    namesModel->setHeaderData(names_id,Qt::Horizontal, tr("id"));
    namesModel->setHeaderData(names_firstname, Qt::Horizontal, tr("First Name"));
    namesModel->setHeaderData(names_surname,Qt::Horizontal, tr("Surname"));
    namesModel->setHeaderData(names_address_id, Qt::Horizontal, tr("address id"));
    namesModel->setHeaderData(names_comment, Qt::Horizontal, tr("Comment"));
 
    // Load the table data
    namesModel->select();
 
    // Create the view
    namesView = new QTableView(this);
    namesView->setModel(namesModel);
 
    namesModel->setEditStrategy(QSqlTableModel::OnRowChange);
 
    namesView->setEditTriggers(QAbstractItemView::AnyKeyPressed
                               | QAbstractItemView::DoubleClicked);
 
    namesView->setSelectionMode(QAbstractItemView::SingleSelection);
    namesView->setSelectionBehavior(QAbstractItemView::SelectRows);
    namesView->hideColumn(names_id);
    namesView->resizeColumnsToContents();
    namesView->horizontalHeader()->setStretchLastSection(true);
 
    // Set up Label...
    namesLabel = new QLabel(tr("Names in Table"));
    namesLabel->setBuddy(namesView);
 
    // ... and layout
    QVBoxLayout *layout = new QVBoxLayout;
    namesView->setMinimumWidth(600);
    namesView->setMinimumHeight(400);
    layout->addWidget(namesLabel);
    layout->addWidget(namesView);
 
 
    buttonBox = new QDialogButtonBox;
    QPushButton *addButton = buttonBox->addButton(tr("&Add"),QDialogButtonBox::ActionRole);
    QPushButton *rmButton = buttonBox->addButton(tr("&Delete"),QDialogButtonBox::ActionRole);
    buttonBox->addButton(QDialogButtonBox::Ok);
 
    layout->addWidget(buttonBox);
 
    connect(rmButton, SIGNAL(clicked()), this, SLOT(remove()));
 
    connect(addButton, SIGNAL(clicked()), this, SLOT(insert()));
    connect(buttonBox, SIGNAL(accepted()), this, SLOT(close()));
    setLayout(layout);
    setWindowTitle(tr("Names Table"));
 
    namesView->setCurrentIndex(namesModel->index(0,0));
}
 
void NameTableWindow::insert()
{
    int row = namesView->currentIndex().row();
 
    if (row < 0) // No row selected currently
        row = namesModel->rowCount(); // add row at bottom
 
    namesModel->insertRows(row,1);
    qDebug("rowCount(): %d", row);
 
    QModelIndex index = namesModel->index(row,0);
    namesView->setCurrentIndex(index);
    namesView->edit(index);
 
}
 
void NameTableWindow::remove()
{
    int row = namesView->currentIndex().row();
    namesModel->removeRow(row);
    namesModel->submitAll();
}

#include "nametablewindow.h"

NameTableWindow::NameTableWindow(QWidget *parent) :
    QWidget(parent)
{
    // Create model and load data query
    namesModel = new QSqlTableModel(this);

    namesModel->setTable("names");

    // Set up the headers
    namesModel->setHeaderData(names_id,Qt::Horizontal, tr("id"));
    namesModel->setHeaderData(names_firstname, Qt::Horizontal, tr("First Name"));
    namesModel->setHeaderData(names_surname,Qt::Horizontal, tr("Surname"));
    namesModel->setHeaderData(names_address_id, Qt::Horizontal, tr("address id"));
    namesModel->setHeaderData(names_comment, Qt::Horizontal, tr("Comment"));

    // Load the table data
    namesModel->select();

    // Create the view
    namesView = new QTableView(this);
    namesView->setModel(namesModel);
    
    namesModel->setEditStrategy(QSqlTableModel::OnRowChange);

    namesView->setEditTriggers(QAbstractItemView::AnyKeyPressed
                               | QAbstractItemView::DoubleClicked);

    namesView->setSelectionMode(QAbstractItemView::SingleSelection);
    namesView->setSelectionBehavior(QAbstractItemView::SelectRows);
    namesView->hideColumn(names_id);
    namesView->resizeColumnsToContents();
    namesView->horizontalHeader()->setStretchLastSection(true);

    // Set up Label...
    namesLabel = new QLabel(tr("Names in Table"));
    namesLabel->setBuddy(namesView);

    // ... and layout
    QVBoxLayout *layout = new QVBoxLayout;
    namesView->setMinimumWidth(600);
    namesView->setMinimumHeight(400);
    layout->addWidget(namesLabel);
    layout->addWidget(namesView);


    buttonBox = new QDialogButtonBox;
    QPushButton *addButton = buttonBox->addButton(tr("&Add"),QDialogButtonBox::ActionRole);
    QPushButton *rmButton = buttonBox->addButton(tr("&Delete"),QDialogButtonBox::ActionRole);
    buttonBox->addButton(QDialogButtonBox::Ok);

    layout->addWidget(buttonBox);

    connect(rmButton, SIGNAL(clicked()), this, SLOT(remove()));

    connect(addButton, SIGNAL(clicked()), this, SLOT(insert()));
    connect(buttonBox, SIGNAL(accepted()), this, SLOT(close()));
    setLayout(layout);
    setWindowTitle(tr("Names Table"));

    namesView->setCurrentIndex(namesModel->index(0,0));
}

void NameTableWindow::insert()
{
    int row = namesView->currentIndex().row();

    if (row < 0) // No row selected currently
        row = namesModel->rowCount(); // add row at bottom

    namesModel->insertRows(row,1);
    qDebug("rowCount(): %d", row);

    QModelIndex index = namesModel->index(row,0);
    namesView->setCurrentIndex(index);
    namesView->edit(index);

}

void NameTableWindow::remove()
{
    int row = namesView->currentIndex().row();
    namesModel->removeRow(row);
    namesModel->submitAll();
}

To copy to clipboard, switch view to plain text mode 

For completion, here is the code to setup the MySQL database:
DatabaseTrials.sql (obviously NOT Qt Code!):

drop DATABASE QtTrials;
 
create DATABASE QtTrials;
 
use QtTrials;
 
\! echo "Creating tables..."
 
\! echo "...names table"
create table names (
       id          int not null primary key AUTO_INCREMENT,
       firstname   char(255),
       surname     char(255),
       address_id  int,
       comment     text
) engine = innodb;
 
\! echo "...addresses"
create table addresses (
       id          int not null primary key AUTO_INCREMENT,
       addr1       char(255),
       addr2       char(255),
       town        char(255),
       postcode    char(8)
) engine = innodb;
 
insert into addresses (addr1, addr2, town, postcode) VALUES
       ("20 Park avenue", "Buckley", "Mold", "CH1 1CH"),
       ("Deeside Industrial Park", "", "", "");
 
insert into names (firstname, surname, address_id, comment) VALUES
       ("Paul", "Jewell", 
         (SELECT addresses.id from addresses where addresses.postcode = "CH1 1CH"), "");

drop DATABASE QtTrials;

create DATABASE QtTrials;

use QtTrials;

\! echo "Creating tables..."

\! echo "...names table"
create table names (
       id          int not null primary key AUTO_INCREMENT,
       firstname   char(255),
       surname     char(255),
       address_id  int,
       comment     text
) engine = innodb;

\! echo "...addresses"
create table addresses (
       id          int not null primary key AUTO_INCREMENT,
       addr1       char(255),
       addr2       char(255),
       town        char(255),
       postcode    char(8)
) engine = innodb;

insert into addresses (addr1, addr2, town, postcode) VALUES
       ("20 Park avenue", "Buckley", "Mold", "CH1 1CH"),
       ("Deeside Industrial Park", "", "", "");

insert into names (firstname, surname, address_id, comment) VALUES
       ("Paul", "Jewell", 
         (SELECT addresses.id from addresses where addresses.postcode = "CH1 1CH"), "");

To copy to clipboard, switch view to plain text mode