SQLDatasource Controlparameter and the table Null Value problem

I am a fan of codeless databinding. Today i found a problem where i got doubts. A table contains a varchar field where null values are possible. This field is the source for a query with controlparameter.  So you need something like

select * from table1 where field like @par1 +'%'

The ugly thing is that when the "field" contains sometimes no value , means <null>, the record is not in the result set if searching for %. I use this % as defaultvalue to get all records if nothing is in the textbox.

<SelectParameters>

<asp:ControlParameter ControlID="TextBox1" PropertyName="Text" Name="par1"  DefaultValue="%"/>

</SelectParameters>

The workaround is to change the sql command in sqldatasource to

select * from table1 where IsNull(field,'') LIKE IsNull(@par1+'%','%' )  

PS: thanks to Doug Reilly, i got the trick after 2 hours searching from a old posting from him.

Published Tuesday, January 03, 2006 7:43 PM by preishuber

Comments

# re: SQLDatasource Controlparameter and the table Null Value problem

Tuesday, January 03, 2006 6:25 PM by scottgu
If you are binding with a GridView, there is also a parameter you can set where you can configure the default value of a NULL parameter in it (so for example -- you could make a NULL value act like an empty string). This would be another way to avoid the null check in your SQL query.

Hope this helps,

Scott

# re: SQLDatasource Controlparameter and the table Null Value problem

Wednesday, January 04, 2006 2:40 AM by Hannes Preishuber
Thanks Scott
what parameter and where is it?
The DefaultValue of the controlparemter doesnt solve that problem.

# re: SQLDatasource Controlparameter and the table Null Value problem

Friday, July 14, 2006 6:19 PM by Jim

I was getting so annoyed that I couldn't figure out why the null value of the text box made my gridview empty. The DefaultValue was just the trick. An additional note is that adding that like clause with the % could be a resource problem so maybe you want to try:

WHERE 1 =

CASE

WHEN @FirstName <> '%' THEN

CASE

WHEN P.FirstName LIKE @FirstName + '%' THEN 1 ELSE 0

END

ELSE 1

END

this way it ignores the like and doesn't have to search every record.

# re: SQLDatasource Controlparameter and the table Null Value problem

Thursday, November 15, 2007 6:22 AM by StuFF mc

What about this Property: ConvertEmptyStringToNull. I actually have the oposite problem. I am sending null and it doesn't seem to convert it to DBNull automatically :(

# re: SQLDatasource Controlparameter and the table Null Value problem

Tuesday, February 12, 2008 5:03 AM by Tom Pester

"What about this Property: ConvertEmptyStringToNull. I actually have the oposite problem. I am sending null and it doesn't seem to convert it to DBNull automatically :("

=> Than you need to set CancelSelectOnNullParameter to False of the DataSource control.

# re: SQLDatasource Controlparameter and the table Null Value problem

Thursday, July 10, 2008 11:18 AM by Michael Mach

Awesome.  I had the exact same problem.  I was debugging through VS 05 and it was returning the values okay.  Also, was working in SQL 05.  The textboxes were not working.  the DefaultValue did the trick!  Thanks!

# re: SQLDatasource Controlparameter and the table Null Value problem

Wednesday, July 22, 2009 11:39 AM by Eric Barr

I needed to create a filter on a numeric field that also included an "ALL" option.  So in my query, I converted the numeric field to a string and used the LIKE clause like this:

WHERE CONVERT(varchar(50), AssignedToStaffID) LIKE @assignedToStaffID

Then I added the "ALL" option to the DropdownList with a Value of "%" so when "ALL" is chosen, it matches all numeric ID's.  

Thanks, the post helped a lot!

# re: SQLDatasource Controlparameter and the table Null Value problem

Friday, December 04, 2009 2:05 PM by Sudip Shrestha

Not a big fan of codeless databinding but better way is to:

ConvertEmptyStringToNull="false"

# re: SQLDatasource Controlparameter and the table Null Value problem

Sunday, September 12, 2010 1:08 PM by Dani Saputra

Great, its working. Thanks to Sudip Shrestha

# re: SQLDatasource Controlparameter and the table Null Value problem

Saturday, March 31, 2012 8:51 AM by bhavin

SqlDataSource1.Select(DataSourceSelectArguments.Empty);

# re: SQLDatasource Controlparameter and the table Null Value problem

Tuesday, May 15, 2012 3:15 PM by Torque

Love this, quick and to the point. Solved my problem =D

Leave a Comment

(required) 
(required) 
(optional)
(required)