VB.NET connection to MySQL Add Model

Question

I'm very new to .NET. I am trying to use a code example from the first person that posted a response, here: Connect to remote MySQL database using VB.NET 2010

I would like to instantiate the MySqlVB model object but when I add the following code into the controller, I get a not found error. I don't know how to resolve this.

The error is: Warning 1 Namespace or type specified in the Imports 'MySql.Data.MySqlClient' doesn't contain any public member or cannot be found. Make sure the namespace or the type is defined and contains at least one public member. Make sure the imported element name doesn't use any aliases.

What I need is to run a MySQL query and to return the dataset to the controller. Can someone show me how to do this, please?

I'm using VB 2010 Express to do this.

This is the controller

Public Class Form1
    Private Sub PrintBtn_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles PrintBtn.Click
        Dim data As New MySqlVB

        With data
            If .Connection Then
                MessageBox.Show("Database Conneted.")
            Else
                MessageBox.Show(.ErrorMessage)
            End If
        End With
    End Sub
End Class

And this is my model object

Imports MySql.Data.MySqlClient

Public Class MySqlVB
    Private _connection As New MySqlConnection
    Private _errormessge As String
    Private _servername As String = "xxx.xxx.xxx.xxx"
    Private _databasename As String = "testdb"
    Private _userid As String = "theuser"
    Private _password As String = "thepass"

    Public WriteOnly Property ServerName() As String
        Set(ByVal value As String)
            _servername = value
        End Set
    End Property

    Public WriteOnly Property DatabaseName() As String
        Set(ByVal value As String)
            _databasename = value
        End Set
    End Property

    Public WriteOnly Property UserID() As String
        Set(ByVal value As String)
            _userid = value
        End Set
    End Property

    Public WriteOnly Property Password() As String
        Set(ByVal value As String)
            _password = value
        End Set
    End Property

    Public ReadOnly Property ErrorMessage() As String
        Get
            Return _errormessge
        End Get
    End Property

    Public Function Connection() As Boolean
        Try
            _connection.ConnectionString = "Server=" & _servername & ";Port=3306;Database=" & _databasename & ";User ID=" & _userid & ";Password=" & _password & ""
            _connection.Open()
            If _connection.State = ConnectionState.Open Then
                _connection.Close()
                Return True
            End If
        Catch ex As Exception
            _errormessge = ex.Message
            Return False
        End Try
    End Function
End Class

it is better to look for MySqlCommand and vb net this brings a lot of examples . But to your problem, you have a project, you downloaded mysql connector and added the dll as reference. After that you added a new class MySqlVB, and changed the variables nto fit your mysql server then put your code into your Form. So that works fine and where exactly do you get your error? — nbk
– nbk, Commented Jul 17, 2019 at 18:44
@nbk, thanks. I'll look into that. To answer your question, yes, I have a project and MySQL connector (ODBC) was already installed on the machine where VB resides. I have not added a reference though and I'm not sure if I need to or not. I have also used the variables in the controller but the error still remains. The error is being generated from the controller file. — NaN
– NaN, Commented Jul 17, 2019 at 19:17
I have not added a reference though and I'm not sure if I need to or not. You need to. — LarsTech
– LarsTech, Commented Jul 17, 2019 at 19:19
You are re-inventing the wheel building the connection string. See dev.mysql.com/doc/dev/connector-net/6.10/html/… — Mary
– Mary, Commented Jul 17, 2019 at 19:30

Mary · Accepted Answer · 2019-07-17 21:46:30Z

Assuming you have fixed your reference to MySql.Data.MySqlClient I think your class could use some work.

Public Class DataAccess

    Private ConnectionString As String

    Public Sub New(UserName As String, Password As String)
        Dim builder As New MySqlConnectionStringBuilder With {
            .Server = "xxx.xxx.xxx.xxx",
            .Database = "testdb",
            .UserID = UserName,
            .Password = Password
        }
        ConnectionString = builder.ConnectionString
        Debug.Print(ConnectionString) 'just to see what the builder created
    End Sub

    Public Function TestConnecion() As Boolean
        Using cn As New MySqlConnection(ConnectionString)
            Try
                cn.Open()
            Catch ex As Exception
                Debug.Print(ex.Message) 'just to see what is wrong with connection
                Return False
            End Try
        End Using
        Return True
    End Function

    Public Function GetData() As DataTable
        Dim dt As New DataTable
        Using cn As New MySqlConnection(ConnectionString)
            Using cmd As New MySqlCommand("Select * From SomeTable")
                cn.Open()
                dt.Load(cmd.ExecuteReader)
            End Using
        End Using
        Return dt
    End Function

End Class

Assuming you have a DataGridView to display data and 2 text boxes for user id and password, you can use your class in your form like this.

Private Sub FillGrid()
    Dim daClass As New DataAccess(txtUser.Text, txtPassword.Text)
    Dim dt = daClass.GetData
    DataGridView1.DataSource = dt
End Sub

Of course you will need to add error handling. Also you need to salt and hash passwords. Plain text passwords should never be stored.

Thank you Mary. I'm brand new with this language. Thank you for the help!

Collectives™ on Stack Overflow

VB.NET connection to MySQL Add Model

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related