none
updating value in excel with carriage return RRS feed

  • Question

  • hi while updating excel file using

     

    OleDbCommand comm = new OleDbCommand("Update [sheet1$] set [Status]=('" + dt1.Rows[i][1] + "'), [Remarks]=('" + dt1.Rows[i][2] + "'), [Time]=('" + dt1.Rows[i][3] + "'), [Date]=('" + dt1.Rows[i][4] + "') where [Work Order No]=('" + dt1.Rows[i][0] + "') and ([Status] is null or [Status]='Hold')", conn);

    comm.ExecuteNonQuery();

    if there is carriage return (') in remarks field then it is showing me error how to update excel with values and carriage return.

    

    Monday, May 23, 2011 12:21 PM

Answers

  • Use Command Parameters. If you don't use Command Parameters then any special characters (e.g. quote mark, comma, etc.) in the SQL statement will need to be escaped.

    OleDbCommand comm = new OleDbCommand("Update [sheet1$] set [Status]=?, [Remarks]=?, [Time]=?, [Date]=? where [Work Order No]=? and ([Status] is null or [Status]='Hold')", conn); 
    
    comm.Parameters.AddWithValue("StatusParam", dt1.Rows[i][1]);
    comm.Parameters.AddWithValue("RemarksParam", dt1.Rows[i][2]);
    comm.Parameters.AddWithValue("DateParam", dt1.Rows[i][3]);
    '...
    Monday, May 23, 2011 6:02 PM

All replies

  • Use Command Parameters. If you don't use Command Parameters then any special characters (e.g. quote mark, comma, etc.) in the SQL statement will need to be escaped.

    OleDbCommand comm = new OleDbCommand("Update [sheet1$] set [Status]=?, [Remarks]=?, [Time]=?, [Date]=? where [Work Order No]=? and ([Status] is null or [Status]='Hold')", conn); 
    
    comm.Parameters.AddWithValue("StatusParam", dt1.Rows[i][1]);
    comm.Parameters.AddWithValue("RemarksParam", dt1.Rows[i][2]);
    comm.Parameters.AddWithValue("DateParam", dt1.Rows[i][3]);
    '...
    Monday, May 23, 2011 6:02 PM
  • Thanks Paul............ Cool
    Tuesday, May 24, 2011 4:36 AM