CODEWITHSUNDEEP
c#asp.net
Mohan
Mohan

Reputation: 969

DateTime Conversion Error asp.net with sql server

<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

Upvotes: 0

Views: 1494

Answers (5)

Frederik Gheysels
Frederik Gheysels

Reputation: 56964

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

Upvotes: 2

nik0lai
nik0lai

Reputation: 2655

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 " "?

Upvotes: 0

Denis Ivin
Denis Ivin

Reputation: 5644

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

Upvotes: 1

decyclone
decyclone

Reputation: 30840

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

Upvotes: 2

x2.
x2.

Reputation: 9668

mm-dd-yyyyThh:mm:ss

Upvotes: 0

Related Questions