Prev: Please explain Html.CheckBoxFor() rendering
Next: Output a formula
From: DavidC on 24 May 2010 14:15
We have an aspx page with a GridView tied to an SQL Server stored procedure.
When the GridView was setup we did a "test query" and all expected records
displayed. However, when we open the page we get no records. Below are the
parameters on the SqlDataSource control. The 2 dates are defaulted to NULL
in the stored proc so that only dates with NULL are displayed. I also ran
the stored proc from SQL server and it ran fine when I checked the send null
values for the dates. Any ideas where I should look? Thanks.

asp:QueryStringParameter Name="Branch" QueryStringField="brn" Type="Int16"
asp:ControlParameter ControlID="txtCheckDateStart" DbType="Date"
Name="CheckDateStart" PropertyName="Text"
asp:ControlParameter ControlID="txtCheckDateEnd" DbType="Date"
Name="CheckDateEnd" PropertyName="Text"



--
David
From: DavidC on 24 May 2010 14:55
"DavidC" wrote:

> We have an aspx page with a GridView tied to an SQL Server stored procedure.
> When the GridView was setup we did a "test query" and all expected records
> displayed. However, when we open the page we get no records. Below are the
> parameters on the SqlDataSource control. The 2 dates are defaulted to NULL
> in the stored proc so that only dates with NULL are displayed. I also ran
> the stored proc from SQL server and it ran fine when I checked the send null
> values for the dates. Any ideas where I should look? Thanks.
>
> asp:QueryStringParameter Name="Branch" QueryStringField="brn" Type="Int16"
> asp:ControlParameter ControlID="txtCheckDateStart" DbType="Date"
> Name="CheckDateStart" PropertyName="Text"
> asp:ControlParameter ControlID="txtCheckDateEnd" DbType="Date"
> Name="CheckDateEnd" PropertyName="Text"
>
>
>
> --
> David

I noticed one more thing that might help. When I removed the 2 date
parameters the GridView displayed fine when opening the page. So, how do I
get date fields to default to null as the stored proc only needs them if they
are entered? Thanks.

David
From: Alexey Smirnov on 25 May 2010 03:19
On May 24, 8:55 pm, DavidC <dlch...(a)lifetimeinc.com> wrote:
> "DavidC" wrote:
> > We have an aspx page with a GridView tied to an SQL Server stored procedure.  
> > When the GridView was setup we did a "test query" and all expected records
> > displayed.  However, when we open the page we get no records.  Below are the
> > parameters on the SqlDataSource control.  The 2 dates are defaulted to NULL
> > in the stored proc so that only dates with NULL are displayed.  I also ran
> > the stored proc from SQL server and it ran fine when I checked the send null
> > values for the dates.  Any ideas where I should look?  Thanks.
>
> > asp:QueryStringParameter Name="Branch" QueryStringField="brn" Type="Int16"
> > asp:ControlParameter ControlID="txtCheckDateStart" DbType="Date"
> > Name="CheckDateStart" PropertyName="Text"
> > asp:ControlParameter ControlID="txtCheckDateEnd" DbType="Date"
> > Name="CheckDateEnd" PropertyName="Text"
>
> > --
> > David
>
> I noticed one more thing that might help.  When I removed the 2 date
> parameters the GridView displayed fine when opening the page.  So, how do I
> get date fields to default to null as the stored proc only needs them if they
> are entered?  Thanks.
>
> David

Hi David,

according to Microsoft: "by default the SqlDataSource does not perform
a Select operation if any of the associated parameters to the
SelectCommand are null. To allow the select operation to execute even
when a null parameter is passed, you can set the
CancelSelectOnNullParameter property to false."

That is, try to set CancelSelectOnNullParameter into false, because
the default value is true.

<asp:SqlDataSource runat="server"...
CancelSelectOnNullParameter="False">

http://msdn.microsoft.com/en-us/library/system.web.ui.webcontrols.sqldatasource.cancelselectonnullparameter.aspx

Hope this helps
 | 
Pages: 1
Prev: Please explain Html.CheckBoxFor() rendering
Next: Output a formula