Consult mysql and show in a MS Visual Basic listbox?

Question

Consult mysql and show in a MS Visual Basic listbox?

Navigation

#1 by (1 votes)

1

Cordial greeting colleagues, It turns out that I have a database called quote, a form in MS Visual Basic with a listbox and a button called consult. what I want to do is, by means of the consult button, show in the listbox the information that is in the database. To connect the application with my database use the following code in a module, creating the following function:

Imports MySql.Data.MySqlClient
Module conexion
    'En este bloque esta el metodo o funcion para conectar con la base de datos
    Public Function conectar()

        Try
            Dim conexion As New MySqlConnectionStringBuilder()
            conexion.Server = "localhost"
            conexion.UserID = "root"
            conexion.Password = ""
            conexion.Database = "cotizacion"

            Dim con As New MySqlConnection(conexion.ToString())
            con.Open()
            MsgBox("La conexion se realizo correctamente", 64)
        Catch ex As Exception
            MsgBox("No se pudo conectar", 48)
        End Try


        Return 0
    End Function

It perfectly connects with the function created, the function calls it in the form load.

Try to make the query, and show it in the listbox by means of the button in the following way:

Imports MySql.Data.MySqlClient
Public Class Form1
    Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
        conectar()
    End Sub

    Private Sub btnconsultar_Click(sender As Object, e As EventArgs) Handles btnconsultar.Click

        Dim sql As String = " SELECT nombre_estudio FROM estudios'"
        Dim comando As New MySqlCommand(sql)
        Dim resultado As MySqlDataReader

        resultado = comando.ExecuteReader
        listestudios.Items.Add(resultado)

    End Sub
End Class

But I get an error, how could I do it correctly?

mysql vb.net visual-studio-2017

asked by Kevin Burbano 09.02.2018 в 18:08

source

1 answer

How to separate a list with a delimiter? java.lang.RuntimeException: Unable to start activity ComponentInfo

score 1 · Accepted Answer

You are having a confusion about what you are doing.

That you have managed to connect to the DB, although it is a very big step, it does not imply that your program knows everywhere which connection to use. You could for example, have more than one connection to several different DBs.

Then, in your program the connect function, effectively creates a connection, opens it ... and immediately closes it, because the function does not return the object that is connected, which is the one that actually knows how to talk to that DB.

The command object, in addition, requires knowing which connection to use, which tells you specifically to which DB to connect.

Then let's try these changes:

Public Function conectar() as MySqlConnection
    Try
        Dim conexion As New MySqlConnectionStringBuilder()
        conexion.Server = "localhost"
        conexion.UserID = "root"
        conexion.Password = ""
        conexion.Database = "cotizacion"

        Dim con As New MySqlConnection(conexion.ToString())
        con.Open()
        MsgBox("La conexion se realizo correctamente", 64)
        Return con
    Catch ex As Exception
        MsgBox("No se pudo conectar", 48)
        Return nothing
    End Try

This function now returns an open connection. IMPORTANT , remember to close it after each time you use it.

and now in the other function, let's do something like this:

Private Sub btnconsultar_Click(sender As Object, e As EventArgs) Handles btnconsultar.Click
    Dim con As MySqlConnection
    con = conectar()
    'Aca deberias verificar si efectivamente te devolvio una conexion abierta
    Dim sql As String = " SELECT nombre_estudio FROM estudios"
    Dim comando As New MySqlCommand(sql)
    comando.Connection = con
    Dim resultado As MySqlDataReader

    resultado = comando.ExecuteReader
    listestudios.Items.Add(resultado)
    'RECUERDA CERRAR LA CONEXION!
End Sub

Now your command should know what connection to use.