Error “OdbcParameterCollection only accepts non-null OdbcParameter type objects, not SqlParameter objects” while trying to get values from excel sheet

DotNetLearner 1 Reputation point
2021-06-23T10:26:42.527+00:00

The test fails with "OdbcParameterCollection only accepts non-null OdbcParameter type objects, not SqlParameter objects" error while trying to get values from the excel sheet (data source for my coded UI tests).

[DataSource("System.Data.Odbc", "Dsn=Excel Files;Driver={Microsoft Excel Driver (*.xls)};dbq=|DataDirectory|\Source.xlsx;defaultdir=.;driverid=790;maxbuffersize=2048;pagetimeout=5;readonly=true", "Sheet1$", DataAccessMethod.Sequential), TestMethod]

Since the test is used for checking the translations of languages supported by our app, the excel sheet has only language codes(en-US, pl-PL, cs-CZ, etc.) in all the rows.

I have installed Microsoft Access Database Engine 2010 (English) and configured ODBC data sources in Windows Administrative Tools.

This test was working fine before, I am not able to figure out what has gone wrong suddenly.

C#
C#
An object-oriented and type-safe programming language that has its roots in the C family of languages and includes support for component-oriented programming.
10,625 questions
Office Development
Office Development
Office: A suite of Microsoft productivity software that supports common business tasks, including word processing, email, presentations, and data management and analysis.Development: The process of researching, productizing, and refining new or existing technologies.
3,709 questions
Visual Studio Testing
Visual Studio Testing
Visual Studio: A family of Microsoft suites of integrated development tools for building applications for Windows, the web and mobile devices.Testing: The act or process of applying tests as a means of analysis or diagnosis.
337 questions

1 answer

Sort by: Most helpful
Most helpful Newest Oldest
  1. Michael Taylor 51,256 Reputation points
    2021-06-23T13:44:32.327+00:00

    The error tells me you're trying to add a SqlParameter to the Parameters collection of an OdbcCommand and that won't work. Each DB provider has its own ADO.NET types. You must use the type corresponding with the provider you're using. In the case of ODBC it would be OdbcParameter. Alternatively you can use the CreateParameter on the command to create the parameter without regard for its type. 

    using (var conn = new OdbConnection(...))
{
   //Must use the type corresponding to the connection
   var cmd = new OdbCommand();
   //Or var cmd = conn.CreateCommand();

   var parm1 = new OdbcParameter();
   //Or var parm1 = cmd.CreateParameter();
}

    It looks like you are writing a data test. It is also possible there was a change made to the test framework you're using that prevents it from working with ODBC. It looks like you're using MSTest so make sure you are running the latest version. You may have found another bug in the implementation. You can refer to their Github Issues list to see if there are some problems with ODBC.

    1 person found this answer helpful.
    0 comments