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