CODEWITHSUNDEEP
c#asp.netsql-server
Kim martin Rocero
Kim martin Rocero

Reputation: 131

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

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();

 }
}

Upvotes: 2

Views: 3677

Answers (6)

Kosala W
Kosala W

Reputation: 2143

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.

Upvotes: 0

Access Denied
Access Denied

Reputation: 906

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.

Upvotes: 0

geekonweb
geekonweb

Reputation: 394

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')" ));

Upvotes: 0

jim tollan
jim tollan

Reputation: 22485

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)

Upvotes: 0

Prasad
Prasad

Reputation: 220

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

Upvotes: 0

शेखर
शेखर

Reputation: 17614

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");

Upvotes: 1

Related Questions