In this post, I will certainly give among the solution to get over “The provided key was not present in the dictionary” exception in MySQL.

You are watching: The given key was not present in the dictionary


In this short article, I will certainly provide one of the services to get rid of the "The provided essential was not present in the dictionary" exemption in MySQL.

Developers may confront this error in many instances, yet I confronted this error in query execution after migrating the MSSQL Database to MySQL.

Recently, I have moved a database from MSSQL to MySQL. After migration, I tried to run the simple choose query with joins which currently functioned in my application through MSSQL connection string. When I ran the exact same query through MySQL Connection string, I gained the “The given key was not existing in the dictionary” Exception. But if I run the very same query in MySQL Workbench, it"s functioning fine.

So, here is my sample query which I tried to execute through MySqlDataAdapter.


Select*fromusersdetails;nMySqlDataAdapter=newMySqlDataAdapter(xszQuery,zSqlConn);

When this query passes MySqlDataAdapter, I"m obtaining the exemption “The offered essential was not present in the dictionary”. But the exact same query is working fine in MySQL Workbench.

It"s the very same process if I pass the query like,


SelectusersID,usersnamefromusersdetails;nMySqlDataAdapter=newMySqlDataAdapter(xszQuery,zSqlConn);//Workingfinewithoutanyexception.

I have actually sought many type of services on Google, yet none functioned for me. Some services sassist that error in connection string needs to incorporate the charset=utf8 parameter through the connection string. But that didn"t work-related for me.

Then I discovered something:


SelectusersID,usersnamefromusersdetails;//thisqueryworkingfine,noerrorSelectusersID,usersname,usersAddressfromusersdetails;//thisqueryreturnsexceptionSelectusersID,userGUIDfromusersdetails;//thisqueryworkingfine,noerrorSelectusersID,userGUID,userDepartmentfromusersdetails;//thisqueryreturnsexception

So, the trouble is something through the columns which I trying to select, then I discovered what makes each column differ from the various other.


*

What I discovered is “Collation” value of each column is various based on column dataform, I don’t know in what basis these worths are assigned during migration.

What is Collation in MySQL?

A collation is a set of rules that specifies exactly how to compare and also type character strings. Each collation in MySQL belongs to a single character set. Eexceptionally character set contends leastern one collation, and also a lot of have two or even more collations.

A collation orders characters based upon weights. Each character in a character set maps to a weight. Characters via equal weights compare as equal, and characters via unequal weights compare according to the relative magnitude of their weights.

To know even more about “Collation” click here.

I followed 2 various approaches to get over this exception.

Method 1

By picking “Table default” value in “Collation” option for the all columns in a table we deserve to conquer this exception.


*

Method 2

Throughout migration from MSSQL to MySQL, on hand-operated editing action, we have the right to check out the tables, column names, and respective dataforms. In the complying with picture on step 3, by default MySQL hadtaken “CHARACTER SET ‘utf8mb4’ ” as charcollection value for some columns. We can edit this section, just pick and also delete “CHARACTER SET ‘utf8mb4’ ” and apply transforms. Now all columns with “Collation” value end up being “Table default” after migration.

See more: When Mendel Crossed Tall (Tt) Plants With Tall (Tt) Plants The Offspring Were?


*

By utilizing these two ways we deserve to overcome this exemption. In this short article, I have provided one of the means to get rid of “The offered key was not current in the dictionary” exception. If anybody knows any type of other way to get over this exemption, please point out in the comment box. I hope this write-up is exceptionally beneficial.


Next off Recommfinished Reading
FEATURED ARTICLES

View All


TRENDING UP

View All