How can you get @@IDENTITY or SCOPE_IDENTITY() to work in SQL Server Compact Edition?

Question

In my SQL Server Compact Edition database, this works:

INSERT INTO Clients (Title, FirstName, MiddleName, LastName, Suffix, IsMale)
VALUES (NULL, NULL, NULL, NULL, NULL, NULL)

But this doesn't work:

INSERT INTO Clients (Title, FirstName, MiddleName, LastName, Suffix, IsMale)
VALUES (NULL, NULL, NULL, NULL, NULL, NULL); SELECT @@IDENTITY

What's the dialectual hold-up here? I am passing it all in as only one string/command to SQL Server CE from VB.Net, if that makes any difference.

Originally I tried doing this with SCOPE_IDENTITY() and would have strongly preferred that, but apparently that's not supported? If there is a work-around to the SCOPE_IDENTITY() issue, I would prefer an answer involving that work-around; if not, then I'd be good with an answer involving @@IDENTITY.

Thanks!

The obvious. Do all columns allow null and is a row inserted? — paparazzo, Apr 10 '13 at 04:22
From a quick google search that also returned your cross-posted question (grr) it seems like compact edition does not allow batch queries, so you will have to SELECT @@IDENTITY in another query right after. You might want to use a transaction so you don't get bad values in the event of lots of users hitting the database at the same time. — James, Apr 10 '13 at 04:54
Not my question. The second insert doesn't work is not defined. Does the second insert but not return an identity. Or does the second not even insert. — paparazzo, Apr 10 '13 at 18:01

score -1 · Answer 1 · answered Apr 10 '13 at 09:02

-1

Er... you cant If you google either of these commands they give only full blown SQL as the supported versions.

answered Apr 10 '13 at 09:02

Ian P

1,724
1
10
12

How can you get @@IDENTITY or SCOPE_IDENTITY() to work in SQL Server Compact Edition?

1 Answers1