Can't retrieve identity field after insert in sql database

Question

Using C++ Qt framework and sql server 2008 I've been trying to insert a record into a table with an identity field and retrieve the identity value. Below is a simplified code sample which actually does do the insert but just doesn't retreive the identity. The identity returned from query.value(0) is an invalid QVariant.

QSqlQuery query(*pConn);
query.prepare("insert into [VH_MANUFACTURER]  values ('sRRS test man code','RRS type','RRS logo file',1,'RRS SEO para','RRS description','RS');"
"select SCOPE_IDENTITY();");
if(query.exec())
{
    if(query.next())
    {
        QVariant identity =   query.value(0);
        int id=identity.toInt();
    }
}

I've tried using select @@identity instead of scope_identity with no improvement and also QSqlQuery .lastInsertId() which also returns an invalid QVariant, see below.

  bool bFeature = pConn->driver()->hasFeature(QSqlDriver::LastInsertId);

QSqlQuery query(*pConn);
query.prepare("insert into [VH_MANUFACTURER] ([MFG_NAME],[MFG_TYPE],[MFG_LOGO],[MFG_ACTIVE],[MFG_SEO_CONTENT],[MFG_DESCRI],[MFG_CAPMANCODE]) values ('sRRS test man code','RRS type','RRS logo file',1,'RRS SEO para','RRS description','RS')");
if(query.exec())
{
    QVariant id=query.lastInsertId();
}

hasFeature returns true, so the driver is supposed to support what I'm trying to do. Just to test the sql , I ran the sql directly through Sql Server Management Studio and it inserts as expected and returns the identity value correctly.

Does your `QSqlQuery` support multiple statements in one string??? — Lukasz Szozda, Sep 03 '15 at 17:04
I can't say for sure but the insert works correctly and query.next() returns true when select scope_identity is used and returns false when it's not used. — Rich, Sep 03 '15 at 17:06
Try `query.prepare("insert into [VH_MANUFACTURER] values ('sRRS test man code','RRS type','RRS logo file',1,'RRS SEO para','RRS description','RS'); select SCOPE_IDENTITY();");` — Lukasz Szozda, Sep 03 '15 at 17:06
Identical? I remove `""` between strings. Will you try or not? — Lukasz Szozda, Sep 03 '15 at 17:11
c++ wise that's identical. I tried it all the same but same result. — Rich, Sep 03 '15 at 17:14
See also [this](http://stackoverflow.com/a/11618200/5070879), in Python the use while, maybe you could try to execute `query.next()` one more time. — Lukasz Szozda, Sep 03 '15 at 17:15
I used that question when I tried lastInsertId and got the same result , an invalid QVariant. — Rich, Sep 03 '15 at 17:19
tried while statement but no joy, only one item in collection it seems. — Rich, Sep 03 '15 at 17:21
Last suggestion, specify columns explicitly`insert into [VH_MANUFACTURER] (col_1, col2, col3, ...) values ` I don't like you depend of column order which is undefined, maybe in SSMS it works, but Qt may not map correctly. Check also `QSqlDriver::hasFeature().` `QSqlDriver::LastInsertId 7 Whether the driver supports returning the Id of the last touched row.` — Lukasz Szozda, Sep 03 '15 at 17:28
Same result, thanks for suggestions, will check QSqlDriver::hasFeature() — Rich, Sep 03 '15 at 17:31
Checked with QSqldriver.hasFeature(QSqlDriver::LastInsertId) and the feature is supported. — Rich, Sep 03 '15 at 17:38
So if it has LastInsertId capability it means it didn't INSERT this rows. Check using SSMS — Lukasz Szozda, Sep 03 '15 at 17:41
I've been checking each insert with SSMS and the inserts are working fine — Rich, Sep 03 '15 at 17:43
Not a Microsoft SQL expert, but have you tried removing both the SELECT statement and the call to `QSqlQuery::next()` before calling `QSqlQuery::lastInsertId()`? The reason I ask is that lastInsertId() assumes the last SQL statement was an insert. — MrEricSir, Sep 04 '15 at 06:21
@MrEricSir thanks for suggestion, yes I've tried both of those. There's a post on internet suggesting a sql server 2008 driver bug associated with this issue, unfortunatley the link to the ms website comes up 404. I think maybe this is the next avenue to explore. — Rich, Sep 04 '15 at 12:19

score 1 · Accepted Answer · edited May 23 '17 at 12:30

Finally found a work around using OUTPUT clause in sql. I don't exactly know why the other methods I tried don't work. There is a sql server bug associated with this feature but that doesn't explain why it worked in ssms but not in c++ Qt code. The sample below show's the work around. Here's the reference I used to solve this.

bool bFeature = pConn->driver()->hasFeature(QSqlDriver::LastInsertId);

QSqlQuery query(*pConn);
query.prepare("insert into [VH_MANUFACTURER] ([MFG_NAME],[MFG_TYPE],[MFG_LOGO],[MFG_ACTIVE],[MFG_SEO_CONTENT],[MFG_DESCRI],[MFG_CAPMANCODE]) OUTPUT INSERTED.MFG_ID values ('sRRS test man code','RRS type','RRS logo file',1,'RRS SEO para','RRS description','RS')");
if(query.exec())
{
    if(query.next())
    {
         QVariant id=query.value("MFG_ID");
    }

}

Can't retrieve identity field after insert in sql database

1 Answers1