Error

[Saima_Khan]

Hello !

I'm trying to edit and update and cancel in datagrid. Edit and cancel commands are working properly but at UPDATE i m getting this error.(Incorrect syntax near '12').
Please help me. my code is here..

protected void DataGrid1_UpdateCommand(object source, DataGridCommandEventArgs e)
        {

            num = Convert.ToInt16(e.Item.Cells[1].Text.ToString());
            TextBox t1, t2, t3,t4,t5,t6;
            t1 = (TextBox)e.Item.Cells[1].Controls[0];
            t2 = (TextBox)e.Item.Cells[2].Controls[0];
            t3 = (TextBox)e.Item.Cells[3].Controls[0];
            t4 = (TextBox)e.Item.Cells[4].Controls[0];
            t5 = (TextBox)e.Item.Cells[5].Controls[0];
            t6 = (TextBox)e.Item.Cells[6].Controls[0];

 cmd = new SqlCommand(" update History set [History ID]=" + t1.Text + ", [User ID]=" + t2.Text + ", [Project ID]= " + t3.Text + ", [Project Start Date]=" + t4.Text + ",[Task Name]=" + t5.Text + ",[Completion Date]=" + t6.Text + " where [History ID]="+num, con);
            con.Open();
            cmd.ExecuteNonQuery();
            adpt = new SqlDataAdapter(cmd);
            adpt.Fill(ds);
            DataGrid1.EditItemIndex = -1;
            DataGrid1.DataSource = ds;
            DataGrid1.DataBind();
        }

Error is at
cmd.ExecuteNonQuery();

please i m waiting .

[james6754]

Your command is wrong. Recheck it

[MeSampath]

Hi,

From looking into the update query, I believe that your table is having varchar, datetime and numeric fields. So in the sql you should enclose the varchar and datetime fields with single quote("'"). Make the changes and it should work.

Thank you,

[Saima_Khan]

I did it, but again got the same error.

Hi,

From looking into the update query, I believe that your table is having varchar, datetime and numeric fields. So in the sql you should enclose the varchar and datetime fields with single quote("'"). Make the changes and it should work.

Thank you,

[MeSampath]

Ok then post the new modified code.

Thank you,

I did it, but again got the same error.

[thines01]

[Cat is out of the bag]
Did you wrap the value for [Task Name] in single quotes?
Also, what is your data type of [Completion Date]?
Is it a string (varchar)? Is it a DateTime?

[Saima_Khan]

[Cat is out of the bag]
Did you wrap the value for [Task Name] in single quotes?
Also, what is your data type of [Completion Date]?
Is it a string (varchar)? Is it a DateTime?

Data type of Completon date is datetime. and i didn't enclose task name in single qutes.

[thines01]

So, is it fixed?

[thines01]

How are your dates formatted in the text boxes?

If they are something like 11/14/2011, you would need to convert that to a DateTime with something like:

private static DateTime ParseDate(string strDate)
      {
         string[] arr_strDate = strDate.Split('/');
         return new DateTime(
            int.Parse(arr_strDate[2]),
            int.Parse(arr_strDate[0]),
            int.Parse(arr_strDate[1]));
      }

...but that really depends on how the date is formatted.
And also, if you are building a string for the SQL, you will need to adhere to a specific format for DateTime encoding. You could also use a Command Parameter that is more lenient.

[kvprajapati]

Never use hard-coded sql string. Use the parameterized query.

cmd = new SqlCommand("update History set [History ID]=@historyid,[User ID]=@userid, [Project ID]=@projectid, [Project Start Date]=@startdate,[Task Name]=@taskname,[Completion Date]=@compdate  where [History ID]= @hid", con);

cmd.Parameters.AddWithValue("@historyid",t1.Text);
con.Open();
cmd.ExecuteNonQuery();
          
....

[thines01]

Never use hard-coded sql string. Use the parameterized query.

I completely agree.
If the query contains a parameter (even if it's JUST for the DateTime), the field can be inserted without data conversion.

For the current example, however, I believe one problem is the format of the date.
I was playing around with a hardcoded SQL statement and found that if the date is perfectly formatted as a string, it can be inserted.

This is not the recommended version, but it works.

  private static void UpdateHasDate()
  {
     try
     {
        string strDateTime = CDB_DateTime.GetDbTime();
        string strSQL = "insert into LISTER.LISTER.has_date (MY_DATE, COMMENT) VALUES({ts '" + strDateTime + "'}, 'no comment')";
        using (SqlConnection conn = new SqlConnection(CDB_SqlLister.csb.ToString()))
        {
           conn.Open();
           new SqlCommand(strSQL, conn).ExecuteNonQuery();
           conn.Close();
        }
     }
     catch (Exception exc)
     {
        Console.WriteLine("Exception: " + exc.Message);
     }
  }

(Module for DB Date)

using System;

namespace DB_DateTime
{
   public class CDB_DateTime
   {
      public static string GetDbTime()
      {
         return GetDbTime(DateTime.Now);
      }

      public static string GetDbTime(DateTime dt)
      {
         return string.Format("{0:G}-{1:G}-{2:G} {3:G}:{4:G}:{5:G}",
            dt.Year.ToString().PadLeft(4, '0'),
            dt.Month.ToString().PadLeft(2, '0'),
            dt.Day.ToString().PadLeft(2, '0'),
            dt.Hour.ToString().PadLeft(2, '0'),
            dt.Minute.ToString().PadLeft(2, '0'),
            dt.Second.ToString().PadLeft(2, '0')
            );
      }
   }
}