Subsequent insertion of records with QSqlTableRecord fail after first error

Question

Hi, 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?

Answer 1

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.

Answer 2

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();