Error on saving datetime to MS SQL using C# asp.net

Question

I am getting an issue with saving data from a datepicker to my ms sql database. My column in MSSQL is set to [datetime]. I have this code but I'm getting error in saving because it's unable to convert date/time to correct format. can you help me. Thank you.

The Error is: Conversion failed when converting date and/or time from character string.

protected void RadButton1_Click(object sender, EventArgs e)
{
            string CS = ConfigurationManager.ConnectionStrings["MyConnection"].ConnectionString;
            using (SqlConnection con = new SqlConnection(CS))
            {

                string str = "insert into Leave_Application values(@Badge_Number, @Name, @Section, @Position, @Date_Hired, @Leave_Type, @Date_From, @Date_To, @Time_From, @Time_To, @Reason, '0', getdate(), 'NULL', ' ' )";
                con.Open();
                cmd = new SqlCommand(str, con);

                cmd.Parameters.AddWithValue("@Badge_Number", lblBadgeNo.Text);
                cmd.Parameters.AddWithValue("@Name", lblName.Text);
                cmd.Parameters.AddWithValue("@Section", lblSect.Text);
                cmd.Parameters.AddWithValue("@Position", lblPos.Text);
                cmd.Parameters.AddWithValue("@Date_Hired", lblDateEmp.Text);
                cmd.Parameters.AddWithValue("@Leave_Type", RadioButtonList1.SelectedValue);
                cmd.Parameters.AddWithValue("@Date_From", Convert.ToDateTime(RadDatePicker1.SelectedDate));
                cmd.Parameters.AddWithValue("@Date_To", Convert.ToDateTime(RadDatePicker2.SelectedDate));
                cmd.Parameters.AddWithValue("@Time_From", Convert.ToDateTime(RadTimePicker1.SelectedDate));
                cmd.Parameters.AddWithValue("@Time_To",  Convert.ToDateTime(RadTimePicker1.SelectedDate);
                cmd.Parameters.AddWithValue("@Reason", txtReason.Text);

                cmd.ExecuteNonQuery();
                con.Close();

 }
}

You should debug through your code and see if the SelectedDate values are not null and matching the expected format. — Alex Filipovici
– Alex Filipovici, Commented Jul 4, 2013 at 11:38
As a side remark, it would be better to name the fields in your insert query. if you add or remove a field to your table, your current query would be broken. — Laurent S.
– Laurent S., Commented Jul 4, 2013 at 11:38
Maybe it's because of the Time_From and Time_To? Or are they in [datetime] format also? — Ben Gulapa
– Ben Gulapa, Commented Jul 4, 2013 at 11:40

शेखर · Accepted Answer · 2013-07-04 11:38:53Z

1

The reason could be the format of date in mysql and C# are different.
You will need the same format for sending values to the function.

You can use the following format

DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss");

DateTime.Now.ToString("yyyyMMddHHmmss");

In your case

Convert.ToDateTime(RadDatePicker1.SelectedDate).ToString("yyyy-MM-dd HH:mm:ss");

answered Jul 4, 2013 at 11:38

शेखर

17.6k14 gold badges64 silver badges123 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Prasad · Accepted Answer · 2013-07-04 11:41:44Z

0

First you remove the convert.todatetime then check. Because in sql its already datetime. so here no need of conversion.

answered Jul 4, 2013 at 11:41

Prasad

2203 silver badges9 bronze badges

13 Comments

jim tollan Over a year ago

i think he's converting his input string, not the sql server column

Prasad Over a year ago

RadDatePicker1.SelectedDate means its datetime only. then why he is again converting..

Kim martin Rocero Over a year ago

Prasad thank you but still I'm getting the same error with this cmd.Parameters.AddWithValue("@Date_From", RadDatePicker1.SelectedDate);

Prasad Over a year ago

use this code Convert.ToDateTime(RadDatePicker1.SelectedDate).ToString("yyyy-MM-dd HH:mm:ss");

Kim martin Rocero Over a year ago

that one seems to be working. how about the RADTIMEPICKER? what's the conversion since I'm getting error on that one. Thanks.

|

jim tollan · Accepted Answer · 2013-07-04 11:50:25Z

0

My guess is that you are passing the datetime in the incorrect locale format. It may be worth debugging the code to see what sqlserver is expecting vs what you are supplying. Typically, I have found that this is usually a case of the day and month portions of the passed date being the alternate format.

You can also try a ToString() format on the input, putting it into the expected culture/locale that you expect.

you can also try it from the client end of things:

<telerik:RadDatePicker ID="RadDatePicker1" DateInput-DateFormat="MM/dd/yyyy">
</telerik:RadDatePicker>

(or whatever date format tickles your fancy)

edited Jul 4, 2013 at 11:50

answered Jul 4, 2013 at 11:40

jim tollan

22.5k4 gold badges53 silver badges64 bronze badges

2 Comments

Kim martin Rocero Over a year ago

i am getting this error --- Conversion failed when converting date and/or time from character string.

jim tollan Over a year ago

see prasads answer, that may be of help

geekonweb · Accepted Answer · 2013-07-04 12:05:14Z

0

One solution could be to pass mysql datetime format as well

i.e

cmd.Parameters.AddWithValue("@Time_From",
"date_format(" +  (RadTimePicker1.SelectedDate.ToString("yyyy-MM-dd HH:mm:ss") 
+ ",'%Y-%m-%d %h:%i:%s')" ));

answered Jul 4, 2013 at 12:05

geekonweb

3944 silver badges14 bronze badges

Comments

Access Denied · Accepted Answer · 2013-07-04 13:35:06Z

0

I Think you should add the column names too in insert query, because if the sequence is not correct then this may happen that you are passing the wrong value to date time column.

answered Jul 4, 2013 at 13:35

Access Denied

9061 gold badge13 silver badges25 bronze badges

Comments

Kosala W · Accepted Answer · 2013-07-04 14:30:38Z

0

This must be due to a difference in datetime format in MSSQL server and C#. I have seen this happening in EF also when I try to run applications on different versions of SQL server.

Since you have not mentioned the version of your sql server, I would suggest you to run the SQL server profiler while running the insert command.

Then get the SQL statement from the SQL server profiler and compare the dateformat in your SQL query against your SQL server dateformat.

I am sure you will be able to find the solution after that.

answered Jul 4, 2013 at 14:30

Kosala W

2,1511 gold badge15 silver badges20 bronze badges

Collectives™ on Stack Overflow

Error on saving datetime to MS SQL using C# asp.net

6 Answers 6

Comments

13 Comments

2 Comments

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

6 Answers 6

Comments

13 Comments

2 Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related