@@IDENTITY in code

Hi all,

I encountered a problem with SQL server mobile.

I am inserting a row inside a database table through C# code and need to obtain the ID of the last inserted row if successful. I have the following in my code:

SqlCeHelper.ExecuteNonQuery(connectionString, "*INSERT STATEMENT*");

Int64 id = (Int64)SqlCeHelper.ExecuteScalar(connectionString, "SELECT @.@.IDENTITY");

The variable id always ends up NULL. Is this the correct approach to this? Is there anything else I can use to obtain the id of the last inserted row in SQL server mobile?

-- The class SqlCeHelper is a wrapper that I wrote, it works 100%.

Thanks

you should be using Identity_Scope() - its better. I havent worked with SqlCE but this is what I use, and is best used than @.@.Identity, and no idea if this works for SqlCe

My apologies if it does not

I also believe, perhaps I am wrong again, that you are executing 2 different queries, hence why you are receiving null for your last query - again, I may well be totally wrong here. It's a learning curve for me also

|||

Hi,

No...unfortunately SQL mobile doesn't support the function.

I though of the two separate statements as well. Is it possible to run two different SQL statements inside one command?

Thanks

|||

OK,

Fixed the problem. It really was executing these two statements totally separately.

The correct way to fix this is to add a couple lines inside my SQLCeHelper...

I now have something like this inside the SQLCeHelper:

publicstaticobject ExecuteInsert(string connectionString, string command, paramsSqlCeParameter[] commandParameters){

object retval = null;

try{

SqlCeConnection cn = newSqlCeConnection(connectionString);

SqlCeCommand c = newSqlCeCommand(command, cn);

foreach (SqlCeParameter p in commandParameters)

c.Parameters.Add(p);

cn.Open();

c.ExecuteNonQuery();

c = newSqlCeCommand("SELECT @.@.IDENTITY AS [IDENTITY]", cn);

retval = c.ExecuteScalar();

}

catch

{

throw;

}

return retval;

}

sql