What would be the OleDB connection string in case of Office 2013 RSS

• • Reply
  

  John Bhatt

  Member

  164 Points

  29 Posts

  What would be the OleDB connection string in case of Office 2013

  Nov 23, 2012 05:27 AM|LINK

  I have developed an application and in a web form, I have to upload data from Excel file and save it to database.

  It works perfect to Display records in Grid but while I try to save data using SqlBulkCopy command, it is throwing error telling something like , Server Version. 

  I want to know, what will be the Version of Server in case if user has installed Microsoft Office 365/ 2013 in their machine and we are using OleDB Connection from Access or Excel.

  John Bhatt

• • Reply
  

  sargamlucy

  Member

  559 Points

  164 Posts

  Re: What would be the OleDB connection string in case of Office 2013

  Nov 23, 2012 07:14 AM|LINK

  Check if the below code helps you, I am using this in my appl.

  public static string ExcelConnectionString(string filePath)
          {
              try
              {
                  OleDbConnectionStringBuilder sbConnection = new OleDbConnectionStringBuilder();
                  String strExtendedProperties = String.Empty;
                  sbConnection.DataSource = filePath;
  
                  if (Path.GetExtension(filePath).Equals(".xls"))//for 97-03 Excel file
                  {
                      sbConnection.Provider = "Microsoft.Jet.OLEDB.4.0";
                      strExtendedProperties = "Excel 8.0;HDR=Yes;IMEX=1";//HDR=ColumnHeader,IMEX=InterMixed
                  }
                  else if (Path.GetExtension(filePath).Equals(".xlsx"))  //for 2007 Excel file
                  {
                      sbConnection.Provider = "Microsoft.ACE.OLEDB.12.0";
                      strExtendedProperties = "Excel 12.0;HDR=Yes;IMEX=1";
                  }
  
                  sbConnection.Add("Extended Properties", strExtendedProperties);
  
                  return sbConnection.ToString();
              }
              catch (Exception)
              {
                  throw;
              }
          }

  
  Regards,
  Snigdha
  Please Mark as Answer if my reply helped you.
  • Marked as answer by John Bhatt on Nov 25, 2012 08:39 AM
• • Reply
  

  Decker Dong ...

  All-Star

  118619 Points

  18779 Posts

  Re: What would be the OleDB connection string in case of Office 2013

  Nov 24, 2012 12:05 AM|LINK

  John Bhatt

  John Bhatt

  Hello，

  For Excel or Access connection strings，you can refer to this：

  http://www.connectionstrings.com/

  • Marked as answer by John Bhatt on Nov 25, 2012 08:39 AM
• • Reply
  

  John Bhatt

  Member

  164 Points

  29 Posts

  Re: What would be the OleDB connection string in case of Office 2013

  Nov 25, 2012 08:34 AM|LINK

  Thanks for the link. I was unaware of site. This site has huge collection of different types of Connections String with their description also.

• • Reply
  

  John Bhatt

  Member

  164 Points

  29 Posts

  Re: What would be the OleDB connection string in case of Office 2013

  Nov 25, 2012 08:38 AM|LINK

  Thanks for the code. Actually I am also using the code like above. 

  But my problem is when, User has not installed MS office 2007 or 2010 in System and using Office 365 (2013). In that case it was throwing error. Hoever, I uninstalled Office 2013 and installed Office 2010, Now the same code is working perfect.

  By the way, thanks for answers.

  • Edited by John Bhatt on Nov 25, 2012 08:38 AM
  • Marked as answer by John Bhatt on Nov 25, 2012 08:40 AM