Gridview not getting sp records

  • Thread starter Thread starter DavidC
  • Start date Start date
D

DavidC

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"
 
DavidC said:
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"
Click to expand...

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
 
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
Click to expand...

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/lib...qldatasource.cancelselectonnullparameter.aspx

Hope this helps
 
Back
Top