Reputation: 1185
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
Views: 55429
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
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
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
Reputation: 1290
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.
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
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
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
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
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
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
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
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
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
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