Subsequent insertion of records with QSqlTableRecord fail after first error

I have a problem with inserting data into SQLite database using QSqlTableModel. The table is created like this:

QSqlQuery createTblSMS("CREATE TABLE sms_tbl("
        "isRead BOOLEAN NOT NULL,"
        "readTime DATETIME,"
        "arrivalTime DATETIME NOT NULL,"
        "sender TEXT NOT NULL,"
        "receiver TEXT NOT NULL,"
        "smsContent TEXT,"
        "PRIMARY KEY(arrivalTime, sender, receiver));");

I am inserting the records like this:

smsModel->insertRecord(-1, sms);
QString error = smsModel->lastError().text();
smsModel-开发者_运维百科>submitAll();

smsModel is QSqlTableModel.

If i put for example a record wih this values (false, NULL, '2010-06-30 17:27:55', '075710383', 'ONE 142140', 'TOP 15 # 2') - the record is inserted. After that record if put for example a record wih this values (false, NULL, '2010-06-30 10:05:29', '075710383', 'ONE 142140', 'TOP 15 # 3') - also this record is inserted.

But if i try to reinsert the record (false, NULL, '2010-06-30 17:27:55', '075710383', 'ONE 142140', 'TOP 15 # 2') which is already in the database, the smsModel will give an error like this :"columns arrivalTime, sender, receiver are not unique Unable to fetch row" - which is expected. Any other subsequent insertions of unique records will fail and the model gives me the same error. Do you have any clue why is this happening?

I ran into this problem of two fold.

When you are in manual submit mode, QSqlTableModel does not clear its cache when a submitAll fails (which it should when you send it a record that violates your constraints).

To correct this, you need to call either call select() or revertAll to remove those pending changes.

The doc doesn't make this very obvious, but it is there, check it out: http://doc.qt.io/qt-4.8/qsqltablemodel.html#submitAll

After a while i didn't manage to find solution with QSqlTableModel, so I made a workaround with QSqlQuery. The code is this:

QSqlQuery query(QSqlDatabase::database(mConnectionName));
    query.prepare("INSERT INTO sms_tbl (isRead, readTime, arrivalTime,"
        "sender, receiver, smsContent) "
        "VALUES (:isRead, :readTime, :arrivalTime, "
        ":sender, :receiver, :smsContent)");
    query.bindValue(":isRead", sms.value("isRead").toBool());
    query.bindValue(":readTime", sms.value("readTime").toString());
    query.bindValue(":arrivalTime", sms.value("arrivalTime").toString());
    query.bindValue(":sender", sms.value("sender").toString());
    query.bindValue(":receiver", sms.value("receiver").toString());
    query.bindValue(":smsContent", sms.value("smsContent").toString());
    query.exec();

You can't add a record with same primary key again. You have a primary key which contains the columns arrivalTime, sender, receiver. So you can't a value with same values of this three values.

You can change your create statement and a auto increment sms_table_id of type int.