Could not have gone more crazy than what a simple query made me today. Got into two problems, and then got a single solution for both.
The task was to bind a Grid View to a SQLDataSource which pulls data using a store procedure based on the parameters passed. (okay, such a silly task! max 30 mins including writing stored proc and doing UI, who would want to waste time on this? lets get started!) After creating the required components and doing the needed plumbing, ran my application to check out the result. What? Got no data? Why?
I am using Control Parameters to set filters, just two of them.
<asp:SqlDataSource ID="sdsUsers" runat="server"
ConnectionString="<%$ ConnectionStrings:MyDB %>"
SelectCommand="GetUsers"
SelectCommandType = "StoredProcedure"
>
<SelectParameters>
<asp:ControlParameter Name="Name" ControlID="txtName" DefaultValue=""/>
<asp:ControlParameter Name="IsActive" ControlID="chkActive" DefaultValue="false"/>
</SelectParameters>
</asp:SqlDataSource>
I quickly ran SQL Profiler to check what's happening. Woa, no backend hit! I quickly blamed on my laziness to not to set all the properties of Control Parameter. After a little debugging using the trial & error approach, I found that it fetches data when the 'DefaultValue' is not set to an empty string for the 'Name' parameter. I was expecting it to send NULL or '' to the stored procedure which handles such a scenario. But it didn't! After reviewing other remaining properties of the ControlParameter, it didn't take me any time to find 'ConvertEmptyStringToNull' property. So I set its value to 'false' for the 'Name' parameter and it started sending empty string - '' - to the stored procedure.
So far so good, it started working and I wanted to quickly finish off this sample so I didn't bother much to dig about NULL. But couldn't avoid that for long! (Usually, you end up spending more time on silly issues only when you don't have time for them! And you never keep time for silly issues! huh!)
Now I thought of changing the 'Active' choice input control from a CheckBox to a DropDownList, reason being IsActive is a Bit field in the db table and stored procedure and I wanted to give user an option of stating "doesn't matter if Active or Not". The related stored procedure understands this when 'NULL' is passed for the @IsActive parameter.
So, here I am again trying to pass 'NULL' to my sp. This time I created a DropDownList with three ListItems - 'Yes' ("true"), 'No' ("false"), Ignore (""). Now taking full advantage of the recent experience, I set the 'ConvertEmptyStringToNull' to True for the 'IsActive' parameter. Here is the new code -
<asp:SqlDataSource ID="sdsUsers" runat="server"
ConnectionString="<%$ ConnectionStrings:MyDB %>"
SelectCommand="GetUsers"
SelectCommandType = "StoredProcedure"
>
<SelectParameters>
<asp:ControlParameter Name="Name" Type="String" ControlID="txtName" PropertyName="Text" DefaultValue="" ConvertEmptyStringToNull="false" />
<asp:ControlParameter Name="IsActive" Type="String" ControlID="ddlActive" PropertyName="SelectedValue" DefaultValue="" ConvertEmptyStringToNull="true"/>
</SelectParameters>
</asp:SqlDataSource>
Did this and there I am back to the square one! It stopped hitting the db again. I again did few changes to the 'Ignore' list item, tried various values, and at least got yellow error page to understand that I am on the wrong way.
Finally, I reviewed the SqlDataSource's properties again and then encountered this magical property 'CancelSelectOnNullParameter' which looked like the key to my issues. Now I set the property to 'False', the final code looks like -
<asp:SqlDataSource ID="sdsUsers" runat="server"
ConnectionString="<%$ ConnectionStrings:MyDB %>"
SelectCommand="GetUsers"
SelectCommandType = "StoredProcedure"
CancelSelectOnNullParameter="false"
>
<SelectParameters>
<asp:ControlParameter Name="Name" Type="String" ControlID="txtName" PropertyName="Text" DefaultValue="" ConvertEmptyStringToNull="false" />
<asp:ControlParameter Name="IsActive" Type="String" ControlID="ddlActive" PropertyName="SelectedValue" DefaultValue="" ConvertEmptyStringToNull="true"/>
</SelectParameters>
</asp:SqlDataSource>
Execute.... Bingo! it Worked!
Moral of the story - use 'CancelSelectOnNullParameter' to control if you want to pass null parameter or not. Also use 'ConvertEmptyStringToNull' to convert an empty string (from the bound control) to 'NULL' or to just pass '' (empty string).
Subscribe