current community

your communities

more stack exchange communities

company blog
Stack Exchange Inbox Reputation and Badges
tour help
Stack Overflow is a community of 4.7 million programmers, just like you, helping each other.

Join them; it only takes a minute:

Sign up
Join the Stack Overflow community to:
  1. Ask programming questions
  2. Answer and help your peers
  3. Get recognized for your expertise
up vote 0 down vote favorite

I have these blocks of code:

var cmd = new SqlCommand (); 
cmd.CommandText = @"SELECT * FROM " + TableName + " Where ";
SqlConnection a = new SqlConnection("the setting");
cmd.Connection = a;

These lines below work fine:

cmd.CommandText += strFromTextBox + " LIKE '%" + strUserInput + "%'";
//strFromTextBox & strUserInput is string datatype
//it looks like 'apple', 'ladder', just normal string
SqlDataAdapter adapter = new SqlDataAdapter();
adapter.SelectCommand = cmd;

But these ones produced error:

DateTime dtFrom = DateTime.Parse(dt1).Date;
DateTime dtTo = DateTime.Parse(dt2).Date;
//dt1 & dt2 is originally a string
//they look something like this: 2/1/2012 12:00:00 AM
cmd.Parameters.AddWithValue("@dt1", @dtFrom); 
cmd.Parameters.AddWithValue("@dt2", @dtTo);
cmd.CommandText+= parameter[i].ToString() + " BETWEEN @dt1 AND @dt2";
//parameter[i] refers to the column name

The error it produced:

System.Data.SqlTypes.SqlTypeException: SqlDateTime overflow.

Both blocks are processed through:

DataTable Table = new DataTable();
adapter.FillSchema(Table, SchemaType.Source);
adapter.Fill(Table);

The final query output (found during character)

SELECT * FROM linkDb Where CreateDt BETWEEN @dt1 AND @dt2

Can someone advises whats wrong with those lines of code?

share|improve this question
    
obviously not, pls do check the blocks of code i provide.. i put one commented line to show sample of the inputs.. – rofansmanao Jan 6 '12 at 6:23
    
I have done so. TQ. In fact I have other friend helping me to check the code too.. It's not from the input.. – rofansmanao Jan 6 '12 at 6:29
    
Can you tell me "parameter[i]" what is this? – Amar Palsapure Jan 6 '12 at 6:33
    
It refers to column name, i have edited it.. thank you for spotting it.. – rofansmanao Jan 6 '12 at 6:41
    
@marc_s I edited my question, can't put here due to the ampersand character not allowed to typed more than 1 in comment.. – rofansmanao Jan 6 '12 at 7:21
up vote 2 down vote accepted

The SQL Server DATETIME datatype can only represent dates from 00:00:00 1/1/1753 to 23:59:59 31/12/9999. My guess is that one of the date strings is either being given a date outside this range, or it is being parsed differently than you think.

Also, building SQL strings like that is hugely prone to SQL injection. You should consider abandoning this practice very soon. Like, now.

share|improve this answer
    
I know. But please do check my question throughly I did provide example of the input... which is lying between the normal timeline.. //they look something like this: 2/1/2012 12:00:00 AM the error looks contrasting with my input that's why... – rofansmanao Jan 6 '12 at 6:25
1  
As marc_s already said: check your assumptions. Something I've learned is that when reality is clashing with your expectations, reality is usually correct. – Quick Joe Smith Jan 6 '12 at 6:28
1  
have done so.. tq.. I wont ask it here if the matters are that simple for sure.. – rofansmanao Jan 6 '12 at 6:30
    
Unless there is a bug in SQL server or .net base class library (which is highly impossible as being used by millions), error is in the input, date string is null or empty resulting in DateTime.MinValue which is outside SQL range. – Akash Kava Jan 7 '12 at 8:29
up vote 1 down vote

Instead of this:

cmd.Parameters.AddWithValue("@dt1", @dtFrom); 
cmd.Parameters.AddWithValue("@dt2", @dtTo);

Try this:

SqlParameterCollection p = cmd.Parameters;
p.Add("@dt1", SqlDbType.DateTime).Value = dtFrom;
p.Add("@dt2", SqlDbType.DateTime).Value = dtTo;

I would also set the value of CommandText before setting the parameters.

If that fails as well, you should use a debugger to verify the values contained in dtFrom and dtTo.

BTW, I know it wasn't part of your question, but your posted code is susceptible to SQL injection. You should also wrap your SqlConnection and SqlCommand objects with using statements.

share|improve this answer
    
+1 for SQL Injection alert – Simon Jan 7 '12 at 9:58

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.