Updating/Inserting into table using QSqlTableModel

Abdurahman_Gulamkadirov

Hi there. I'm not familiar with model/view, so I really need your help!
I'm working with MySql, there's a table called goods. It's fields are name, barcode, amount, cost. In UI, there is a tableView. User can input data(good's name, barcode, amount, cost) to the table via modal window. The data user given should be inserted to the database if there's no good with the same barcode, else it's amount field should be updated to amount+user_input.amount

I've written simple model that inherits QSqlTableModel, and implemented it's submitAll() method:

bool SqlTableModelEx::submitAll()
{
    QSqlQuery query(this->database());

    for(int row = 0; row < this->rowCount(); ++row)
    {
        QSqlRecord rec = this->record(row);
        //0 - id, 1 - name, 2 - barcode, 3 - amount, 4 - cost
        query.exec(tr("SELECT * FROM test WHERE barcode=%1").arg(rec.value(2).toString()));
        if(query.size())//if there's a product with the same barcode in the database
        {
            query.next();
            rec.value(3).setValue(rec.value(3).toInt() + query.value(3).toInt());
            rec.setGenerated(3, true);
            this->updateRowInTable(row, rec);
        }
    }
    return this->database().commit();
}

But no updating. I tested:

qInfo() << this->updateRowInTable(row, rec);

it showed false. Last error:

" No Fields to update"

Abdurahman_Gulamkadirov

should I change "dirty" of QModelIndex? If yes, how?

JonB

@Abdurahman_Gulamkadirov
I don't know what you're trying to do/why do it this way. I don't know why you are executing SELECT statements in the middle of submitting.

The usual way of using a QSqlTableModel is:

Allow it to fill by select()ing all the rows in the table. the rows are all in memory now.
Allow the user to insert or update via your UI, or whatever means. You know whether a given row exists by looking at what you have fetched, so you know whether to issue insertRows()/insertRecord() or just setData()/setRecord() on an existing row.
Call submitAll() when you are ready to commit to the database.

You should not need to subclass. You should not need to play with updateRowInTable() or look at "dirtiness" etc. unless you have some need and know what you are doing.

Abdurahman_Gulamkadirov

@JonB I should show just a table to user when importing products to store. So there should be product list that's just imported, not the list of whole products in the store. When the user presses "Import to store" button, If a product in the importing list is not in database, it should be inserted, otherwise it's amount should be updated to it's_amount_in_the_store + it's_amount_in_the_importing_list. I just tried to select product by it's barcode from database, using SELECT statement. Then I know if the product exists in db

Sorry for my english, I couldn't describe my question clearly