Problem excel C# RSS

• • Reply
  

  tzahi2010

  Member

  266 Points

  287 Posts

  Problem excel C#

  Nov 25, 2011 02:12 PM|LINK

  hello this is my code:

  string conn = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + DBtb.Text + ";Extended Properties=Excel 12.0";
              OleDbConnection oledbConn = new OleDbConnection(conn);
              try
              {
                  oledbConn.Open();
                  OleDbCommand cmd = new OleDbCommand("SELECT O FROM [Sheet4$]", oledbConn);
                  OleDbDataAdapter oleda = new OleDbDataAdapter();
                  oleda.SelectCommand = cmd;
                  cmd.CommandText = "INSERT INTO [Sheet4] (B5, C5, D5) VALUES(1,\"Fake Record\",\"Fake Record\")";
                  cmd.ExecuteNonQuery();

  but the command text doesnt get excuted why is that?

  EDIT...
  by the way i dont get any erros the values just dont show up 

  • Edited by tzahi2010 on Nov 25, 2011 02:12 PM
  • Moved by mbanavige on Nov 25, 2011 05:18 PM
• • Reply
  

  sreejukg

  All-Star

  27495 Points

  4095 Posts

  Re: Problem excel C#

  Nov 25, 2011 02:29 PM|LINK

  retrieve the return value from ExecuteNonQuery and see how many results are inserted

  e.g.

  int records = cmd.ExecuteNonQuery()

  check the value in records to see how many records inserted.

  The real voyage of discovery consists not in making new landscapes, but in having new eyes
  
  My Blog
• • Reply
  

  tzahi2010

  Member

  266 Points

  287 Posts

  Re: Problem excel C#

  Nov 25, 2011 02:39 PM|LINK

  when this is running:

                  int records = cmd.ExecuteNonQuery();
                  
  im getting an Exception

   

  • Edited by tzahi2010 on Nov 25, 2011 02:40 PM
• • Reply
  

  Mikesdotnett...

  All-Star

  154818 Points

  19853 Posts

  Moderator

  MVP

  Re: Problem excel C#

  Nov 25, 2011 05:48 PM|LINK

  string conn = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + DBtb.Text + ";Extended Properties=Excel 12.0";
  using(OleDbConnection oledbConn = new OleDbConnection(conn)){
    oledbConn.Open();
    OleDbCommand cmd = new OleDbCommand("INSERT INTO [$Sheet4] (B5, C5, D5) VALUES(1,'Fake Record','Fake Record')", oledbConn);
    cmd.ExecuteNonQuery();
  }

  You do have columns named C5, D5 etc, don't you? You can use the Excel column names. You have to provide your own as the first row in the spreadsheet.

  Regards, Mike
  Beginning ASP.NET Web Pages with WebMatrix | My Site | Twitter
  • Edited by Mikesdotnetting on Nov 25, 2011 05:50 PM
  • Edited by Mikesdotnetting on Nov 25, 2011 08:00 PM
• • Reply
  

  tzahi2010

  Member

  266 Points

  287 Posts

  Re: Problem excel C#

  Nov 25, 2011 06:18 PM|LINK

  listen the "Dbtb.Text" is wrong it says its:
  "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\\Users\\Tzahi\\Desktop\\Xlsx Files\\Clal data base 20110930 - Copy.xls;Extended Properties=Excel 12.0"
  u see the dubble \\ which arent really there theres a problem 

  even when i do this:
   string conn = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Users\Tzahi\Desktop\Xlsx Files\Clal data base 20110930 - Copy.xls;Extended Properties=Excel 12.0";

  after it when i put breakpoints i see it changes during the runtime 

  • Edited by tzahi2010 on Nov 25, 2011 06:22 PM
• • Reply
  

  Mikesdotnett...

  All-Star

  154818 Points

  19853 Posts

  Moderator

  MVP

  Re: Problem excel C#

  Nov 25, 2011 06:32 PM|LINK

  Why don't you move the file to App_Data within your site, then use this connection stirng:

  Provider=Microsoft.ACE.OLEDB.12.0;Data Source=|DataDirectory|Clal data base 20110930 - Copy.xls;Extended Properties=Excel 12.0

  Regards, Mike
  Beginning ASP.NET Web Pages with WebMatrix | My Site | Twitter
• • Reply
  

  tzahi2010

  Member

  266 Points

  287 Posts

  Re: Problem excel C#

  Nov 25, 2011 06:38 PM|LINK

  never mind problem solved but im having another problem
  when im trying to select a sheet it doesnt work
  code:
  
  

  OleDbCommand cmd = new OleDbCommand("SELECT O FROM [Main Table]", oledbConn);

  
  
  error:

  The Microsoft Access database engine could not find the object 'Main Table'. Make sure the object exists and that you spell its name and the path name correctly. If 'Main Table' is not a local object, check your network connection or contact the server administrator.

   

• • Reply
  

  Mikesdotnett...

  All-Star

  154818 Points

  19853 Posts

  Moderator

  MVP

  Re: Problem excel C#

  Nov 25, 2011 08:00 PM|LINK

  You need to prefix sheet names with a $ sign:

  OleDbCommand cmd = new OleDbCommand("SELECT O FROM [$Main Table]", oledbConn);

  Regards, Mike
  Beginning ASP.NET Web Pages with WebMatrix | My Site | Twitter
• • Reply
  

  tzahi2010

  Member

  266 Points

  287 Posts

  Re: Problem excel C#

  Nov 26, 2011 05:00 AM|LINK

  Mikesdotnetting

  You need to prefix sheet names with a $ sign:

  OleDbCommand cmd = new OleDbCommand("SELECT O FROM [$Main Table]", oledbConn);

  didnt work

• • Reply
  

  Mikesdotnett...

  All-Star

  154818 Points

  19853 Posts

  Moderator

  MVP

  Re: Problem excel C#

  Nov 26, 2011 06:50 AM|LINK

  Try wrapping the sheet name in single quotes if it has a space in it:

  OleDbCommand cmd = new OleDbCommand("SELECT O FROM ['$Main Table']", oledbConn);

  Regards, Mike
  Beginning ASP.NET Web Pages with WebMatrix | My Site | Twitter
  • Edited by Mikesdotnetting on Nov 26, 2011 06:50 AM