Insert query failing when using a parameter in the associated select statement in SQL Server CE

Question

INSERT INTO voucher (voucher_no, account, party_name, rece_amt, particulars, voucher_date, voucher_type, cuid, cdt)
    SELECT voucher_rec_no, @account, @party_name, @rece_amt, @particulars, @voucher_date, @voucher_type, @cuid, @cdt 
   FROM auto_number 
   WHERE (auto_no = 1)

Error:

A parameter is not allowed in this location. Ensure that the '@' sign is in a valid location or that parameters are valid at all in this SQL statement.

Answer 1

I've just stumbled upon this whilst trying to fix the same issue. I know it's late but, assuming that you're getting this error when attempting to execute the query via .net, ensure that you are setting the SqlCeParameter.DbType - if this is not specified, you get the exception you listed above.

Example (assume cmd is a SqlCeCommand - all the stuff is in the System.Data.SqlServerCe namespace):

SqlCeParameter param = new SqlCeParameter();
param.ParameterName = "@SomeParameterName";
param.Direction = ParameterDirection.Input;
param.DbType = DbType.String;   // this is the important bit to avoid the exception
param.Value = kvp.Value;
cmd.Parameters.Add(param);

Obviously, you'd want to set the DB type to match the type of your parameter.