the4thamigo_uk
November 3rd, 2004, 05:58 AM
(This has caused me so much strife that I thought I would let others know.)
SELECT @@ IDENTITY does not work if there is an unclosed recordset on a connection that was created with Connection::Execute() function. ADO/ODBC or SQL SERVER (I dont know what is at fault here!) returns NULL instead of the last identity value on that connection. The following code will therefore not work unless you uncomment the rs1->Close() line. You can resolve this issue either by closing all recordsets prior to doing an insert or alternatively there is a second work around. The workaround involves creating a new Command object on which you execute BOTH the INSERT statement and the SELECT @@IDENTITY statement. This seems to return valid identity values correctly. The following snippet demonstrates the problem :
CoInitialize(NULL);
_ConnectionPtr pCn;
_ConnectionPtr pCn2;
pCn.CreateInstance(__uuidof(Connection));
pCn->Open(_bstr_t("Driver=SQL Server;Server=ENGINEERING15;Database=MyDatabase"), _bstr_t("guiadmin"),_bstr_t("guiadmin"),-1);
_RecordsetPtr rs1 = pCn->Execute(L"SELECT * FROM MyOtherTable",NULL,adLockReadOnly);
//rs1->Close(); // uncomment this to make the SELECT @@IDENTITY query work
pCn->Execute(L"INSERT INTO MyTable (MyField1,MyField2) VALUES (1,2)",NULL,adExecuteNoRecords);
_RecordsetPtr rs2 = pCn->Execute(L"SELECT @@IDENTITY",NULL,adLockReadOnly);
long lId = rs2->GetFields()->GetItem(0L)->GetValue();
rs2->Close();
SELECT @@ IDENTITY does not work if there is an unclosed recordset on a connection that was created with Connection::Execute() function. ADO/ODBC or SQL SERVER (I dont know what is at fault here!) returns NULL instead of the last identity value on that connection. The following code will therefore not work unless you uncomment the rs1->Close() line. You can resolve this issue either by closing all recordsets prior to doing an insert or alternatively there is a second work around. The workaround involves creating a new Command object on which you execute BOTH the INSERT statement and the SELECT @@IDENTITY statement. This seems to return valid identity values correctly. The following snippet demonstrates the problem :
CoInitialize(NULL);
_ConnectionPtr pCn;
_ConnectionPtr pCn2;
pCn.CreateInstance(__uuidof(Connection));
pCn->Open(_bstr_t("Driver=SQL Server;Server=ENGINEERING15;Database=MyDatabase"), _bstr_t("guiadmin"),_bstr_t("guiadmin"),-1);
_RecordsetPtr rs1 = pCn->Execute(L"SELECT * FROM MyOtherTable",NULL,adLockReadOnly);
//rs1->Close(); // uncomment this to make the SELECT @@IDENTITY query work
pCn->Execute(L"INSERT INTO MyTable (MyField1,MyField2) VALUES (1,2)",NULL,adExecuteNoRecords);
_RecordsetPtr rs2 = pCn->Execute(L"SELECT @@IDENTITY",NULL,adLockReadOnly);
long lId = rs2->GetFields()->GetItem(0L)->GetValue();
rs2->Close();