Problem retrieving identity value in output param?

[Mervin Williams]

My page includes a dataset with a dataadapter for the Contacts table, with
insert command that reads:

================================================================

INSERT INTO Contact
(firstname, lastname, title, phone)
VALUES (@firstname, @lastname, @title, @phone);
SELECT contact_id, firstname, lastname, title,
phone
FROM Contact
WHERE (contact_id = @@IDENTITY)
================================================================

I've configured an output parameter for this insert command (@contact_id)
that retrieves the autoincrement value for the contact_id column.

Nonetheless, the value of the output parameter is returning null after the
insert, although the value exists in the database.

Please help! Here is the data adapter update and parameter retrieval code.

================================================================
// Create new row object
dsCompanyInfo.ContactRow rowToInsert =
(dsCompanyInfo.ContactRow)dsCompanyInfo.Tables["Contact"].NewRow();

// Updates the dataset table.
rowToInsert.firstname = newFirstName;
rowToInsert.lastname = newLastName;
rowToInsert.title = newTitle;
rowToInsert.phone = newPhone;

dsCompanyInfo.Tables["Contact"].Rows.Add(rowToInsert);

Boolean hasChanges = dsCompanyInfo.HasChanges();

// Calls a SQL statement to update the database from the dataset
daContact.Update(dsCompanyInfo);
daContact.Fill(dsCompanyInfo);
if (daContact.InsertCommand.Parameters["@contact_id"].Value != DBNull.Value)
{
contactid =
(int)daContact.InsertCommand.Parameters["@contact_id"].Value;
}
================================================================

Mervin Williams

 

[Steve]

Hi Mervin

I recently looked into returning an Identity value from the database after
an insert. I found that the recommended method was to use the SCOPE_IDENTITY
variable after performing the insert. Lookup SCOPE_IDENTITY and @@IDENTITY
in Books to familiarise yourself with the differences.

I would change the function to this:

INSERT INTO Contact
(firstname, lastname, title, phone)
VALUES (@firstname, @lastname, @title, @phone)
SELECT @contact_id = SCOPE_IDENTITY()

Research to see if this meets your needs. I'm using it at the moment and it
works fine, but it's only really tested when put into a Multi-user
environment, that's where the @@IDENTITY is meant to start giving incorrect
results compared to the SCOPE_IDENTITY.

Kind Regards,
Steve.