SQL Server CE doesnt recognize DECLARE, BEGIN, COMMIT

Question

I am trying to insert data in two tables in a single query. the second table requires the scope_identity() value from the first one.

My query is :

 String query = "DECLARE @questionID int;
            INSERT INTO question (questionText) VALUES(@question);SELECT @questionID = scope_identity();
            INSERT INTO topic_question(toppicID,questionID) VALUES(@topic,@questionID);";

Executing it as:

SqlCeCommand commandInsert = new SqlCeCommand(query, connection);
commandInsert.Parameters.AddWithValue("@question", question);
commandInsert.ExecuteScalar();

Got an error message:

There was an error Parsing the query.Token Line number =1, Token line offset =1, Token in error = DECLARE. ..........

I tried using BEGIN TRANSACTION in the beginning of the query and COMMIT at last of query. But still error remains. Can anybody please note, what I am doing wrong ?

Answer 1

You cannot declare variables in SQL Server CE and only one statement per command is allowed.

So you have to split your statement and execute it one-by-one like:

SqlCeCommand cmd = new SqlCeCommand("INSERT INTO question (questionText) VALUES(@question)", connection);
cmd.Parameters.AddWithValue("@question", question);
cmd.ExecuteNonQuery();

cmd.CommandText = "select @@IDENTITY";
cmd.Parameters.Clear();
var id = cmd.ExecuteScalar();

cmd.CommandText = "INSERT INTO topic_question(toppicID,questionID) VALUES(@topic,@questionID);"
cmd.Parameters.Clear();
cmd.Parameters.AddWithValue("@topic",.....);
cmd.Parameters.AddWithValue("@questionID", id);
cmd.ExecuteNonQuery();

Note you have to keep your connection open (don't close it) after first call cmd.ExecuteNonQuery(); in order for select @@identity to work.