cmd.Add(new SqlCeParameter("@.IntColumn", SqlInt32.Null));
cmd.CommandText = "select count(*) from TableName where IntColumn is not @.IntColumn";
cmd.Add(new SqlCeParameter("@.IntColumn", DBNull.Value));
cmd.CommandText = "select count(*) from TableName where IntColumn is not @.IntColumn";
cmd.Add(new SqlCeParameter("@.IntColumn", SqlInt32.Null));
cmd.CommandText = "select count(*) from TableName where not IntColumn = @.IntColumn";
cmd.Add(new SqlCeParameter("@.IntColumn", DBNull.Value));
cmd.CommandText = "select count(*) from TableName where not IntColumn = @.IntColumn";
cmd.Parameters.Add(new SqlCeParameter("@.IntColumn", SqlDbType.Int32));
cmd.Parameters["@.IntColumn"].Value = SqlInt32.Null;
cmdGetNumRead.CommandText = "select count(*) from TableName where IntColumn is not @.IntColumn";
cmd.Parameters.Add(new SqlCeParameter("@.IntColumn", SqlDbType.Int32));
cmd.Parameters["@.IntColumn"].Value = SqlInt32.Null;
cmdGetNumRead.CommandText = "select count(*) from TableName where not IntColumn = @.IntColumn";
cmd.Parameters.Add(new SqlCeParameter("@.IntColumn", SqlDbType.Int32));
cmd.Parameters["@.IntColumn"].Value = DBNull.Value;
cmdGetNumRead.CommandText = "select count(*) from TableName where IntColumn is not @.IntColumn";
cmd.Parameters.Add(new SqlCeParameter("@.IntColumn", SqlDbType.Int32));
cmd.Parameters["@.IntColumn"].Value = DBNull.Value;
cmdGetNumRead.CommandText = "select count(*) from TableName where not IntColumn = @.IntColumn";
cmd.Parameters.Add(new SqlCeParameter("@.IntColumn", SqlDbType.Variant));
cmd.Parameters["@.IntColumn"].Value = SqlInt32.Null;
cmd.CommandText = "select count(*) from Meter where IntColumn is not @.IntColumn";
cmd.Parameters.Add(new SqlCeParameter("@.IntColumn", SqlDbType.Variant));
cmd.Parameters["@.IntColumn"].Value = SqlInt32.Null;
cmd.CommandText = "select count(*) from Meter where not IntColumn = @.IntColumn";
md.Parameters.Add(new SqlCeParameter("@.IntColumn", SqlDbType.Variant));
cmd.Parameters["@.IntColumn"].Value = DBNull.Value;
cmd.CommandText = "select count(*) from Meter where IntColumn is not @.IntColumn";
cmd.Parameters.Add(new SqlCeParameter("@.IntColumn", SqlDbType.Variant));
cmd.Parameters["@.IntColumn"].Value = DBNull.Value;
cmd.CommandText = "select count(*) from Meter where not IntColumn = @.IntColumn";
Whenever I use a "is not" in the query I get an exception, otherwise it returns a count of 0 which is not accurate. Any help will be appreciated.
try using this syntax:
WHERE [columnName] != DBNull.Value or WHERE [columnName] <> DBNull.Value
Darren
|||I could not get that to work. Could you provide a more detailed examle of what the c# code would actually look like?|||
I think you've found yourself a bug in the ADO provider for SQL Mobile. I tried this code on the Northwind database and it reports 0 rows (should be 22):
SqlCeConnection cn = new SqlCeConnection(@."Data Source = \program files\deviceapplication7\Northwind.sdf");
try
{
cn.Open();
SqlCeCommand cmd = new SqlCeCommand("SELECT count(*) FROM CUSTOMERS WHERE FAX = @.fax", cn);
SqlCeParameter p1 = new SqlCeParameter("@.fax", DBNull.Value);
cmd.Parameters.Add(p1);
object o = cmd.ExecuteScalar();
if ( o != null && o != DBNull.Value)
{
MessageBox.Show("There were " + ((int)o).ToString() + " null fax numbers");
}
else
MessageBox.Show("Result of query was null");
This only occurs with a parameterized query however. If you use the normal syntax (IS NULL) in your query, you'll get the right results.
Darren
|||
Thanks for your response Daren. I started a new project and ran a program a program with the very similar code as what you tried and I got the same result. I was then wondering if the limitation/bug might be related to using SqlCeCommand.ExecuteScalar with parameterized queries in general rather than specifically looking for null or not null. So I tried this program and it returned a count of 1 which is correct:
SqlCeConnection conn = new SqlCeConnection(@."Data Source= \Program Files\BugTesting\Northwind.sdf;");
conn.Open();
SqlCeCommand cmd = conn.CreateCommand();
cmd.Parameters.Add(new SqlCeParameter("@.fax", SqlDbType.NVarChar, 24));
cmd.CommandText = "select COUNT(*) from Customers where Fax = @.fax";
cmd.Prepare();
cmd.Parameters["@.fax"].Value = "030-0076545";
object o = cmd.ExecuteScalar();
if (o != null && o != DBNull.Value)
{
MessageBox.Show("There were " + ((int)o).ToString() + " null fax numbers");
}
else
{
MessageBox.Show("Result of query was null");
}
conn.Close();
conn.Dispose();
This shows that you can do parameterized queries with count and execute scalar, but I still have not found a way to count the number of null values. I don't really know how parameterized queries work under the hood, but I would guess that the code that Darren posted would translate into something like this:
SELECT count(*) FROM CUSTOMERS WHERE FAX = null
If this is true then that would explain why it returns a count of 0. Which is why in my original post I was trying things more along the lines of: cmd.CommandText = "select count(*) from Customers where Fax is @.fax"
but that generates an SqlCeException. Is this a limitation, a bug, or is there still maybe another way to do this? Anyway the only reason why I was trying to do this as a parameterized query is because in my code runs it multiple times in a row, and I thought it might run faster this way. Even if I could get this to work somehow do think that it would improve the execution speed of the query? Also where is the best place to report this as a potential bug?
