Can't delete data with condition in SQL Server Compact Edition

Question

I'm working with SQL Server Compact Edition. I try to delete data from the database on max date, I try on query it works perfectly, but when I execute in my program, turn to delete all data not base on query I have created.

Here is my code

string sqlCom="";
sqlCom = " delete from " + tableName; ;
sqlCom += " where messageid not in(";
sqlCom += " select messageid from tabmessageinclient";
sqlCom += " where convert(nvarchar(10),dtmessagetime,101) ";
sqlCom += " in (select max(convert(nvarchar(10),dtmessagetime,101)) from        tabmessageinclient ))";

SqlCeConnection ceCon = new     SqlCeConnection(Properties.Settings.Default.MyConnection);

if (ceCon.State == System.Data.ConnectionState.Open)
{ ceCon.Close(); }

ceCon.Open();
SqlCeCommand cmd = new SqlCeCommand();
cmd.Connection = ceCon;
cmd.CommandText = sqlCom;
cmd.ExecuteNonQuery();

does anyone know what wrong with my code, sorry for bad english

Use @ symbol to write string on more than one line, currently this is unreadable for me. Also try to format your query ... — mybirthname, Mar 15 '16 at 05:04
[SQL Injection alert](http://msdn.microsoft.com/en-us/library/ms161953%28v=sql.105%29.aspx) - you should **not** concatenate together your SQL statements - use **parametrized queries** instead to avoid SQL injection — marc_s, Mar 15 '16 at 05:37

score 0 · Answer 1 · edited May 23 '17 at 11:50

I would suggest firing all sub queries separately once to confirm that those sub queries return the correct set of data. i.e. in Following order

select max(convert(nvarchar(10),dtmessagetime,101)) from tabmessageinclient
select messageid from tabmessageinclient where convert(nvarchar(10),dtmessagetime,101) in (select max(convert(nvarchar(10),dtmessagetime,101)) from tabmessageinclient)

If this returns an expected data set, then verify if the second command gives any null values for messageid. When we use not in, it tends to not bring back anything if any of the selection value is null. In which case it would be better to use another and condition in your subquery.

where messageid is not null

You can read more about this behavior on SQL NOT IN not working

Just curious if there's any particular reason for using string concat? You may also want to use verbatim string (and string format), just so that your query is more legible.

string sqlComm = string.format(@"delete from {0} where messageid not in 
(select messageid from tabmessageinclient where convert(nvarchar(10),dtmessagetime,101) in
(select max(convert(nvarchar(10),dtmessagetime,101)) from tabmessageinclient))", tableName);

Can't delete data with condition in SQL Server Compact Edition

1 Answers1