Cannot connect to MySQL database

[GideonE]

Hope I am not asking this on the wrong forum, but I have no idea where to look elsewhere (and this forum has been my recue many, many times).

I am trying to develop a new VB.net application with Visual Studio 2019. I cannot add a connection to my application. When I connect (local host and root) it creates a connection, but if I want to open the table folder I get an error "character set 'utf8mb3' is not supported by .Net. However my Mysql database does not use that (it is utf8mb4). What am I missing. I recently updated to a new MYQLconnector (8.0.33.) I am using MYSQL 8.

Any ideas would be much appreciated

[Zvoni]

What are the Character-Set Values in the server-config (my.ini)?

[GideonE]

Thank you very much for replying.

I include part of the my.ini file

[mysqld]
local_infile=ON
secure-file-priv = ""

character-set-server=utf8mb4
collation-server=utf8mb4_0900_ai_ci

[Zvoni]

Do you specify a Characterset in the connection itself (what was ODBC Connection-String)?
and have you checked each table/column, what character-set is applied there? You do know, that you can specify different charactersets to tables during creation, irrespective of what is the default for the Server (my.ini)?
And don't forget the Collation, too. Those can even be set per Column

If that doesn't help, i'm out of ideas

[dday9]

And don't forget the Collation, too. Those can even be set per Column

That has gotten the best of me before.

[GideonE]

All the caractersets and collation for the database, tables and columns are the same.

[GideonE]

Yes, looks like Microsoft is putting pressure on us to use SQL in stead of MySql.
Even hours spent on chatgbt could not solve this.

[Zvoni]

Well, last idea: does it work with ODBC?

[dday9]

Another idea is to use an ORM to handle the database connection and commands for you.

For example, PetaPoco (GitHub) supports MySQL (documentation).

If you go to Project > {project name} Properties, open the Application tab, click on View Application Events, you can do the following:

1. Create a new property of type Database named ApplicationDatabase
2. Generate the MyApplication.Startup event
3. Inside of the event handler, instantiate PetaPoco using the MySqlConnector provider
4. Create a POCO class that represents one of your tables (e.g. class named UserModel with UserId, Username, etc. properties)
5. For testing purposes, run the following code to see if it works:

   Dim records = My.Application.ApplicationDatabase.Query(Of UserModel)("SELECT * FROM `user`;")

It is definitely overkill but I'm curious to see if that works.

[GideonE]

Hi Zvoni. Thank you for making the effort to reply.
I have tried to connect via Microsoft ODBC Data Source (ODBC).
I have set a SYSTEM DNS on my ODBS Data Sources (64bit) and created a data source name (using Microsoft ODBC 8.0 Unicode), tested it and it connects. I used server : localhost, user :root and my password. The databasis show up in the dropdown and it tests OK.
BUT when i try to add a connection via server explorer in visual studio choosing Microsoft ODBC Data Source (ODBC), after i click OK no data source names are listed. Am I doing it right? I cannot find any tutorials online.

[vbdotnut]

sometimes clearing the appdata can have a positive result, especially after updating a connector such as this, it seems to me that a cached setting can often lead to confusing results. It is worth a shot

Run: %LocalAppData%

this will bring you to your local app data, find your apps and delete the folders.

failing everything else i would roll back the connector.

[GideonE]

Ok so I have given up. To anyone reading this - if you want to create database applications with Visual studio : DO NOT USE MYQL.
Even if you use Visual Studio 2019.
Also it seems vb.net is with its one foot in the grave.

[dday9]

You're not even going to bother with my suggestion?

[vbdotnut]

Also it seems vb.net is with its one foot in the grave.

nothing could be further from the truth.

MySQL is a fine choice as well, no need to discourage people from using it.

It sounds to me like youve botched an installation somewhere. What ever the problem is you are having it is due to the choices you have made

[GideonE]

Hi dday9. I have managed to use ODBC. Seems like Visual studio used 32 bit. So when i made the sure the platform in my ODBC Datasources is a 32 bit I could add and ODBC connections after many,many hours of research. Thank you for putting me on the ODBC route.

[GideonE]

Hi vbdotnut. Apologies that was frustration getting the hold of me. I agree that I must have " botched' up somewhere. Finding that issue connection to a Mysql database using MySQL Database (MySQL Data Provider) got the best of me, even worst is that I used it in the past and trying to reproduce it is what got me. Regards.