locked
Getting Data Source from Excel via app.config file - error ( access db engine not find object sheet1$) RRS feed

  • Question

  • [TestMethod]
    //[DataSource("System.Data.Odbc", "Dsn=Excel Files;dbq=|DataDirectory|\\TestData\\mywidgets.xlsx", "Sheet1$", DataAccessMethod.Sequential)]
    [DataSource("ExcelDS_Sheet1")]
    public void TestMyBusinessLogicWithExcelSecond()
    {
    int valueA = Convert.ToInt32(TestContext.DataRow["num1"]);
    Console.WriteLine("value {0} ", valueA);

    }

    app.config file is 

    <?xml version="1.0" encoding="utf-8" ?>
    <configuration>
    <configSections>
    <section name="microsoft.visualstudio.testtools" type="Microsoft.VisualStudio.TestTools.UnitTesting.TestConfigurationSection, Microsoft.VisualStudio.QualityTools.UnitTestFramework, Version=10.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a"/>
    </configSections>
    <startup>
    <supportedRuntime version="v4.5" sku=".NETFramework,Version=v4.5" />
    </startup>
    <connectionStrings>

    <add name="ExcelCon" connectionString="Dsn=Excel Files;dbq=|DataDirectory|\\TestData\\mywidgets.xlsx;defaultdir=.;driverid=790;maxbuffersize=2048;pagetimeout=5;readonly=true"
    providerName="System.Data.Odbc"  />
    </connectionStrings>
    <microsoft.visualstudio.testtools>
    <dataSources>
    <add name="ExcelDS_Sheet1" connectionString="ExcelCon" dataTableName="Sheet1$" dataAccessMethod="Sequential"/>
    </dataSources>
    </microsoft.visualstudio.testtools>

    </configuration>

    It's working when i include [DataSource("System.Data.Odbc", "Dsn=Excel Files;dbq=|DataDirectory|\\TestData\\mywidgets.xlsx", "Sheet1$", DataAccessMethod.Sequential)].

    Problem is when i include [DataSource("ExcelDS_Sheet1")] which gets configuration from App.Config file

    then getting error.

    Please help to resolve this issue.

    Friday, October 17, 2014 6:03 AM

Answers

All replies

  • Hi nagesh,

    Thank you for posting in the MSDN forum.

    Maybe you could share me the detailed error message in your side.

    I try to repro this issue in my side using VS2013, it seems that it works well in my side like the following screen shot.

    But there are some differences between the App.config file:

    <?xml version="1.0" encoding="utf-8" ?>
    <configuration>
        <configSections>
            <section name="microsoft.visualstudio.testtools" type="Microsoft.VisualStudio.TestTools.UnitTesting.TestConfigurationSection, Microsoft.VisualStudio.QualityTools.UnitTestFramework, Version=10.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a"/> 
        </configSections>
        <connectionStrings>
            <add name="MyJetConn" connectionString="Provider=Microsoft.ACE.OLEDB.12.0; Data Source=c:\users\myname\documents\visual studio 2013\Projects\UnitTest20141017\UTsample\testdatasource.accdb; Persist Security Info=False;" providerName="System.Data.OleDb" />
            <add name="ExcelCon" connectionString="Dsn=Excel Files;dbq=c:\users\myname\documents\visual studio 2013\Projects\UnitTest20141017\UTsample\data.xlsx;defaultdir=.; driverid=790;maxbuffersize=2048;pagetimeout=5;" providerName="System.Data.Odbc" />
        </connectionStrings>
        <microsoft.visualstudio.testtools>
            <dataSources>
                <add name="MyJetDataSource" connectionString="MyJetConn" dataTableName="MyDataTable" dataAccessMethod="Sequential"/>
                <add name="ExcelDS_Sheet1" connectionString="ExcelCon" dataTableName="Sheet1$" dataAccessMethod="Sequential"/>
            </dataSources>
        </microsoft.visualstudio.testtools>
    </configuration>
    Maybe you could change the settings "dbq=c:\users\myname\documents\visual studio 2013\Projects\UnitTest20141017\UTsample\data.xlsx", please use the absolute path for your excel file.

    Best Regards,

    Jack


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Friday, October 17, 2014 10:29 AM
  • Thanks you for suggesting absolute path.  After, I am facing following error.

    Error details: ERROR [IM014] [Microsoft][ODBC Driver Manager] The specified DSN contains an architecture mismatch between the Driver and Application

    app.config has following connection string.

    1. <add name="ExcelCon" connectionString="Dsn=Excel Files;dbq=D:\Automation\Test\AutomationTestsProj\TestData\mywidgets.xlsx;defaultdir=.;driverid=790;maxbuffersize=2048;pagetimeout=5;readonly=true"
      providerName="System.Data.Odbc"  />

    Please advise. 



    Friday, October 17, 2014 12:36 PM
  • Thanks you for suggesting absolute path.  After, I am facing following error.

    Error details: ERROR [IM014] [Microsoft][ODBC Driver Manager] The specified DSN contains an architecture mismatch between the Driver and Application



    Hi nagesh,

    It seems that if you use the 64-bit odbcad32.exe to configure or remove a DSN that connects to a 32-bit driver, you will receive the following error message.

    To resolve this issue, I'm afraid that you would use the 32-bit odbcad32.exe to configure or remove the DSN.

    Reference:

    https://social.msdn.microsoft.com/Forums/sqlserver/en-US/70171a91-f571-49e9-874d-f89eab521f24/excel-microsoft-odbc-driver-manager-the-specified-dsn-contains-an-architecture-mismatch-between?forum=sqldataaccess

    http://community.microfocus.com/borland/test/silk_test/w/knowledge_base/18456.excel-dsn-error-dsn-contains-an-architecture-mismatch.aspx

    Note: This response contains a reference to a third party World Wide Web site. Microsoft is providing this information as a convenience to you.

    Microsoft does not control these sites and has not tested any software or information found on these sites;

    Therefore, Microsoft cannot make any representations regarding the quality, safety, or suitability of any software or information found there.

    There are inherent dangers in the use of any software found on the Internet, and Microsoft cautions you to make sure that you completely understand the risk before retrieving any software from the Internet.

    Best Regards,


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Monday, October 20, 2014 1:49 AM
  •  Hi Jack,

    It's working when i include inline data source string as "[DataSource("System.Data.Odbc", "Dsn=Excel Files;dbq=|DataDirectory|\\TestData\\mywidgets.xlsx", "Sheet1$", DataAccessMethod.Sequential)]". Failing to load from data source from app.config.

    So, how does it matter whether it's 32 bit or 64 bit?  Please give more clarify.

    Please provide how to update DSN of right odbc drivers? Can i use OLEDB to load from app.config?

    Please suggest.


    Monday, October 20, 2014 12:24 PM
  • Hi nagesh,

    You could get it under Control Panel-> Data Sources (ODBC).

    Reference:

    http://support.microsoft.com/kb/942976

    http://wikis.openlinksw.com/dataspace/owiki/wiki/UdaWikiWeb/Win32vs64OdbcAdmin

    Best Regards,


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Tuesday, October 21, 2014 10:39 AM