Tell me more ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.

I use stored procedure to get paged list and this is my method:

using(SqlConnection conn = new SqlConnection(_connectionString)) {
   using(SqlCommand cmd = new SqlCommand("[GetPagedSP]", conn)) {
      cmd.CommandType = System.Data.CommandType.StoredProcedure;

       //Passing Parameters 
       **Update**
       SqlParameter spCreationDate = new SqlParameter("@CreationDate_9", CreationDate);
       spCreationDate.IsNullable = true;
       cmd.Parameters.Add(spCreationDate);

       // ........
       //Finished Passing Parameters
       conn.Open();

       SqlDataReader dr = cmd.ExecuteReader();

       while(dr.Read()) {
          //Get Values
       }
       conn.Close();
   }
}

And this is my stored procedure command:

CREATE TABLE #PagingTemp  (
[RowId]           [bigint]    IDENTITY(1,1)    NOT NULL,
[RecordId]        [bigint]  
);

INSERT INTO [#PagingTemp] ([RecordId])
SELECT  [CAR].[Id]      
FROM [Article] AS [CAR] ;

SELECT [CAR].*
FROM [Collections].[Article] AS [CAR]
INNER JOIN [#PagingTemp] AS [PT] ON [CAR].[Id] = [PT].[RecordId]
WHERE [PT].[RowId] BETWEEN 1 AND 50;

When I run the query in SQL every thing is fine, But in .NET I have an exception on this line:

SqlDataReader dr = cmd.ExecuteReader();

and the exception is:

System.Data.SqlTypes.SqlTypeException was unhandled by user code
Message=SqlDateTime overflow. Must be between 1/1/1753 12:00:00 AM and 12/31/9999 11:59:59 PM.
Source=System.Data

Update

The example of run Query in SQL: enter image description here

That's so weird I don't understand what happened?

I don't have any datetime value that be bigger than 12/31/9999 or less than 1/1/1753

I just have some Nullable datetime values with null value in database.

what do you think? where is the problem?

share|improve this question
 
Check that your date/time column is returning a value and not null, otherwise you need to set your DateTime to DateTime?. –  Gavin Jul 30 '12 at 7:42
 
@Gavin As I said I have some nullable columns that returns null, but in object side my types are DateTime?, also before any mapping an exception throw in this line: SqlDataReader dr = cmd.ExecuteReader(); –  Saeid Jul 30 '12 at 7:50
 
Out of interest, if you do have any DateTime values, can you post an example of one? –  Gavin Jul 30 '12 at 7:53
1  
ah... Just out of interest, are you passing any date/time objects as parameters into your query? i.e. before con.Open(); If so, debug and make sure the date is not null, and is between the above dates –  Gavin Jul 30 '12 at 8:03
1  
CreationDate is probably zero, not null but zero. Could you please verify that? –  Nikola Markovinović Jul 30 '12 at 8:10
show 6 more comments

3 Answers

Assign any valid date to the variable CreationDate before passing it to execute the stored procedure

  //Passing Parameters 

   CreationDate = DateTime.Now or any other date you want if you dont want to pass any date then assign DBNull.Value

   **Update**
   SqlParameter spCreationDate = new SqlParameter("@CreationDate_9", CreationDate);
   spCreationDate.IsNullable = true;
   cmd.Parameters.Add(spCreationDate);

Hope this will solve your problem.

share|improve this answer

IF possible, paste the SP here, it would be better to propose a fix. For now, you may consider following possibilities to try to fix it:

If select is causing issue then consider replacing NULL date with min date as following:

SELECT IsNULL(CreationDate,'1/1/1753') CreationDate, IsNULL(LastBidDate,'1/1/1753') LastBidDate from yourtable.

For reference, here is another answered question: error-sqldatetime-overflow-must-be between-1-1-1753-120000-am-and-12-31-999

share|improve this answer
up vote 0 down vote accepted

As Gavin And Nikola Markovinović mentioned in comments in select command when you passed the DateTime type parameter need to check the parameter value is in correct range or not (1/1/1753 to 12/31/9999)? I pass a Nullable DateTime parameter and I thought the value of that is null, also I never use this parameter in the query in this case, so the null value is correct and the use of parameter in command is not important, when I check again I found that the value of parameter is not null and is 1/1/0001. another solution is use DATATIME2 type in SQL that support all range of DateTime which .Net supports.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.