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'.

Change Dim Conn As New SQLConnection to Dim Conn As New MySqlConnection ? — Alex
– Alex, Commented Apr 24, 2015 at 1:09

Alex · Accepted Answer · 2015-04-25 15:55:11Z

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:

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

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.

A much neater and probably the real way to fix my error instead of the hack method I did. Thanks very much!
@Spike you are welcome. Several more improvements can be made though. I have edited my answer to add a section on the use of SQL parameters.

arifhazwan · Accepted Answer · 2015-04-24 01:16:55Z

0

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

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

answered Apr 24, 2015 at 1:16

arifhazwan

481 silver badge7 bronze badges

2 Comments

Spike Over a year ago

It's using an SQLConnection class I made not "SqlConnection".

arifhazwan Over a year ago

Have you read anything from the link? Change Dim Conn As New SQLConnection to Dim Conn As New MySqlConnection

Spike · Accepted Answer · 2015-04-24 01:50:49Z

0

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

answered Apr 24, 2015 at 1:50

Spike

571 gold badge2 silver badges8 bronze badges

Collectives™ on Stack Overflow

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

3 Answers 3

2 Comments

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related