CODEWITHSUNDEEP
c#asp.netsql-server-2008
Mohemmad K
Mohemmad K

Reputation: 839

SQL Server Date Conversion from String

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

Upvotes: 1

Views: 1399

Answers (3)

syed mohsin
syed mohsin

Reputation: 2938

you have to do multiple changes

  1. Convert your date as DateTime
  2. Pass value as parameter to avoid SQL injection
  3. 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);
    }

}

Upvotes: 0

user2031802
user2031802

Reputation: 744

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

Upvotes: 0

Daniel Kelley
Daniel Kelley

Reputation: 7747

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.

Upvotes: 4

Related Questions