Trouble retrieving 'last Insert ID' on SQL Server

[redBeard]

I'm developing a program that will work with both MySQL and SQLServer databases.

On MySQL, I can successfully use the QtSQL module to insert records into the database - the tables have a an 'AUTO_INCREMENT' key field on them. I can successfully retrieve the 'last Insert ID' value via the 'QSqlQuery::lastInsertId ()' method. Works great.

On SQLServer, that feature does not exist so I updated my program to issue a QSqlQuery of "SELECT SCOPE_IDENTITY()". This seems to execute but when I retrieve the resultant values from the query, I get one value and it is always 0.

In a SQL Server Management Studio query window, I successfully inserted a record into a table (as my program does) and executed the "SELECT SCOPE_IDENTITY()" query immediately afterward and it retrieved the correct value.

I've tried a number of options - using transactions and no transactions. commiting the INSERT transaction before issuing the SELECT transaction, etc.

Basic code:

db = QSqlDatabase::database(dbName, true);
        // db.transaction();
        QString queryStr = "INSERT INTO ADDRESS (ts, address1) "
            " VALUES (:ts, :address1)";
        QSqlQuery qQuery (db);
        qQuery.prepare(queryStr);
        QDateTime ts = QDateTime::currentDateTime();
 
        qQuery.bindValue (":ts", QVariant (ts));
        qQuery.bindValue (":address1", QVariant ("test address1"));
 
        if (qQuery.exec()) {
            cout << "Success!" << endl;
 
            // db.commit();
 
            QSqlQuery query2 (db);
            query2.prepare ("SELECT SCOPE_IDENTITY()");
	    query2.setForwardOnly(true);
	    if (query2.exec()) {
	        while (query2.next()) {
	            QVariant dbValue = query2.value(0);
                    cout << "testdb(): DB value: " <<  dbValue.toString().toStdString() << endl;
                }
            } else {
                 // error
            }
        } else {
            // error
        }

db = QSqlDatabase::database(dbName, true);
        // db.transaction();
        QString queryStr = "INSERT INTO ADDRESS (ts, address1) "
            " VALUES (:ts, :address1)";
        QSqlQuery qQuery (db);
        qQuery.prepare(queryStr);
        QDateTime ts = QDateTime::currentDateTime();

        qQuery.bindValue (":ts", QVariant (ts));
        qQuery.bindValue (":address1", QVariant ("test address1"));

        if (qQuery.exec()) {
            cout << "Success!" << endl;

            // db.commit();

            QSqlQuery query2 (db);
            query2.prepare ("SELECT SCOPE_IDENTITY()");
	    query2.setForwardOnly(true);
	    if (query2.exec()) {
	        while (query2.next()) {
	            QVariant dbValue = query2.value(0);
                    cout << "testdb(): DB value: " <<  dbValue.toString().toStdString() << endl;
                }
            } else {
                 // error
            }
        } else {
            // error
        }

To copy to clipboard, switch view to plain text mode 

Ideas?

---

Added after 1 1:

This appears to be a SQLServer 2008 bug and I'm using SQLServer 2008. Grr.....

[allyxcristiano]

Try to use @@IDENTITY poor man.