[SOLVED] QSqlRecord and auto-increment primary keys

[soroush]

Hello dear Qt guys

I have a QSqlTableModel and want to append a record to it. The primary key of database table is a not-null (not surprisingly!), auto-incrementing unsigned integer. I don't want to deal with the key, just want to let database to decide about value of PK when I append a new row.

I'm getting error when trying to model->submitAll after adding a QSqlRecord to model. Error string says that the PK should not be null. So I have to do something like following code, which think is not so good.
@
QSqlRecord newRecord = model->record();
// This is very bad:
QSqlQuery query("SELECT myKey FROM myTable ORDER BY myKey DESC LIMIT 1",Database::instance);
query.first();
int key = query.value(0).toInt()+(currentCount++);
newRecord.setValue(0,key+1);
// fill newRecord, add it to medel, ...
@

[ObiWan]

Normally, you generate the next auto-inc number for the column by inserting either NULL or 0 into it.
Try
@newRecord.setValue(0,0);@

[soroush]

Above code, first time inserts a record with PK=0. next time gives error: PK is duplicated & cannot insert a record with duplicated PK value...

[ChrisW67]

You either specify a valid value for the PK column or you do not attempt to insert a value into that column and have the database generate it for you. By default inserting a new record en masse will try to put a value in every column and fail if the value in the PK column is NULL. You may be able to avoid setting a value on the PK column by calling QSqlRecord::setGenerated(index, true) for the index of the PK column (probably 0).

[bu7ch3r]

Your ideea is good. I was doing the same thing for an old project. The problem is that you take the record before setting the querry to the model. That record is also an intem from your model. I usually insert a record on the -1 position(at the end of the database) then modify the primary key with my count value. Something like this:

[code]
QSqlRecord record;
record.setValue("primary_key", nNextKeyValue);
//more initialization of the record.
model->InsertRecord(-1,record);
[/code]

Another option is :

[code]
while(model->canFetchMore())
model->fetchMore();

int count = model->rowCount();
model->insertRow(count);
QSqlRecord record = model->record(count);
record.setValue("id", nMaxId);
[/code]

dont forget to save the model after changes

[soroush]

I find the answer. No need to query last PK in table (in that case, DBMS should process almost half records in table by index). Just remove the PK from record. Not set it to NULL or '0' or set generated flag. Just remove the field from record:
@
QSqlRecord newRecord = model->record();
newRecord.remove(0);
@

:)