C#- Problem while updating Excel column RSS

• • Reply
  

  anandbpatil

  Member

  45 Points

  61 Posts

  C#- Problem while updating Excel column

  Nov 28, 2012 11:24 AM|LINK

  Hi Guys,

  I have got a excel file which contains a column with values '0032323' (quotes in them) as in:

  ID   TelNo     Comp

  1    '098993'

  2    '0098933'

  3   '8494994'

   

  Now i want to update the Comp column with 1 or 0 based on telno ,since it contains quotes its not getting updated.

  When i update it using ID column , it does.. Problem is with quotes.

  The code :

  public void ExcelUpdate()
          {

           string fileName = @"C:\details.xls";
           string sheet = "Tags_Report";
         
           string excelConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + fileName + ";Extended Properties='Excel 12.0 Xml;IMEX=1;HDR=No'";
           OleDbConnection mycon = new OleDbConnection();
           OleDbCommand cmd = new OleDbCommand();
           try
           {
               mycon.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + fileName + ";Extended Properties='Excel 12.0 Xml;IMEX=0;HDR=Yes'";

               mycon.Open();
               cmd.Connection = mycon;
  //this works…
               cmd.CommandText = "Update [" + sheet + "$] SET [FM Closed] = 1 where [ID] = 2";

  //this doesn’t
  cmd.CommandText = "Update [" + sheet + "$] SET [Comp] = 1 where [tele No] = '''01235516551'''";

               cmd.ExecuteNonQuery();
               mycon.Close();
           }
           catch (Exception e)
           {
               MessageBox.Show(e.Message);
           }

          }

   

  • Moved by bbcompent1 on Nov 28, 2012 11:31 AM
  • Moved by XIII on Nov 28, 2012 02:26 PM
• • Reply
  

  anandbpatil

  Member

  45 Points

  61 Posts

  Re: C#- Problem while updating Excel column

  Nov 29, 2012 05:57 AM|LINK

  No suggestions, no pointers?...

• • Reply
  

  Lannie

  Contributor

  3738 Points

  728 Posts

  Re: C#- Problem while updating Excel column

  Nov 29, 2012 08:52 PM|LINK

  pass the phone number as a string variable to the procedure and that gets rid of the hard coded value

  and all the problems with quotes.

• • Reply
  

  anandbpatil

  Member

  45 Points

  61 Posts

  Re: C#- Problem while updating Excel column

  Nov 30, 2012 06:00 AM|LINK

  have tried that as well , sent it via parameters , but still it doesnt work..

• • Reply
  

  Lannie

  Contributor

  3738 Points

  728 Posts

  Re: C#- Problem while updating Excel column

  Nov 30, 2012 09:59 PM|LINK

  Try IMEX = 1

• • Reply
  

  aarsh

  Participant

  1543 Points

  428 Posts

  Re: C#- Problem while updating Excel column

  Dec 10, 2012 09:18 PM|LINK

  Here is something useful, plenty of executed and readymade examples that you can freely downlaod from the 'zip' links (from skydrive)

  https://wildclick.wordpress.com/codelib/codelib-c/  (this page has a dedicated demo called "Updating Ms-Excel spreadsheets Udating data of Excel sheets using C#")

  https://wildclick.wordpress.com/codelib/codelib-aspnet/

  • Edited by aarsh on Dec 10, 2012 09:18 PM
  • Edited by aarsh on Dec 10, 2012 09:18 PM