Click here to Skip to main content
16,004,406 members
Please Sign up or sign in to vote.
1.00/5 (1 vote)
See more:
Hello,

I'm trying to connect to a MYSQLWorkbench DataBase from VB.NET (Visual Studio) using this code:

Imports System.Data
Imports MySql.Data.MySqlClient
Imports MySql.Data

Public Class Form1

    Dim oCon As MySql.Data.MySqlClient.MySqlConnection


    Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load

        Dim sProveedor As String

        sProveedor = "server = localhost; database = vikingos_barberia; user id = root; password = **********; port = 3306"
        oCon = New MySqlClient.MySqlConnection(sProveedor)

        oCon.Open()
        MsgBox("Conexion Exitosa!!")


    End Sub
End Class


What I have tried:

I verified several times the parameter without any change.
Posted
Updated 30-Apr-23 18:46pm

To add to what Dave says, it's even worse - you open a connection to the DB which can never be used as it is only stored in a local variable, which goes out of scope at the end of the Form.Load event handler.

Since it can't be accessed at all outside the method (in fact all local variables no longer exists when the method ends) the connection you create is completely useless, and just takes up scarce resources on the server.

You should always us a Using block around the creation of any DB objects (Connection, Command, Reader, and Adapter) to automatically close and Dispose them when the block ends:
VB
Using con As New SqlConnection(strConnect)
	con.Open()
	Using cmd As New SqlCommand("SELECT iD, description FROM myTable", con)
		Using reader As SqlDataReader = cmd.ExecuteReader()
			While reader.Read()
				Dim id__1 As Integer = CInt(reader("iD"))
				Dim desc As String = DirectCast(reader("description"), String)
				Console.WriteLine("ID: {0}" & vbLf & "    {1}", iD, desc)
			End While
		End Using
	End Using
End Using
 
Share this answer
 
The problem is that you are using Oracle's very buggy MySql.Data class library. It's not well maintained and should be avoided. It is not compatible with newer versions of MariaDB and the errors are often nonsensical like the one you received.

Instead switch to using the truly open source MySqlConnector. The code is identical, you just have to change the Imports line at the top of each module or class.
 
Share this answer
 
Comments
agilith 21-Jan-24 10:53am    
Seems I can't edit my reply above. By "the code is identical" I mean the code you use within your project is identical. So you don't need to refactor your code to change from Oracle's buggy MySql.Data implementation to Bradley Grainger's excellent MySqlConnector open source implementation.
The code you posted cannot possibly generate that error. The error message refers to converting something retrieved from the database where a field value is DbNull and you're trying to convert that to another type.

The code you posted does not execute a query at all. All you're doing is opening a connection in the Form_Load event.

As a side-note, you should NOT be opening a connection to the database and keeping it open for the lifetime of your app. You should be opening a connection to the database only when you need to execute a query, and closing it as soon as possible.
 
Share this answer
 
Comments
Member 15022970 25-Jul-24 4:53am    
Your statement is incorrect. This error is thrown when an attempt is made to open the connection. An SQL query is not even involved here. The MySQL connector is probably faulty and is therefore the cause of the problem.
Dave Kreskowiak 25-Jul-24 8:49am    
Yeah, it's buggy, but my statement still stands. Converting a DbNull to another type doesn't work, and the code the guy posted did not execute a query, but only tried to opened a connection. Again, MySQL is buggy, but I covered more than what you did.


Member 15022970 25-Jul-24 9:14am    
That's exactly the problem, that the error occurs when opening the connection and not when issuing a SQL select command. I solved the problem today by installing a NuGet MySqlConnector from Bradley Grainger in Visual Studio. After that, opening the connection and all subsequent commands worked perfectly.
Dave Kreskowiak 25-Jul-24 9:21am    
The conversion of DbNull DOES show up when trying to convert the "value" to another type. I HAVEA SEEN IT HAPPEN MYSELF IN MY OWN CODE.
Member 15022970 25-Jul-24 9:25am    
Yes, I agree with you. But the error message also appears when opening the connection to MariaDB from a certain version onwards. So the code at the beginning of the post is correct because it produces the error even without SELECT commands and just through the OPEN command alone.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900