SQL Server Date Conversion from String

Question

I need to store the date fetch from the ASP.Net text box into a SQL Server 2008 date column.

I am getting the following exception message.

Conversion failed when converting date and/or time from character string.

My code is:

SqlConnection cn;
SqlCommand cmd;
SqlDataAdapter da;

int n=4;
protected void Page_Load(object sender, EventArgs e)
{
    cn = new SqlConnection(<Connection String>);
}

protected void Button1_Click(object sender, EventArgs e)
 {
        try
      {
          string query = "insert into temp values('" + TextBox1.Text  + "')";
          cn.Open();
          cmd = new SqlCommand(query, cn);
          cmd.ExecuteNonQuery();
          cn.Close();
          Response.Write("Record inserted successfully");
     }
    catch (Exception ex)
    {
         Response.Write(ex.Message);
    }

}

Please help ..

Thanks in advance

Couple of things. 1) Your code is wide open to a SQL injection attack. 2) I guess you are trying to insert into a DateTime field but your logic is passing it as a string. — Daniel Kelley
– Daniel Kelley, Commented Feb 2, 2013 at 11:18
Again, these question are all the same. String concatenation is BAD. stackoverflow.com/questions/14660255/… — Steve
– Steve, Commented Feb 2, 2013 at 11:20

Daniel Kelley · Accepted Answer · 2013-02-02 11:21:09Z

4

Change your code to the following (assuming the column in temp is actually a DateTime):

protected void Button1_Click(object sender, EventArgs e)
{
    try
    {
        string query = "insert into temp values(@Value)";
        cn.Open();
        cmd = new SqlCommand(query, cn);
        cmd.Parameters.AddWithValue("@Value", DateTime.Parse(TextBox1.Text));
        cmd.ExecuteNonQuery();
        cn.Close();
        Response.Write("Record inserted successfully");
 }
catch (Exception ex)
{
     Response.Write(ex.Message);
}

}

You should also change your logic to validate that TextBox1.Text is actually a valid date.

answered Feb 2, 2013 at 11:21

Daniel Kelley

7,7677 gold badges45 silver badges51 bronze badges

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

3 Comments

Daniel Kelley Over a year ago

@user2017483 What value is in TextBox1.Text at the point you click the button? What data type is the column in temp?

Mohemmad K Over a year ago

The data type of column in sql server is Date.. And the value in textbox would be 13-05-1990 (dd-mm-yyyy)

Daniel Kelley Over a year ago

@user2017483 It's odd that DateTime.Parse failed to handle that date format. As you have found from syed's answer Convert.ToDateTime seems to handle that better.

syed mohsin · Accepted Answer · 2013-02-02 11:37:22Z

0

you have to do multiple changes

Convert your date as DateTime
Pass value as parameter to avoid SQL injection
Set the field as DateTime in DB.

Your code would be like that

SqlConnection cn;
SqlCommand cmd;
SqlDataAdapter da;

int n=4;
protected void Page_Load(object sender, EventArgs e)
{
cn = new SqlConnection(<Connection String>);
}

protected void Button1_Click(object sender, EventArgs e)
{
    try
    {
      string query = "insert into temp values(@date))";
      cn.Open();
      cmd = new SqlCommand(query, cn);
      cmd.Parameters.AddWithValue("@date",Convert.DateTime(TextBox1.Text));
      cmd.ExecuteNonQuery();
      cn.Close();
      Response.Write("Record inserted successfully");
    }
    catch (Exception ex)
    {
     Response.Write(ex.Message);
    }

}

edited Feb 2, 2013 at 11:37

answered Feb 2, 2013 at 11:25

syed mohsin

2,9382 gold badges27 silver badges48 bronze badges

Comments

user2031802 · Accepted Answer · 2013-02-02 11:22:06Z

0

Do check if the column datatype is set to datetime
Use convert.ToString before inserting into the database

answered Feb 2, 2013 at 11:22

user2031802

7448 silver badges7 bronze badges

Collectives™ on Stack Overflow

SQL Server Date Conversion from String

3 Answers 3

3 Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

3 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related