heyday2004
asked on
Minimum datetime value in ASP.NET and Stored procedure question.
In one ASP.NET page, I have to use below value for the min value and max value for searching the results. The reason is the different min value and max value in ASP.NET and in SQL Server. I know it's stupid, but when I used: string MinValue = "12/12/1800"; string MaxValue = "12/12/2050"
in both asp page and the stored procedure, the search function works. But what is the better way (for sure there is) to replace the '12/12/1800', etc. in both asp page and stored procedure and will for sure WORK? Thanks.
#1: search.aspx:
public static DataTable GetSearchResults(string StartDateFrom,string StartDateTo,)
string MinValue = "12/12/1800";
string MaxValue = "12/12/2050";
comm.CommandText = "GetSearchResults";
if (StartDateFrom == "" & StartDateTo == "")
{
StartDateFrom = Convert.ToDateTime(MinValu e).ToStrin g();
StartDateTo = Convert.ToDateTime(MaxValu e).ToStrin g();
}
param = comm.CreateParameter();
param.ParameterName = "@StartDateFrom";
param.Value = StartDateFrom;
param.DbType = DbType.DateTime;
comm.Parameters.Add(param) ;
...
return GenericDataAccess.ExecuteS electComma nd(comm);
#2 Stored procedure detail (called in #1):
...
CREATE PROCEDURE GetSearchResultsFiltered
@StartDateFrom datetime,
@StartDateTo datetime
...
AS
DECLARE @MinValue DATETIME
DECLARE @MaxValue DATETIME
SET @MinValue = '12/12/1800'
SET @MaxValue = '12/12/2050'
IF (@StartDateFrom = @MinValue and @StartDateTo = @MaxValue)
BEGIN
SET @StartDateFrom = null
SET @StartDateTo = null
END
select * from ...
where ...
AND ((@StartDateFrom is null) OR (StartDate > @StartDateFrom - 1))
AND ((@StartDateTo is null) OR (StartDate < @StartDateTo + 1))
...
in both asp page and the stored procedure, the search function works. But what is the better way (for sure there is) to replace the '12/12/1800', etc. in both asp page and stored procedure and will for sure WORK? Thanks.
#1: search.aspx:
public static DataTable GetSearchResults(string StartDateFrom,string StartDateTo,)
string MinValue = "12/12/1800";
string MaxValue = "12/12/2050";
comm.CommandText = "GetSearchResults";
if (StartDateFrom == "" & StartDateTo == "")
{
StartDateFrom = Convert.ToDateTime(MinValu
StartDateTo = Convert.ToDateTime(MaxValu
}
param = comm.CreateParameter();
param.ParameterName = "@StartDateFrom";
param.Value = StartDateFrom;
param.DbType = DbType.DateTime;
comm.Parameters.Add(param)
...
return GenericDataAccess.ExecuteS
#2 Stored procedure detail (called in #1):
...
CREATE PROCEDURE GetSearchResultsFiltered
@StartDateFrom datetime,
@StartDateTo datetime
...
AS
DECLARE @MinValue DATETIME
DECLARE @MaxValue DATETIME
SET @MinValue = '12/12/1800'
SET @MaxValue = '12/12/2050'
IF (@StartDateFrom = @MinValue and @StartDateTo = @MaxValue)
BEGIN
SET @StartDateFrom = null
SET @StartDateTo = null
END
select * from ...
where ...
AND ((@StartDateFrom is null) OR (StartDate > @StartDateFrom - 1))
AND ((@StartDateTo is null) OR (StartDate < @StartDateTo + 1))
...
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.