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

Question

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;

Solution 1

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

Solution 2

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 ran 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.

Solution 3

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

Solution 4

Try using

SELECT count(*) as count FROM goods

Solution 5

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());

View more solutions

Comments

[AsthaUndefined]

Are you getting the same error by using this statement also?

[AsthaUndefined]

try cmd.Connection.Open(); before converting it to Int32.

[Romil Kumar Jain]

some time it may required .Net 4.5.2 or upper version