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
adam
identity
Hot Topic
- How to transfer data from a SqlServer database to a SqlServer Express database
- Class does not support aggregation
- Bug in OWC11 - Wrong Totals
- How do I create a copy of a record in a table? ( identity is used on PK )
- SQL Server Express is locking the database
- Huge issue with the Lookup problem
- Blank Error Messagebox Displayed at startup
- Cannot connect to SQL Server 2005 April CTP from remote computer
- Consuming Multiple Messages In Parallel from Multiple Windows Services
- Indexing SL CE to make search faster. How?
SQL Server
- Dynamically modifying flat file connector based on variable header row
- Report Server error
- Reading and writing same variable to a script
- Parameters in URL - Problem
- How to convert a timestamp into dateTime in SQLServer 2000
- How to Pass parameters in SSIS dataflow
- Embedding/Distributing SQL Express With A VB Program
- Textbox Calculations
- OLE DB Destination - table name variable
- No comment
identity
M. Lohmeijer
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
tryToLearn
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.