Insert new rows into database using QSqlTableModel

ras123

I am using QSqlTableModel with manual submit. Consider a situation where 4 new rows are inserted into this model using inserRows, and user add data into the 2 rows of newly created. Then on manual submit, it returns with an error "No fields to update". The following code,
@ model->database().transaction();
if(model->submitAll())
{
model->database().commit();
}
else
{
model->database().rollback();
QMessageBox::warning(this, "Database Write Error",
tr("The database reported an error: %1")
.arg(model->lastError().text()));
}@

So when submitAll() fails nothing updated in database. How to change the code to "update/insert the valid data in to the database, and other remains in cache" instead of nothing inserted into the database.
Thanking You,
Ras

franku

Could you please add some sourcecode to explain how you use insertRows?

ras123

Here is the code used for inserting rows
@ int row = model->rowCount();
model->insertRows(row, 4);@

Or
@ int row = model->rowCount();
model->insertRow(row);@

called 4 times

franku

Have a look at this as it is working.

@#include <QCoreApplication>
#include <QSqlDatabase>
#include <QSqlTableModel>
#include <QSqlQuery>
#include <QSqlError>
#include <QDebug>
#include <QModelIndex>

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

QSqlDatabase db = QSqlDatabase::addDatabase("QSQLITE");
db.setDatabaseName("rast123.sqlite");

if(!db.open()) {
    qDebug() << db.lastError().text();
    return 0;
}

QSqlQuery q(db);
if(!q.exec&#40;"create table if not exists \"rast123\" (id integer&#41;")) {
    qDebug() << "Create table" << q.lastError().text();
    return 0;
}

QSqlTableModel model(0, db);
model.setEditStrategy(QSqlTableModel::OnManualSubmit);
model.setTable("rast123");
model.select();

model.database().transaction();

int rowCount = model.rowCount();
qDebug() << rowCount;
if(!model.insertRows(rowCount, 4)) {
    qDebug() << "insertRows" << model.lastError().text();
    return 0;
}

model.setData(model.index(rowCount + 0,0), rowCount +0);
model.setData(model.index(rowCount + 1,0), rowCount +1);
model.setData(model.index(rowCount + 2,0), rowCount +2);
model.setData(model.index(rowCount + 3,0), rowCount +3);

rowCount = model.rowCount();
if(!model.insertRow(rowCount)) {
    qDebug() << "insertRow" << model.lastError().text();
    return 0;
}
model.setData(model.index(rowCount,0), rowCount);

if(model.submitAll()) {
    model.database().commit();
} else {
    model.database().rollback();
            qDebug() << "Database Write Error" <<
                 "The database reported an error: " <<
                   model.lastError().text();

}

return a.exec&#40;&#41;;

}
@

ras123

Hi,
The problem is only when user didn't fill the rows completely, i.e. as an example, try after removing two lines in

@ model.setData(model.index(rowCount + 0,0), rowCount +0);
model.setData(model.index(rowCount + 1,0), rowCount +1);
model.setData(model.index(rowCount + 2,0), rowCount +2);
model.setData(model.index(rowCount + 3,0), rowCount +3);@

Thanks for your help.

franku

have fun :-)

ras123

Please note that it may not work with the situaltion
@
model.setData(model.index(rowCount + 0,0), rowCount +0);
model.setData(model.index(rowCount + 1,0), rowCount +1);
@
i.e. user left two rows, any solution?

franku

I tried out your example, leaving the two rows, this results in the mentioned error. So you have to find out how many rows the user wants to add prior to call insertRows.

Wilk

Hello.
I have another question: isn't it easier to insert rows after getting data from user? I mean user inserts data into some intermediate table and then you insert it into your DB. So you can create some class based on QAbstractTableModel, implement adding information into it, checking input, etc. Then you may create a special dialog with this model in QTableView and a spin box for changing number of rows to insert. After user finished input, you check new data (if you need it) and then you build a query to insert data into your DB. You may process rows one by one, or may be at the same time using threads (I realy don't think it's a good idea), or even construct a big query, whitch adds all rows at once .
Or let user to add rows one by one - it seems less comfortable, but it's easier to implement.
And another one question: does your DB have any not null fields?

franku

Wilk, this is what I mentioned above. But it depends on the application someone wants to deploy. However, I don't think that it makes sense using multithreaded insertion into a database in general. Since nearly everything is possible in coder's world ('beam me up' not yet) it should be clear what the user wants to enter and how - not inverse - looking at what Qt does and then decide what the user has to do. Besides, the example DB does only have one integer key.