CODEWITHSUNDEEP
c++qtsqliteqtableview
Thomas F.
Thomas F.

Reputation: 11

Update the database with cell edit using QT and SQLite

I'm creating an application with SQLite database access that displays database search in QTableView. Search is looking up all columns. Now I would like to be able to edit cells and save to the database. I seem to be able to edit the cells but data is not saved. 

void MainWindow::on_search_box_textChanged(const QString &arg1)
{
QSqlQueryModel *GetTable = new QSqlTableModel(ui->tableView);

if(!arg1.isEmpty())
{
    GetTable->setQuery(QString("SELECT * FROM Cour WHERE (Date LIKE '""%"+arg1+"%""' OR Name LIKE '""%"+arg1+"%""' OR bill LIKE '""%"+arg1+"%""' OR [B O L] LIKE '""%"+arg1+"%""' OR SO LIKE '""%"+arg1+"%""' OR ETA LIKE '""%"+arg1+"%""' OR Description LIKE '""%"+arg1+"%""' );"));
    ui->tableView->setModel(GetTable);
    ui->tableView->setAlternatingRowColors(GetTable);
    ui->tableView->horizontalHeader()->setSectionResizeMode(QHeaderView::Stretch);

}
if(arg1.isEmpty())
{
    GetTable->clear();
    ui->tableView->clearFocus();
    ui->tableView->setModel(GetTable);
}
}

This is the code I use to display the data in QTableView. I have tried changing between QSqlTableModel and QSqlQueryModel. Based on docs and hours of googling it appears as if the QTableView should update the database accordingly when the cell is edited. I must be missing something.

At first, I thought it might be caused by database without a primary key but the situation is the same in both cases.

Upvotes: 0

Views: 1626

Answers (1)

Thomas F.
Thomas F.

Reputation: 11

I found the solution to my problem. The thing I was missing was the setFilter() with a properly crafted query.

In QSqlTableModel as oppose to QSqlQueryModel's setQuery() we should use setFilter() which is a WHERE part of SQL query and setTable() which is "Select * FROM tablename". Now to make table editable via QTableView all we have to do is use setEditStrategy(). There is couple of options to chose from but the one I needed was setEditStrategy(QSqlTableModel::OnFieldChange).

void MainWindow::on_search_box_textChanged(const QString &arg1)
{
QSqlTableModel *GetTable = new QSqlTableModel(ui->tableView);

if(!arg1.isEmpty()){
    GetTable->setTable("DHL");
    GetTable->setEditStrategy(QSqlTableModel::OnFieldChange);
    GetTable->setFilter(QString("Date LIKE '""%"+arg1+"%""' OR Name LIKE '""%"+arg1+"%""' OR bill LIKE '""%"+arg1+"%""' OR [B O L] LIKE '""%"+arg1+"%""' OR SO LIKE '""%"+arg1+"%""' OR ETA LIKE '""%"+arg1+"%""' OR Description LIKE '""%"+arg1+"%""';"));
    GetTable->select();

    QTableView *tableview = new QTableView();
    ui->tableView->setModel(GetTable);
    ui->tableView->hideColumn(7); // don't show the ID
    ui->tableView->setAlternatingRowColors(GetTable);
    ui->tableView->horizontalHeader()->setSectionResizeMode(QHeaderView::Stretch);
    ui->tableView->show();
}
else
{
    GetTable->clear();
    ui->tableView->clearFocus();
    ui->tableView->setModel(GetTable);
}

I hope someone will find it useful.

Upvotes: 1

Related Questions