date time is not working in c#

Question

I have a table called Tbl_Sud_Details it has three columns:

Name is varchar(),
Course is varchar(),
DateofAdmission is Datetime(),

When I am inserting a date in the format dd/mm/yyyy it gives the error:

Error:The conversion of a varchar data type to a datetime data type resulted in an out-of-range value. The statement has been terminated

SqlCommand cmd = new SqlCommand("Insert into  Tbl_Sud_Details(Name,Course,DateofAdmission)Values('" + ddlname.SelectedItem.Text + "','" + txtcourse.Text + "','" +Convert.ToDateTime(txtAdddate.Text).ToString("dd/MM/yyyy") + "')", con);
con.Open();
int i = cmd.ExecuteNonQuery();
con.Close();
if (i > 0)
{
    Page.ClientScript.RegisterStartupScript(this.GetType(), "k1", "<script language=javascript> alert(\"data is  save Successfully \"); </script>");
    return;

}

fubo · Answer 1 · 2016-03-11T10:22:18.453

6

"dd/MM/yyyy" is not supported by SQL. try "yyyy-MM-dd" instead. To avoid injection attacks i would use parameters to submit data.

using (MySqlConnection con = new MySqlConnection(connString))
{
    string stm = "Insert into  Tbl_Sud_Details(Name,Course,DateofAdmission)Values(@Name,@Course,@DateofAdmission)";
    using (SqlCommand cmd = new SqlCommand(stm))
    {
        cmd.Parameters.Add("@Name", SqlDbType.VarChar).Value = ddlname.SelectedItem.Text;
        cmd.Parameters.Add("@Course", SqlDbType.VarChar).Value = txtcourse.Text;
        cmd.Parameters.Add("@DateofAdmission", SqlDbType.DateTime).Value = Convert.ToDateTime(txtAdddate.Text);

        con.Open();
        int i = cmd.ExecuteNonQuery();
        con.Close();
    }
}

edited Mar 11 '16 at 10:22

answered Mar 11 '16 at 10:18

fubo

44,811
17
103
137

i want insert with c# code with textbox i was already mention above – Santosh Kumar Bind Mar 11 '16 at 10:21
*"dd/MM/yyyy" is not supported by SQL* - Yes it is, but could return an error if regional settings are for a DATEFORMAT of `MDY` rather than `DMY`. +1 nonetheless because this becomes irrelevant with parameterised queries. – GarethD Mar 11 '16 at 10:27

score 1 · Answer 2 · edited May 23 '17 at 11:48

1

Depending on which database backend you use, you may have to encode dates differently.

For an example in SQL server, see this question: Sql query to insert datetime in SQL Server

edited May 23 '17 at 11:48

Community

1
1

answered Mar 11 '16 at 10:18

Erik

134
10

I am using sql server 2008 R2 all the above is not working provided by you – Santosh Kumar Bind Mar 11 '16 at 10:27

score 0 · Answer 3 · answered Mar 11 '16 at 10:24

SQL Understand this format "yyyy-MM-dd". So your custom datetime format must follow default datetime. When you send default datetime, it then understand by sql. also in ui for client view, you can manage this by formatting it as your need but when sending to database must follow the default format

score 0 · Answer 4 · answered Mar 11 '16 at 10:38

0

SqlCommand cmd = new SqlCommand("Insert into Tbl_Sud_Details(Name,Course,DateofAdmission)Values('" + ddlname.SelectedItem.Text + "','" + txtcourse.Text + "',CONVERT(datetime,'" + txtAdddate.Text + "',103))", con);

answered Mar 11 '16 at 10:38

Santosh Kumar Bind

63
1
12

date time is not working in c#

4 Answers4