Inserting into DB showing Exception

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.

Answers


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

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)


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


Need Your Help

how to loop the data retrieve from mysql using codeigniter

php mysql codeigniter

I have a problem wherein i will retrieve several datas in my sql, for example i have 9 datas but my problem is that inside my foreach the data that will be process and the foreach is placed in my

Dynamic programming SPOJ problem SCUBADIV

c algorithm dynamic-programming

I am trying to solve this problem from SPOJ, it's a dynamic programming problem, but I'm having trouble visualizing the recursive step. I believe it's similar to a knapsack, but here there are two