identity

i am using sql server mobile

I have an insert statement with select @.@.identity:
commandString = "insert into Location (Address,Suburb,State) values('627 beach rd', 'rose bay', 'NSW');SELECT @.@.IDENTITY"

I execute the insert statement as below:
command.CommandText = commandString;
id = command.ExecuteNonQuery();

I get the following error:
Error: There was an error parsing the query. [ Token line number = 1,Token line offset = 137,Token in error = SELECT ]
Source: SQL Server 2005 Mobile Edition ADO.NET Data Provider

this statement works ok in sql mobile query analyzer

does anybody know where i am going wrong

do i need an output parameter

thanks
adamTry ...

SELECT SCOPE_IDENTITY() AS LocationID

...instead...I haven't worked with SQL Mobile, but you should rarely use @.@.IDENTITY. @.@.IDENTITY will give you the last identity generated in the database by any session. If there's a trigger or other user session that generates an IDENTITY for another table or the same table, you will get the wrong value. SCOPE_IDENTITY() gives you the last IDENTITY value generated by your session. (see BOL for more details about the difference.)

Also, look into the use of a simi-colon between the statements. I've seen that throw wierd errors. You might replace that with a CRLF instead. You can determine whether the semi-colon or the @.@.identity is causing the issue by replacing the @.@.identity with a one...(ie...SELECT 1 AS LocationID)

You might also need the (AS LocationID) in the statement for ADO to assign a column name to the return value, but not sure...

Lastly, as a matter of good form, you might consider placing this whole thing into a simple stored proc.

CREATE PROCEDURE dbo.LocationInsert (
@.Address VARCHAR(255),
@.Suburb VARCHAR(255),
@.State VARCHAR(255) )

AS

BEGIN

INSERT INTO dbo.Location (
Address,
Suburb,
State )
VALUES (
@.Address,
@.Suburb,
@.State )

SELECT SCOPE_IDENTITY() AS LocationID

END

Then, simply call the stored proc with the following....

commandString = "EXEC dbo.LocationInsert @.Address='627 beach rd', @.Suburb='rose bay', @.State='NSW'"

Good luck...

g2|||Setting aside issue of SCOPE_IDENTITY vs. @.@.IDENTITY, the problem you are seeing is a limitation of SQL Mobile.

This is a piece of SQL Server 2005 Mobile Books Online (http://msdn2.microsoft.com/en-us/library/ms173053(en-us,SQL.90).aspx)

Queries that can typically be run on SQL Server Mobile can also run on Microsoft SQL Server; however, many of the features of Transact-SQL are absent from SQL Server Mobile, and only a single SQL statement can be executed in a command.

Stored procedures are not allowed either (http://forums.microsoft.com/msdn/ShowPost.aspx?PostID=104249).

To Greg: semicolons are required on Mobile to separate statements, so CRLF won't help in this case (http://forums.microsoft.com/msdn/ShowPost.aspx?PostID=92581).

Regards,
Boris.