SqlDataSource and null parameters

I came across what is, in my opinion, an absurd default value for something on the SqlDataSource today.

What I was trying to do was to call a table based UDF with a number of parameters, some of those parameters could be left as null and default values would be applied in the select within the UDF.

I spent some time debugging the code, then I tried profiling the SQL server and to my surprise nothing was actually being fired at the SQL server. So, I manually tweaked the parameters whilst on a breakpoint just before my database call. It worked, I got my expected result. Then just before I was about to re-code my UDF to cope with empty strings rather than nulls I did a quick search on google for “SqlDataSource + null parameters” and I stumbled across a forum post detailing a parameter to the SqlDataSource itself called CancelSelectOnNullParameter.

So, I set that to false on my SqlDataSource any hey presto is worked.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s