SELECT @@IDENTITY Using System.Data.SqlServerCe?

Hi! I am having a problem getting the inserted id from a table.

Here is the table:

/*********************** Companies **********************************/
CREATE TABLE Companies(
CompanyId int IDENTITY(1,1) PRIMARY KEY,
Description nvarchar(30) NOT NULL
)
GO

When I type SELECT @@IDENTITY in SQL Management Studio after a successful insert, it works fine. However, in code (System.Data.SqlServerCe), I get an error using this code:


SqlCeCommand cmd = conn.CreateCommand();

cmd.CommandText = sql;

cmd.CommandType = System.Data.CommandType.Text;

ret = (int)cmd.ExecuteScalar();

I saw a thread with this solution, but do I really have to go to this trouble?

DECLARE @MyTable table
( MyIdentity int )
INSERT INTO Employees ( FirstName, LastName )
OUTPUT inserted.EmployeeID INTO @MyTable
VALUES ( 'Bill', 'Smith' )
SELECT MyIdentity
FROM @MyTable



scope_identity does not seem to work either. Am I missing something?



Thanks!

Michael