CODEWITHSUNDEEP
c#sql-server
rofans91
rofans91

Reputation: 3010

Why does this SqlCommand parameter produce SQLDateTime overflow error?

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?

Upvotes: 0

Views: 1651

Answers (2)

RickNZ
RickNZ

Reputation: 18654

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.

Upvotes: 1

Quick Joe Smith
Quick Joe Smith

Reputation: 8222

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.

Upvotes: 2

Related Questions