PDA

View Full Version : QSqlQuery: Getting an Auto Increment value



Al_
22nd December 2009, 20:04
Hi

How can I get the row id of a record added by QSqlQuery::exec("INSERT ...") if it is automatically assigned by the database?

There is an old thread on a closely related topic (see http://www.qtcentre.org/forum/f-qt-programming-2/t-getting-an-auto-increment-value-from-qsqltablemodel-with-onmanualsubmit-12908.html), but the proposed solutions did not work for me:


lastInsertedId(): not supported by database or driver (Microsoft Access, QODBC)
bind NULL, i.e., QVariant(QVariant::Int) with QSql::ParamType InOut: QSqlQuery::exec fails with the message "you tried to assign the Null value to a variable that is not a Variant data type" (yes, I tried; how else can I bind a Null value if not as variant?)
bind NULL, i.e., QVariant(QVariant::Int) with QSql::ParamType Out: QSqlQuery::exec fails with the message "Optional feature not implemented"


My current workaround is very ugly: I insert the record with a modified field (I add 100 to an int field that is in reality maximally 10), then SELECT .... WHERE value > 100, store the row id, then UPDATE with the corrected value).

Any less cumbersome method is highly appreciated.

Al_

tangential
23rd December 2009, 02:42
You're right, odbc doesn't support retrieval of this value, but a bit of googling turned up that apparently access supports "select @@identity", which apparently gives the information you want.

Hope this helps.

Al_
23rd December 2009, 17:48
YES! Works perfectly, many thanks. I feel a bit ashamed that I did not find this statement myself; but just little, judged by the many threads to this topic without solution, @@identity seems to be little known.

tangential
23rd December 2009, 23:15
Yeah, I found it about 3 levels deep when I was looking to see if there was a way to retrieve the information via ODBC (so I could fix the qt driver). Glad to have been able to help :)