Friday, June 22, 2007

Selected Event not Firing for SqlDataSource: CancelSelectOnNullParameter

The problems I've had with declarative code are the subject of another post, but suffice it to say that I have not yet achieved "declarative enlightenment." Nevertheless, I dutifully trudge forward, using the SqlDataSource object instead of explicitly grabbing my data.


The main idea behind declarative programming is to make programmer intent more easily expressible and creating a SqlDataSource and telling a control the ID of that data source is right in-line with that idea. Unfortunately, things break down when the code running behind the declarative markup makes assumptions about your intent that are non-intuitive.


Enter the Boolean CancelSelectOnNullParameter of the SqlDataSource. This very well-named property defaults to TRUE. That's right, if you have multiple parameters on your SelectCommand and one of them is null, the SqlDataSource will not execute the SelectCommand. The page will blithely continue executing and the control(s) that are bound to the SqlDataSource will not get populated.

Interestingly, the Selecting event of the SqlDataSource will fire but the Selected event will not. This makes perfect sense: what doesn't make sense is the default. Cancelling the SelectCommand is an optimization--APIs should NEVER make an assumption around an optimization.

8 comments:

Jon said...

Christopher,

At last, the solution. I was about to give up on sqldatasource as I could not get it working with SQL server and stored procedures. None of the articles mentioned CancelSelectOnNullParameter when using the Select method with DataSourceSelectArguments.Empty. It is bizarre that CancelSelectOnNullParameter is true by default.

Thanks again.

Jon M. Wear said...

It's 3 years later and this post just saved me from throwing my workstation out the window. Why default to true? Why? Why?

TRINAKRIAE said...

4 years later you saved my health and the life of my workstation! I agree with Jon...why the default is true? This happened before to me with the ConvertEmptyStringToNull attribute in the UpdateParameters which is treu by default as well...thanks!!!!

Martin Brennan said...

Going to have to agree with the other guys, you just saved my computer from being hurled out of a window. Thanks so much for this solution, I had a stored procedure that has all parameters optional so this was a lifesaver.

Unknown said...

THANK YOU!!!! 6 years later and I can't believe this!!!!

sadbasturd99 said...

I tried so many things, read so many forum posts. I put code in every event for the datasource. In every event of my other datasource. Nothing would ever happen. And then I found this.

Unknown said...

This has bitten me SO MANY TIMES! I forget about it every time. Thank goodness for your post.

Anonymous said...

I about died laughing at the above comments. Nearly 10 years later and this post is still preventing laptop-sized holes in nearby walls/windows.