Parameters: How to set null value

Hi, I have a problem.
I have two parameters: Group and Selection

In the first I have 2 choices: Total and Day
And the value of the second parameter is dynamic and is sets to allow null value
The behaviour I want is this: If a user select Total in the first parameter, the second must be set to NULL, if the selection is Day, the second must be set with a data set.

How I can set the value of the second parameter with null, I tryed

Have anyone an idea?

One way I have done this in the past is using '-1' as the Total value, and then letting the stored procedure pass in a null value by setting all '-1' parameters to null.

if @.param1 = '-1' set @.param1=null

System.DbNull.Value may work too.

Yet another option that may be possible is to use an expression to convert the parameter to a null value, and pass in the expression instead of the parameter to the report.

cheers,

Andrew

|||thanks,
I tryed System.DbNull.Value but give me this error : DbNull is a type in system and cannot be used as an expression

Perhaps I wrong something:
I wrote this in the datasets I use for the second parameter

=iif(Parameters!parm1.Value = 2,
"select a11.DATE_ID from Day" ,System.DBNull )

Another question, where I have to place this expression, in the store procedure?

if @.param1 = '-1' set @.param1=null

but I use a dataset.

Thanks in advance

|||

Not sure exactly how you are passing in a dataset as a parameter but looks interesting.

Instead of null perhaps you could use "SELECT null as DateID" instead? Or maybe an empty string?

The syntax for getting dbnull is to use System.DBNull.Value

=iif(Parameters!parm1.Value = 2,
"select a11.DATE_ID from Day" ,System.DBNull.Value )

cheers,

Andrew

|||If I use this expression "System.DBNull.Value" something don't work but I don't understand what.
but following your example and this link forums.microsoft.com/MSDN/ShowPost.aspx?PostID=766574&SiteID=1
it works

thanks

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?

Parameterized query returns one row with null values.

I am hoping someone could help me understand why this is happening and perhaps a solution.

I am using ASP.NET 2.0 with a SQL 2005 database.

In code behind, I am performing a query using a parameter as below:

sql = "SELECT field_name FROM myTable WHERE (field_name = @.P1)"

objCommand.Parameters.Add(New SqlParameter("@.P1", TextBox1.Text))

The parameter is obtained from TextBox1 which has valid input. However, the value is not in the table. The query should not return ANY results. However, I am getting one single row back with null values for each field requested in the query.

The SQL user account for this query has select, insert, and update permissions on the table. The query is simple, no joins, and the table has no null values in any fields. If I perform the exact same query using an account with select only permission on the table, I get what I was expecting, no records. Then if I go back to the previous user account with more permissioins, and I change the query to pass the paramter this way:

sql =String.Format("SELECT field_name FROM myTable WHERE (field_name = {0})", TextBox1.Text)

I also get NO records retuned using the same criteria.

What is going on here? I would prefer to use the parameterized query method with the account having elevated permissions. Is there some command object setting that can prevent the null row from returning?

Thanks!

I am not sure but see if adding the datatype helps:

objCommand.Parameters.Add(New SqlParameter("@.P1", SqlDbType.Varchar,30)).value = TextBox1.Text

|||

Thanks for the suggestion. I tried adding the data type as you suggested. It did not change the results.

I have found that if I change to a data reader, the null value is not being returned. So, now it looks to be related to the ExecuteScalar method.

|||

I also just realized that it is not a null value being returned but instead an empty value, ie "".

I can get around this easily enough in multiple ways, I am just wanting to understand why this is happening.

So far I have this narrowed down to the following:

A parameterized query, with a user account having select, insert, update permission, and using the ExecuteScalar method. This combination returns a record with an empty result when the criteria is not found in the table instead of returning no records at all.

|||

Eh?

ExecuteScalar is used to return the first column of the first row of the query. If there is no rows, the value comes back as null.

I think perhaps you are misunderstanding what ExecuteScalar is supposed to do. It doesn't return records, or recordsets, it returns a singular scalar value (One column of one row - the first of each).

For further help, please post the whole code block in question. How you initialize your connection, command objects, how you are actually executing the query, where you are storing the result of the query (And how it is defined), and what you expected the result to be, and what you actually got.

If the results are varying depending on what user is executing the query, please make sure that either you explicitly define the schema you want to use, or that there doesn't exist multiple tables with the same name under different schemas (Refer to the table as dbo.Table not just Table).

|||

Ok, my bad, stupid mistake(s) with both user permissions and also with the string.format method.

I at least have it consistenly returning the empty record.

One last question, why return null/empty instead of just nothing like a data reader?

Thank you very much for the response.

|||

Hi,

ExecuteScalar is designed to return a single value from a database command and the proper representation of a single non-existant value is returning null. The ExecuteScalar is a non-void method and should return something!

Enjoy C#,

Mehrdad

|||

Thank you to everyone for the help and clairification on ExecuteScalar.