question

DotNetLearner avatar image
0 Votes"
DotNetLearner asked TimonYang-MSFT edited

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

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.

dotnet-csharpoffice-scripts-excel-devvs-testing
· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.


Maybe you must write ‘using Microsoft.Data.SqlClient’ instead of ‘using System.Data.SqlClient’ (according to similar issues, for example: https://github.com/dotnet/efcore/issues/16812).


0 Votes 0 ·

1 Answer

cooldadtx avatar image
1 Vote"
cooldadtx answered DotNetLearner rolled back

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.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.