Reputation: 1185

.Net MySql error "The given key was not present in the dictionary"

Trying to get simple count from table results in exception bellow. Tried different select statemens which also makes exception: "SELECT * FROM goods", but "SELECT col1, col2 FROM goods" - works without exception. What am I doing wrong? From workbench these selects works.

The given key was not present in the dictionary. System.Collections.Generic.KeyNotFoundException: The given key was not present in the dictionary. at System.Collections.Generic.Dictionary`2.get_Item(TKey key) at MySql.Data.MySqlClient.MySqlField.SetFieldEncoding() at MySql.Data.MySqlClient.NativeDriver.GetColumnData(MySqlField field)
at MySql.Data.MySqlClient.NativeDriver.GetColumnsData(MySqlField[] columns) at MySql.Data.MySqlClient.Driver.GetColumns(Int32 count)
at MySql.Data.MySqlClient.ResultSet.LoadColumns(Int32 numCols) at MySql.Data.MySqlClient.ResultSet..ctor(Driver d, Int32 statementId, Int32 numCols) at MySql.Data.MySqlClient.Driver.NextResult(Int32 statementId, Boolean force) at MySql.Data.MySqlClient.MySqlDataReader.NextResult() at MySql.Data.MySqlClient.MySqlDataReader.Close() at MySql.Data.MySqlClient.MySqlCommand.ResetReader() at MySql.Data.MySqlClient.MySqlCommand.ExecuteReader(CommandBehavior behavior) at MySql.Data.MySqlClient.MySqlCommand.ExecuteReader()
at MySqlSybaseComparer.DbTester.Test(String& error) in c:\MySqlSybaseComparer\DbTester.cs:line 68

code snippet:

using (MySqlConnection conn = new MySqlConnection(ConStrMySql))
{
    try
    {
        conn.Open();
        using (MySqlCommand cmd = new MySqlCommand("SELECT count(*) FROM goods", conn))
        {
            using (MySqlDataReader reader = cmd.ExecuteReader())
            {
                if (reader.Read())
                    MessageBox.Show(reader[0].ToString());
            }
        }
    }
    catch (Exception ex)
    {
        MessageBox.Show(ex.Message + Environment.NewLine + ex.ToString(););
    }
    conn.Close();
}

Connection string to DB: Server=localhost; Database=art; Uid=ramunas; Pwd=xxxx; AllowUserVariables=True;

Upvotes: 18

Answers (14)

anuradha

Reputation: 133

As of 2023, updating MySqlData.dll to the latest using the Nuget or offline installation is the only fix for this.

Upvotes: 1

Hadi Zamani

Reputation: 11

use Database name in your query and Port in connection String

strMySqlConnection = 
   "server=localhost;Port=3306; user id=root; password=; database=art; 
    pooling=false;Charset = utf8 ;";
SELECT * FROM goods ==> SELECT * FROM `art`.`goods`

Update MySql.Data & use .Netframework 4.6

Upvotes: 0

Xavier

Reputation: 1430

To get compatability you will need to use specific versions of the MySql.EntityFrameworkCore, MySql Connector/NET and entity framework to avoid these issues.

See https://dev.mysql.com/doc/connector-net/en/connector-net-entityframework-core-scaffold-example.html for examples

<PropertyGroup>
    <TargetFramework>net6.0</TargetFramework>
</PropertyGroup>
<ItemGroup>
    <PackageReference Include="Microsoft.EntityFrameworkCore.Tools" Version="6.0.10">
        <IncludeAssets>runtime; build; native; contentfiles; analyzers; buildtransitive</IncludeAssets>
        <PrivateAssets>all</PrivateAssets>
    </PackageReference>
    <PackageReference Include="MySql.EntityFrameworkCore" Version="6.0.4" />
</ItemGroup>

Update the entity framework tool with

dotnet tool update --global dotnet-ef

The scaffolding command should look like

dotnet ef dbcontext scaffold "Server=[Host];Database=[DBName];User Id=[Username];Password=[Password];" MySql.EntityFrameworkCore -o [OutputContextName] --no-pluralize --no-onconfiguring -f

For details check out https://learn.microsoft.com/en-us/ef/core/cli/dotnet and search for dotnet ef dbcontext scaffold

Upvotes: 0

Rafael Neto

Reputation: 1290

Year 2017

I have solved your same error simply adding the charset to the connection string:

Server=myServer;Port=3306;Database=myDB15;User ID=usr33;Password=usr33P;CharSet=utf8;

In my case I'm using MySql Connector for .Net version 6.9.3. to connect to 30 equal databases with the same structure, same collation (utf8_unicode_ci) and different table contents.

When I use the MySqlCommand.ExecuteReader() method to select content from user table, in some databases (4 of 30) a got the same error The given key was not present in the dictionary.

Year 2022

Some years later I had the same problem with ObjectContext.ExecuteStoreQuery(), but right now using MySql.Data and MySql.Data.EntityFramework both on version 8.0.27 and when migrating a database from MySQL 5.7.38 to 8.0.28 or 8.0.30.

Adding CharSet or Allow User Variables to the connection string didn't work (and it wasn't possible to change my SELECT or Server settings).

Only updating MySql.Data and MySql.Data.EntityFramework to version 8.0.30 solved the problem.

Upvotes: 32

nightooi

Reputation: 11

For anyone with the error "the given key '0' was not found in the dictionary", it seems it's looking for the MySQL DLL. Adding MySql.Data through your package manager or adding the DLL manually from the connector should solve it.

Upvotes: 1

Balkrishna Panthi

Reputation: 1

You may try going to "Server explorer" and delete the instance of your server that you are trying to connect. Once the instance is deleted trying connecting to the database once again with correct credentials. And if you are working with ADO.NET model be careful the versions of component that you are installing and referencing.

Upvotes: 0

Charulatha Krishnamoorthy

Reputation: 1

Do update your mysql connector for .net, Then remove the old reference mysql.data.dll in your application project and add the updated one from program files(x86)/mysql/MySQL Connector Net 8.0.26\Assemblies\v4.5.2\MySql.Data.dll

Upvotes: 0

John Dovey

Reputation: 31

I tried all of these answers and none helped me. I eventually tracked it down. The message was that .NET does not support the characterset (in my case utf8mb3) that the database was reporting. Changed the default charset of the database and it worked perfectly.

Upvotes: 0

ibrahim saputra

Reputation: 417

in my case i use MySQL Connector NET 6.3.0 and the solution that i got is to change connection string key
from

Server=127.0.0.1;Uid=root;Pwd=xxxx;Database=dbname;CharSet=utf8

to this

server=127.0.0.1;user id=root;password=xxxx;database=dbname;charset=utf8;pooling=false

Upvotes: -1

Musa Alp

Reputation: 11

I had same problem using .net core 2.1, mysql, nhibernate.

After i added "Allow User Variables=True" to connection string in appsettings.json my problem solved.

Upvotes: 1

Marvin Nario Machitar

Reputation: 75

After spending 5 hours researching how to fix it!!!.. i finally figured it out... all you need to do is to make sure that your 'MySql.Data.dll' is up to date! you can download it somewhere. or you can find it here C:\Program Files (x86)\MySQL\MySQL Installer for Windows\MySql.Data.dll.. :D

Upvotes: 5

Ramunas

Reputation: 1185

Code is correct and all suggestion also should work. Just removed "collation-server" settings and restarted server and everything works as expected.

Upvotes: 4

AsthaUndefined

Reputation: 1109

In place of this statement:

using (MySqlCommand cmd = new MySqlCommand("SELECT count(*) FROM goods", conn))

Use:

using (var cmd = new MySqlCommand("SELECT COUNT(*) FROM goods", conn))

and then convert it to int value by using ExecuteScalar(). Something like this:

int count = Convert.ToInt32(cmd.ExecuteScalar());

Upvotes: 1

Ali Baig

Reputation: 3867

Try using

SELECT count(*) as count FROM goods

Upvotes: 1

.Net MySql error &quot;The given key was not present in the dictionary&quot;

Answers (14)

Year 2017

Year 2022

Related Questions

.Net MySql error "The given key was not present in the dictionary"