DateTime Conversion Error asp.net with sql server

Question

<asp:TextBox ID="txtBox" runat="server">
</asp:TextBox>
<asp:CalendarExtender ID="ce" runat="server" TargetControlID="txtBox" Format="dd-MMM-yyyy">
</asp:CalendarExtender>

using (SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["Connection239"].ToString()))
{
    SqlCommand cmd = new SqlCommand("insert into tbl_testing(dttm) values('"+DateTime.Parse(txtBox.Text)+"')", con);
    con.Open();
    cmd.ExecuteNonQuery();
}

when i execute following error is coming. In which format should I send the date to sql server

The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value. The statement has been terminated. Thanks in advance

Give us some examples of the dates that work and the dates that don't... — nik0lai
– nik0lai, Commented Dec 13, 2010 at 9:41

decyclone · Accepted Answer · 2010-12-13 08:39:52Z

2

Use following code instead:

        SqlCommand cmd = new SqlCommand("insert into tbl_testing(dttm) values(@dttm)", con);
        cmd.Parameters.AddWithValue("@dttm", DateTime.Parse(txtBox.Text));
        con.Open();
        cmd.ExecuteNonQuery();

answered Dec 13, 2010 at 8:39

decyclone

30.9k7 gold badges67 silver badges80 bronze badges

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

Comments

Doctor Jones · Accepted Answer · 2010-12-13 14:06:14Z

2

Use parameters in your query, instead of string concatenation. Not only will your datetime problems be gone, but your query will also not be vulnerable to sql injection.

Next to that, you can also use a DateTimePicker instead of textbox, can't you ?

var command = conn.CreateCommand();
command.CommandText = "insert into tbl_testing(thecol) values(@p_someDate)";
command.Parameters.Add ("@p_someDate", SqlDbType.DateTime).Value = datetimePicker.Value;
command.ExecuteNonQuery();

edited Dec 13, 2010 at 14:06

Doctor Jones

21.8k13 gold badges81 silver badges103 bronze badges

answered Dec 13, 2010 at 8:39

Frederik Gheysels

57.2k12 gold badges103 silver badges156 bronze badges

1 Comment

Mohan Over a year ago

query is executing sometimes. but when i take Dec 31-2010, it gives me error. kindly provide me solution.

Denis Ivin · Accepted Answer · 2010-12-13 08:40:42Z

1

Just pass the value via SqlParameter and NEVER use string concatenation.

answered Dec 13, 2010 at 8:40

Denis Ivin

5,6341 gold badge29 silver badges28 bronze badges

Comments

x2. · Accepted Answer · 2010-12-13 08:39:01Z

0

mm-dd-yyyyThh:mm:ss

answered Dec 13, 2010 at 8:39

x2.

9,6586 gold badges43 silver badges63 bronze badges

Comments

nik0lai · Accepted Answer · 2010-12-13 08:43:54Z

0

Make sure the server (local or otherwise) is using the correct time. For e.g if its British or American. Dates are also wrapped in ' '. It seems you are wrapping it in " "?

answered Dec 13, 2010 at 8:43

nik0lai

2,66524 silver badges37 bronze badges

Collectives™ on Stack Overflow

DateTime Conversion Error asp.net with sql server

5 Answers 5

Comments

1 Comment

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

Comments

1 Comment

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related