@@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

@@IDENTITY and thread safety

I am currently using @.@.Identity to retreive the Identity value for the PK field in a table that I am inserting data into. Essentially, my code looks like this:
SqlCommand cmd=new SqlCommand("Insert into table(... ;Select @.@.Identity from table",conn);
string identity=cmd.ExecuteScalar();

Testing this myself, it works fine, but I am worried as to how thread safe this is in a real-world environment? (i.e. with multiple users clicking at it). Is there a guaranteed way to make this thread safe- wrap it in a transaction maybe?
I think you should use scope_identity()|||I would search the online books on @.@.Identity. I'm sure the subject istouched upon. I seem to recall something about @.@.Identity applying tothe current connection or context but I forgot the details. In anycase, @.@.Identity is so widely used that if it wasn't thread safe we'dall be in serious trouble.
|||scope_identity()
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_sa-ses_6n8p.asp
@.@.Identity
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_globals_50u1.asp
@.@.Identity is thread safe, but scope_identity() is the way to go.|||

Everyone,

Thanks for the help. JHouse, those articles are great. Just for the benefit of others reading this thread, the key difference between the @.@.IDENTITY and Scope_Identity() is the @.@.IDENTITY returns the last identity value of any table your batch or procedure inserts into - implicit or explicit - while Scope_Identity() is explicit only. So if you insert into tableA that has a trigger that inserts into tableB. @.@.Identity will return the identity from tableB, and Scope_Identity will return the identity from tableA.

(Simple) SQL question regarding P/F keys and inserting data...

Hi, all:

I'm a newbie trying to understand the concept of referential integrity and
dealing with Primary and Foreign Keys. I'm sure mine is a simple problem...

I've created 3 tables as follows:

MemberTable
--
1 Member ID (Primary key)
2 Member Name
3 etc...

FoodsTable
--
1 Food ID (Primary key)
2 Food Name

MemberFoods
--
1 Member ID (Foreign key)
2 Food ID (Foreign key)

Now, I've just learned about referential integrity and all that, and this is
my first foray into creating primary/foreign keys, linking tables, etc. (so
I'm assuming the above tables are fine). What I don't understand is this:
if I have a (checkbox) form from which members can choose their favorite
foods, how do I insert that data? For example:

Food Form
--
Which of these are your favorite foods:
1) Steak
2) Chicken
3) Potatoes
4) etc...

If I want to insert, let's say, 'Steak' and 'Chicken', do I have to first do
a query on my Foods table to retrieve the Food ID's for Steak and Chicken,
and then do an "insert" using those ID's into my MemberFoods table? (I am
using the actual Food Names (not ID's) as values on my form.) If so, can
someone please share the SQL for retrieving and then inserting?

I don' t know...maybe I'm overcomplicating the issue, but it just seems like
a lot of extra work to maintain a "linking table" of foreign keys.

Thanks for the help.Replied in microsoft.public.sqlserver.programming

Please don't multi-post.

--
David Portas
----
Please reply only to the newsgroup
--