parameterized query that counts the number of null values in an int32 column

Using c# in the compact framework, is there a way to do a parameterized query for counting the number of records where a specified column is null. I tried all of these, but none of them work:

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?