OK

We have two issues here. First, you can't use TSQL variables in SQL CE. Secondly, SQL CE processes single commands through a connection. So you need to do the following through your .NET application:

As I haven't got a copy of your database or schema to work against, the following is to demonstrate the principle of what you need to do. It is not tested or working code.

Firstly declare a recordset or datareader object and populate it with the result of the first query:

SELECT TOP 1 FOREIGN_RESIDENCE_LOCATION_CODE FROM FOREIGN_RESIDENCE_LOCATION WHERE COUNTRY_ID

IN

(SELECT HOST_COUNTRY_PLACE_ID FROM COST_PROJECTION CP

WHERE CP.COST_PROJECTION_ID = 2)

)

ORDER BY SORT_INDEX

With the value now in that object, you are ready for the next part of the query which can be executed as follows:

using (SqlCeConnection connect = new SqlCeConnection("Data Source=C:\[YourDataFileHere].sdf"))

{ connect.Open();

int location = [value from first aprt of the query];

int num = 0;

using (SqlCeCommand command = new SqlCeCommand("UPDATE JURISDICTION_COUNTRY_INPUT

SET FOREIGN_RESIDENCE_LOCATION_CODE = @tmp_RESIDENCE_LOCATION

WHERE COUNTRY_TYPE = 1 AND COST_PROJECTION_ID = 2", connect))

{ command.Parameters.Add("@tmp_RESIDENCE_LOCATION", SqlDbType.[Int or whatever datatype it is], location);

command.ExecuteNonQuery();

}

}

HTH