Issue connecting to MySQL using a Class for Connectionstring and Command?

Question

Apologies for the poor wording in the title, I'm new to OOP VB and I have no idea how to describe the problem I'm having! I'm attempting to create a login form that's connecting to MySQL via a class that handles the logging in. It connects no problem but I'm having issues with creating an SQL command to pull data from the database. Here's the SQLConnection Class

    Imports MySql.Data.MySqlClient
    Imports System.Threading
    Public Class SQLConnection
    Private serverhost As String
    Private db As String
    Private userid As String
    Private pwd As String
    Private Shared cn As New MySqlConnection
    Private Shared commandstring As New MySqlCommand

    Public Property Server As String
        Get
            Return serverhost
        End Get
        Set(ByVal value As String)
            serverhost = value
        End Set
    End Property
    Public Property Database As String
        Get
            Return db
        End Get
        Set(ByVal value As String)
            db = value
        End Set
    End Property
    Public Property User As String
        Get
            Return userid
        End Get
        Set(ByVal value As String)
            userid = value
        End Set
    End Property
    Public Property Password As String
        Get
            Return pwd
        End Get
        Set(ByVal value As String)
            pwd = value
        End Set
    End Property
    Public Property command As MySqlCommand
        Get
            Return commandstring
        End Get
        Set(ByVal value As MySqlCommand)
            commandstring = value
        End Set
    End Property

    Private Shared ReadOnly Property Conn As MySqlConnection
        Get
            Return cn
        End Get
    End Property

    Public Shared Function TryConn(ByVal obj As SQLConnection) As Boolean
        Try
            Dim connectionstring As String =
            "server=" & obj.Server &
            ";database=" & obj.Database &
            ";user id=" & obj.User &
            ";password=" & obj.Password
            cn = New MySqlConnection
            cn.ConnectionString = connectionstring
            If cn.State = ConnectionState.Closed Then
                cn.Open()
            End If
            Return True
            cn.ConnectionString = ""


        Catch ex As Exception
            Return False
        End Try

    End Function
End Class

Here's the login form Code snippet:

Try
            Dim Conn As New SQLConnection
            Dim reader As MySqlDataReader
            With Conn
                .Server = "localhost"
                .Password = ""
                .User = "root"
                .Database = "customers"
            End With
            If SQLConnection.TryConn(Conn) = True Then
                Dim Query As String
                Query = String.Format("SELECT * FROM users WHERE Username = '{0}' AND Password = '{1}'", Me.UsernameTextBox.Text.Trim(), Me.PasswordTextBox.Text.Trim())
                sql = New MySqlCommand(Query, Conn)
                reader = sql.ExecuteReader
                Dim count As Integer
                count = 0
                While reader.Read
                    count = count + 1
                End While
                If count = 1 Then
                    Me.Hide()
                    sqlloading.Show()
                ElseIf count > 1 Then
                    errorform.FlatAlertBox1.Text = "Username and password are duplicate"
                    errorform.Show()
                Else
                    errorform.FlatAlertBox1.Text = "Wrong username or password"
                    errorform.Show()
                End If
                Me.Hide()
            Else
            End If
        Catch
        End Try

When running this I get

"Value of type 'WindowsApplication1.SQLConnection' cannot be converted to 'MySql.Data.MySqlClient.MySqlConnection'.

Answer 1

The problem causing the error message appears to be on this line:

sql = New MySqlCommand(Query, Conn)

Because Conn is an instance of your SQLConnection type, but has to be a MySql.Data.MySqlClient.MySqlConnection instance, that you have created as a private property of SQLConnection.

You need to make a few changes:

1. Make Conn and TryConn methods normal, non shared methods
2. Make the cn field non shared.
3. Have the SQLConnection class implement IDisposable, making sure to dispose cn when it is disposed.

4. Change the login form code block to the below:

   Try
       Using Conn As New SQLConnection
           With Conn
               .Server = "localhost"
               .Password = ""
               .User = "root"
               .Database = "customers"
           End With
           If SQLConnection.TryConn(Conn) = True Then
               Const Query As String = "SELECT * FROM users WHERE Username = @username AND Password = @password"
               ' The line below fixes the error.
               Using sql As MySqlCommand = New MySqlCommand(Query, Conn.Conn)
                   sql.Parameters.AddWithValue("@username", Me.UsernameTextBox.Text.Trim())
                   sql.Parameters.AddWithValue("@password", Me.PasswordTextBox.Text.Trim())
                   Using reader As MySqlDataReader = sql.ExecuteReader
                       Dim count As Integer
                       count = 0
                       While reader.Read
                           count = count + 1
                       End While
                       If count = 1 Then
                           Me.Hide()
                           sqlloading.Show()
                       ElseIf count > 1 Then
                           errorform.FlatAlertBox1.Text = "Username and password are duplicate"
                           errorform.Show()
                       Else
                           errorform.FlatAlertBox1.Text = "Wrong username or password"
                           errorform.Show()
                       End If
                       Me.Hide()
                   End Using
               End Using
           End If
       End Using
   Catch
       ' at least log the error?
   End Try
   

This will solve you immediate problems, but there is room left for improvement. Instead of formatting your SQL yourself like this:

Query = String.Format(
    "SELECT * FROM users WHERE Username = '{0}' AND Password = '{1}'", 
    Me.UsernameTextBox.Text.Trim(), Me.PasswordTextBox.Text.Trim())

You should really use SQL parameters to prevent SQL injection attacks, refer to the Little Bobby Tables story for reasons why. I have updated the code snippet above to improve this.

A final remark: you are now storing passwords as unencrypted plain text in your database. That is considered to be not secure. Passwords should always be stored and transmitted across the wire in an encrypted format.

Answer 2

I added the whole function from the login form to the SQLConnection class Then Called it using:

Try
    Dim Conn As New sqltryclass
    With Conn
        .Server = "localhost"
        .Password = ""
        .User = "root"
        .Database = "adminapp"
    End With
    If sqltryclass.TryLogin(Conn) = True Then
        Me.Hide()
        sqlloading.Show()
    Else
    End If
Catch
End Try

Answer 3

Your Dim Conn As New SQLConnection is using the SQLConnection class.

If you're using MySQL, try have a look here.