I inherited a system that was started in Access and moved to SQL 2000. The business has grown and we are trying to replace our older systems with ASP.NET and Server 2005.
Currently, we are trying to make a new asp.net page for searching the database for records with matching dates or date ranges. There are several types of dates to search, so they are all optional. Set to default as null in the proc. For each date there is an operator field, such as equal or greater, etc. The proc only looks at the date if the operator is set to EQ" or "IN" and ignores the date if operator set to "NO"
The proc works fine when running under Management Studio, but fails coming through a SQLDataSource to a gridview. It works with integer and string filters, but fails when entering the same date ('07/20/2007') that works in the testing tool. All dates are actually stored as datetime, and they are set as DateTime Control Parameters in the SQLDataSource.
<asp:SqlDataSourceID="SqlDataSource1"runat="server"ConnectionString="<%$ ConnectionStrings:someConnectionString %>"
SelectCommand="spTESTSearch"SelectCommandType="StoredProcedure">
<SelectParameters>
<asp:ParameterDefaultValue="M"Name="TypeCode"Type="String"/>
<asp:ParameterDefaultValue="EQ"Name="FirstPubOp"Type="String"/>
<asp:ControlParameterControlID="FirstPubDateTextBox"DefaultValue=""Name="FirstPubDate"
PropertyName="Text"Type="DateTime"/>
<asp:ParameterDefaultValue="C"Name="UserName"Type="String"/>
<asp:ParameterDefaultValue="NO"Name="SearchTextOp"Type="String"/>
<asp:ParameterName="SearchText"Type="String"/>
</SelectParameters>
</asp:SqlDataSource>The dates are selected properly in the testing tool, with code such as :
DateDiff(day, FirstPubDate, @.FirstPubDate)= 0
I think my problem is based on option settings for the databases themselves. The old database was set to Ansii Nulls and Quoted Identiers to OFF, and the new ones were defaulted to them being ON. I noticed that the tool also, sets those options on when creating new stored procedures.
Would this difference be causing the dates to be quoted and viewed as objects rather than strings? What are the dangers in changing those options on the database that still gets uploads from the old SQL 2000 database and some Mac-based systems?
I welcome any suggestions on how to get my new stuff running while not breaking my old production systems.
Thanks for the assist!
The options you mentioned should have no relevance to your current issue. Quoted identifiers is referring to whether a statement like:
SELECT "my_column" or SELECT [my_column] is the correct way to specify column/table names that have characters in them that could cause confusion, or are reserved words.
The ANSI NULLS parameter refers to a number of things like if the boolean operation (NULL=NULL) should return true, or NULL.
BTW, the default values on the stored procedure (as specified in the stored procedure) aren't used. They only come into play when you call a stored procedure without specifying a value for that parameter. The parameters you have defined in the SqlDatasource will always pass a value.
Personally, I would first hook up an event to the SqlDatasource_Selecting event. By checking the e.Command.Parameters collection in that event, you can see what it about to be passed to the stored procedure. Verify that passing the values (as they were in the selecting event) works in Management Studio.
If that does not lead you to the problem, then run Sql Profiler, and compare the difference between the queries that get submitted via the web application, and the query submitted by management studio.
