Inserting into DB showing Exception

Question

I am inserting some values into db. In DB field received_date is type of datetime datatype I am using following code for inserting But it is showing some exception, I am unable to figure it out.

Exception:

The conversion of a varchar data type to a datetime data type resulted in an out-of-range value.

SqlConnection con = new SqlConnection("data source=ATLBLRDP-19\\PRIME;database=arp;uid=sa;pwd=****;");
            con.Open();

            SqlCommand cmd_r = new SqlCommand();
            cmd_r.Connection = con;
            cmd_r.CommandType = CommandType.Text;
            cmd_r.CommandText = "insert into raw_mails(received_date,sender,receiver,subject,body,has_parsed,has_attachments,created_by,created_on,mail_type) Values(@received_date,@sender,@receiver,@subject,@body,@has_parsed,@has_attachments,'" + DateTime.Now + "','" + DateTime.Now + "',@mail_type)";
          cmd_r.Parameters.Add("@received_date", em.DateTimeReceived);
            cmd_r.Parameters.Add("@sender", em.From);
            cmd_r.Parameters.Add("@receiver",  em.Receiver);
            cmd_r.Parameters.Add("@subject", em.Subject);
            cmd_r.Parameters.Add("@body", em.Body);
            cmd_r.Parameters.Add("@has_parsed", 1);
            cmd_r.Parameters.Add("@has_attachments", em.HasAttachments);
            cmd_r.Parameters.Add("@mail_type", 4);
            cmd_r.ExecuteNonQuery();
            con.Close();

I checked with every query (parameters.add()) all are working but if I try to insert received_date then only it shows exception . Here em is type of EmailMessage.

And I am using sql server 2012 for DB purpose.

What is the .Net type of DateTimeReceived and when you step over (in debug mode) what was the value? — codingbiz
– codingbiz, Commented Dec 13, 2013 at 13:59
you used datetime.now for both created_by and created_on .. check it to .. — Moumit
– Moumit, Commented Dec 13, 2013 at 14:03

Dan Puzey · Accepted Answer · 2013-12-13 14:08:14Z

You don't give much to go on, but it is clear that you are setting a date value on your Sql server using a string.

There are definitely two and potentially three places you do this, depending on the type of em.DateTimeReceived. When you build your CommandText you also insert DateTime.Now twice, implicitly calling .ToString() for the conversion.

However, calling .ToString() will use your system's locale. For example, I am in the UK, so today's date (December 13) is written out as "13/12/2013 14:02:08". If I assign this string to a sql datetime it will fail, because my Sql server is using it's default US locale - so it reads 13 as the month and 12 as the day, and throws exactly the error you've seen.

So in order to fix this, you need to either:

output the dates as strings using an explicit format that matches your Sql server's collation (using DateTime.Now.ToString("MM/dd/yyyy hh:mm:ss") or similar)
ensure that all of the dates passed in are actual DateTime variables and not strings, allowing the SqlCommand to ensure formatting is correct

S Koppenol · Accepted Answer · 2013-12-13 13:59:29Z

The valid range for the datetime data type is 1753-01-01 through 9999-12-31 (Date and Time Data Types and Functions (technet))

If the em.DateTimeReceived property is smaller than 1753-01-01, you will get this error. Depending on data conversion from .NET to SQL, this might also be the case when DateTimeReceived is null.

Ensure that your property value is always greater than 1753-01-01 or use the datetime2 data type which has a range starting at 0001-01-01.

on a side note: Is there a specific reason you are still using the legacy datetime data type? Microsoft recommends "Use the time, date, datetime2 and datetimeoffset data types for new work. These types align with the SQL Standard. They are more portable. time, datetime2 and datetimeoffset provide more seconds precision. datetimeoffset provides time zone support for globally deployed applications." (msdn)

Akeel · Accepted Answer · 2013-12-13 14:04:48Z

0

Either change the DateTime.Now to DateTime.Now.ToString("yyyy/MM/dd HH:mm") or pass them as parameters, similar to your other values.

answered Dec 13, 2013 at 14:04

Akeel

1214 bronze badges

Collectives™ on Stack Overflow

Inserting into DB showing Exception

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related