I totally agree with you. I don't expect there to be a rowCount of 1 afterwards. In fact the ODBC result seems correct to me. I was a shocked to see the MySQL method work as it does.

But here is the problem: When inserting a new row into a model and then submitting it how do you retrieve that same row again?

Example (using the same table as previously posted):
Qt Code: 
  1. QSqlRecord rec = model.record(); //create blank record
  2. rec.setValue(1, "some name"); //set only the name, leaving the auto id field null
  3. model.insertRecord(-1, rec); //append the record to the end of the model
  4. mapper.toLast(); //set the mapper to the last row so we are seeing this new record in our widgets
  5. //** let the user edit data on the widgets here
  6. model.submitAll(); //save our cached model to the database
  7. mapper.setCurrentIndex(  ????? );
To copy to clipboard, switch view to plain text mode 

Here are some ideas I have come up with but they pose their own problems:

Solution #1
Do not filter the model so that select returns all rows, then assume the last row is our newly inserted row.
Qt Code: 
  1. model.setFilter(QString()); //no filter
  2. model.select();
  3. QSqlRecord rec = model.record(); //create blank record
  4. rec.setValue(1, "some name"); //set only the name, leaving the auto id field null
  5. model.insertRecord(-1, rec); //append the record to the end of the model
  6. mapper.toLast(); //set the mapper to the last row so we are seeing this new record in our widgets
  7. //** let the user edit data on the widgets here
  8. model.submitAll(); //save our cached model to the database
  9. mapper.setCurrentIndex(model.rowCount() - 1);
To copy to clipboard, switch view to plain text mode 

Possible Problems:
  • What if other users insert or delete rows in the table while the user is editing data. When the submitAll() get's called it will reselect the table and our row will be in a different location.
  • When selecting a table with no filter and the table has many many rows we are wasting memory and slowing things down. (Am I wrong with this assumption?)
  • What if we insert more than just one new row...?


Solution #2
Save the values of the current row (except for the id) just before we submit. Then right after the submit change the model's filter to select the record that matches all those values. Then call select and we should have only one row returned.

Possible Problems:
  • If two or more records exist that have identical data but different id's then our filter won't know which one was the new one. I guess we could sort on id and take the highest one but that's assuming the database always increments the id.


Solution #3
Don't use the database auto id's and use UUID's.

Possible Problems:
  • The primary key for each table will be a string instead of a BIGINT. Is this a performance problem?


Solution #4
Subclass QSqlTableModel and re-implement QSqlTableModel::insertRowsIntoTable(const QSqlRecord &values) so that you can retrieve the last inserted id. With MySQL and SQLITE you can use QSqlQuery::lastInsertId() and with ODBC (or at least MSSQL+ODBC) you can run the query "SELECT id = @@Identity"

Possible Problems:
  • Still have the same problem as solution #1 in that you have to select all rows.



I realize now that this has boiled down to the same problem that I posted in this thread. (Sorry for duplicating posts! )
In regards to that thread, yes wysota passing NULL as the auto-increment value is the correct way of doing it in both MySQL and ODBC, thanks.
What trueneo suggested doesn't work for 2 reasons. First the QODBC driver does not support lastInsertId and secondly QSqlDatabase does not have a lastInsertId method.

I'm leaning towards using UUID's instead of database-generated id's just to make this work properly. I look forward to hearing what your opinions are on any of the above solutions or if you have different ways of doing things let me know! Thanks for reading such a long post heh.