How can I handle this error: 'object cannot be cast from dbnull to other types.'?

Question

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 30-Apr-23 12:28pm

Carlos Almeraz

Updated 30-Apr-23 18:46pm

Add a Solution

3 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

OriginalGriff · Answer 1 · 2023-04-30T18:46:00

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

Dave Kreskowiak · Answer 2 · 2023-04-30T13:26:00

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.

agilith · Answer 3 · 2024-01-21T04:47:00

Solution 3

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.

Posted 21-Jan-24 4:47am

agilith

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.

How can I handle this error: 'object cannot be cast from dbnull to other types.'?

3 solutions

Solution 2

Solution 1

Solution 3

Add your solution here

Preview 0