Damned if you Do Damned if you Don't

Wasting hours on a non-problem is frustrating to say the least.
Such is the case of not knowing about the CancelSelectOnNullParameter property of the ASP.NET 2.0 SqlDataSource control.

There was a time when if I wanted use a language / IDE I'd study a couple manuals cover to cover and Then dive in. While this was time and energy consuming, there was nothing like understanding your tools in their entirety to really solve problems in the most efficient manner. Well, I've gotten older and have a lot less time. My current method of solution discovery is trial and error. While frustrating & rewarding, it saves time in that I'm focused on the areas I believe I need to be in. I've used the SqlDataSource, GridView, FormView and most recently Telerik RadGrid Many times and never ran into a situation where the CancelSelectOnNullParameter was relevant. Now 2+ hours after I should have been on to the next section of the project I'm finally able to adequately test the BASICS of Data Access. I swear I thought I was losing it when the data grid wasn't showing up on a tested and working SqlDataSource. Why does the test work differently than the actual implementation?!?! No more bitching, but a HUGE thanks to ecbruck from the ASP.NET forums.

I'd post a thank you there, but they have the Most RETARDED (that is a medical and technical term not to be confused with salnder again the mentally challenged) non-passport login system! I forgot my password. Send me a link to a page to reset it, except I'm Denied Access to the reset page. WTF!!?!!? Ok. Now, no more bitching... for now.


So, in summary:

If your Gridview, RadGrid, DataReader, etc is not returning data when you KNOW it should be and its DataSource is a SqlDataSource (or AccessDataSource) and there are parameters with no value (null) being passed to it, it's probably because the CancelSelectOnNullParameter property is set to True (the default). Set to false and let the data flow again. I'm so frustrated right now.

ASP.NET 2.0 Data Controls Not Updating - Solution

Couple of extra hooks for searches: GridView, DetalsView, FormView, ASP.NET 2.0, Not Updating, Problem

Please keep in mind these solutions were put into use after much trial & effort and a LOT of google/msdn searching. Also, I suggest these solutions because they worked for me, NOT because I have a deep understanding of why they work (I have a passing understand, but nothing worth writing about).

Solution: The DataKeyNames property of the data control you're using should be set to the Primary field key of the table you're updating. Found the obscure reference here.

Alt Solution: I'm not particularly fond of this solution. It involves making a change to the properties of the (SQL Server) database you're using. In SQL Server 2005 Management Studio, setting ANSI NULLS Enabled = true seems to remove the updating problem as well, but this effects the database and potential pre-written logic on a much larger scale. Make sure you understand the repercussions of changing DB options. Why this works might be discerned from here.

Correlated Subquerys

Ah, the wonderful Correlated Subquery...

This discovery neared completion with a google search for "t-sql select subquery parameter from select". Which returned a link to this article (http://www.databasejournal.com/features/mssql/article.php/3485291).

Why did I need subquery that made use of data from the outer query?

I've got a calendar table built using this article (http://sqlserver2000.databases.aspfaq.com/why-should-i-consider-using-an-auxiliary-calendar-table.html).
Now I have a need to return data from the Quarter Name column and the last date in that quarter for each line item. Sure there are lots of ways to do it, but a solution modified from the first referenced article allows me to do it in a single line of SQL.

I'd previously created a view (view_Quarters) to return DISTINCT quarter names (and id) for simplified inclusion in other queries.

Here's the Code:
SELECT
qn, qid, (SELECT TOP 1 dt FROM Calendar WHERE qid = vQ.qid ORDER BY dt DESC) LastDay
FROM
view_Quarters vQ ORDER BY qid

I knew what I wanted to happen ahead of time, but didn't know if it had been implemented SQL (ANSI or the flavor of my choice). It's always incredible to see that someone else had laid the groundwork for most of the problems you'll run into and even better with a good search engine to make that work easily available. Thank you DatabaseJournal, ASP.FAQ and Google.