How to add new rows in a database?

Question

I am creating a simple app for modifying SQLite databases.

I am trying to add new rows in MainWindow::on_AddButton_clicked() method, but when I use the MainWindow::on_reselectTable_clicked() method where I reselect SqlTableView, all new rows are removed.

MainWindow.h:

#ifndef MAINWINDOW_H
#define MAINWINDOW_H

#include <QMainWindow>

#include <QtSql/QSqlDatabase>
#include <QtSql/QSqlError>
#include <QtSql/QSqlTableModel>
#include <QDebug>
#include <QtSql>
QT_BEGIN_NAMESPACE
namespace Ui { class MainWindow; }
QT_END_NAMESPACE

class MainWindow : public QMainWindow
{
    Q_OBJECT

public:
    MainWindow(QWidget *parent = nullptr);
    ~MainWindow();

private slots:
    void on_AddButton_clicked();

    void on_tableView_clicked(const QModelIndex &index);

    void on_DeleteButton_clicked();

    void on_comboBox_currentIndexChanged(int index);

    void on_submitButton_clicked();

    void on_revertButton_clicked();

    void on_reselectTable_clicked();

private:
    Ui::MainWindow *ui;

    QSqlDatabase db;
    QSqlTableModel* model;
    int currentRow;
};
#endif // MAINWINDOW_H

MainWindow.cpp:

#include "mainwindow.h"
#include "./ui_mainwindow.h"

MainWindow::MainWindow(QWidget *parent)
    : QMainWindow(parent)
    , ui(new Ui::MainWindow)
{
    ui->setupUi(this);
    db = QSqlDatabase::addDatabase("QSQLITE");
    db.setHostName("127.0.0.1");
    //db = QSqlDatabase::addDatabase("QMYSQL");
    db.setDatabaseName("../db/cities.db3");

    if (db.open())
    {
        ui->statusbar->showMessage("Successful database connection: " + db.databaseName());
        model = new QSqlTableModel(this, db);
        model->setTable("cities");
        model->setEditStrategy(QSqlTableModel::OnManualSubmit);

        model->select();

        ui->tableView->setModel(model);
        //rui->tableView->setColumnHidden(0, true);
        ui->tableView->setColumnHidden(1, true);
        ui->tableView->setColumnHidden(2, true);

        ui->tableView->setSelectionBehavior(QAbstractItemView::SelectRows);
        ui->tableView->setSortingEnabled(true);
    }
    else
    {
        ui->statusbar->showMessage("Connection error: " + db.lastError().databaseText());
    }
}

MainWindow::~MainWindow()
{
    delete ui;
}

void MainWindow::on_tableView_clicked(const QModelIndex &index)
{
    currentRow = index.row();
}

void MainWindow::on_comboBox_currentIndexChanged(int index)
{
    qDebug() << index;
    switch (index)
    {
    case 0:
        model->setFilter("population > 0");
        break;
    case 1:
        model->setFilter("population > 1000");
        break;
    case 2:
        model->setFilter("population > 10000");
        break;
    case 3:
        model->setFilter("population > 25000");
        break;
    case 4:
        model->setFilter("population > 50000");
        break;
    case 5:
        model->setFilter("population > 100000");
        break;
    case 6:
        model->setFilter("population > 250000");
        break;
    case 7:
        model->setFilter("population > 500000");
        break;
    case 8:
        model->setFilter("population > 1000000");
        break;
    case 9:
        model->setFilter("population > 2000000");
        break;
    case 10:
        model->setFilter("population > 20000000");
    default: // nop
        break;
    }
    model->select();
}

void MainWindow::on_AddButton_clicked()
{
    qDebug() << "inserting row:" << model->insertRow(model->rowCount());
}

void MainWindow::on_DeleteButton_clicked()
{
    qDebug() << "deleting row:" << model->removeRow(currentRow);
    model->select();
}

void MainWindow::on_submitButton_clicked()
{
    model->submitAll();
}

void MainWindow::on_revertButton_clicked()
{
    model->revertAll();
}

void MainWindow::on_reselectTable_clicked()
{
    model->select();
}

CMakeList.txt

cmake_minimum_required(VERSION 3.5)

project(cities VERSION 0.1 LANGUAGES CXX)

set(CMAKE_INCLUDE_CURRENT_DIR ON)

set(CMAKE_AUTOUIC ON)
set(CMAKE_AUTOMOC ON)
set(CMAKE_AUTORCC ON)

set(CMAKE_CXX_STANDARD 14)
set(CMAKE_CXX_STANDARD_REQUIRED ON)

find_package(QT NAMES Qt5 COMPONENTS Widgets Sql REQUIRED)
find_package(Qt${QT_VERSION_MAJOR} COMPONENTS Widgets Sql REQUIRED)


set(PROJECT_SOURCES
        main.cpp
        mainwindow.cpp
        mainwindow.h
        mainwindow.ui
)
add_executable(cities ${PROJECT_SOURCES})

if(SQLite3_FOUND)
    include_directories(${SQLITE_INCLUDE_DIRS})
    target_link_libraries(cities, ${SQLITE_LIBRARIES})
endif(SQLite3_FOUND)

target_link_libraries(cities PRIVATE Qt${QT_VERSION_MAJOR}::Widgets Qt${QT_VERSION_MAJOR}::Sql)

Answer 1

From QSqlTableModel::select:

Note: Calling select() will revert any unsubmitted changes and remove any inserted columns.

So you need to submit changes before trying to call select(), but if you do so without filling the newly added rows, it still wouldn't work.

To check why, you could use:

qDebug()<<model->submitAll();
qDebug()<<model->lastError();

This would output:

false
QSqlError("", "No Fields to update", "")

---

Example:

#include <QApplication>
#include <QtWidgets>
#include <QtSql>

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

    QWidget w;
    QVBoxLayout l(&w);

    QSqlDatabase db;
    QSqlTableModel* model;
    QTableView tableView;
    QPushButton add("add");
    QPushButton submit("submit");
    QPushButton reselect("reselect");

    l.addWidget(&add);
    l.addWidget(&submit);
    l.addWidget(&reselect);
    l.addWidget(&tableView);

    db = QSqlDatabase::addDatabase("QSQLITE");
    db.setDatabaseName("cities.db3");

    if(db.open())
    {
        model = new QSqlTableModel(&w, db);
        model->setTable("cities");
        model->setEditStrategy(QSqlTableModel::OnManualSubmit);

        model->select();

        tableView.setModel(model);
    }
    else
    {
        qDebug()<<"Could not open database file";

        return 0;
    }

    QObject::connect(&add,&QPushButton::clicked,[=]()
    {
        qDebug() << "inserting row:" << model->insertRow(model->rowCount());
    });

    QObject::connect(&submit,&QPushButton::clicked,[=]()
    {
        qDebug()<<model->submitAll();
        qDebug()<<model->lastError();
    });

    QObject::connect(&reselect,&QPushButton::clicked,[=]()
    {
        model->select();
    });

    w.show();

    return a.exec();
}