A connection attempt failed because the connected party did not properly respond after a period of time - VB

Question

I am using An XAMPP locally hosted MySQL server with my login database on. I am trying to compare the entries in the database to the inputs of the TextBoxes in the vb program. However, upon clicking the button the program freezes.

Imports MySql.Data.MySqlClient
Public Class Login
    Private Sub Login_Load(sender As Object, e As EventArgs) Handles MyBase.Load
        'TODO: This line of code loads data into the 'Parts.User' table. You can move, or remove it, as needed.
        'Me.UserTableAdapter.Fill(Me.Parts.User)

    End Sub
    Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
        If TextBox1.Text = "" Or TextBox2.Text = "" Then
            MsgBox("Oops ¯\_(ツ)_/¯ " + Err.Description(), MsgBoxStyle.OkOnly, "Enter Value")
        Else
            Try
                Dim connectionString = "server=localhost; userid=root; password=; database=partstest1; CharSet=utf8;"
                Dim connDB = New MySqlConnection(connectionString)
                Try
                    connDB.Open()
                    Dim objCmd = New MySqlCommand
                    objCmd.Connection = connDB
                    objCmd.CommandText = "SELECT ID, Username, Password FROM `user` WHERE `Username` = '" + TextBox1.Text + "' and `Password` = '" + TextBox2.Text + "';"
                    objCmd.CommandType = CommandType.Text
                    Dim objAdpt = New MySqlDataAdapter
                    Dim objDS = New DataSet
                    objAdpt.SelectCommand = objCmd
                    objAdpt.Fill(objDS)
                    Console.WriteLine(objDS)
                    If TextBox1.Text = "admin" Then
                        'If Access_Level = 0 Then
                        Me.Hide()
                        AdminMainMenu.Show()
                    Else
                        Me.Hide()
                        MainMenu.Show()
                    End If
                Catch
                    MsgBox("Oops " + Err.Description(), MsgBoxStyle.OkOnly, "Failed to Open")
                    MsgBox("Incorrect login details", MsgBoxStyle.OkOnly)

                End Try
            Catch ex As System.Exception
                System.Windows.Forms.MessageBox.Show(ex.Message)
            End Try
        End If
        TextBox1.Clear()
        TextBox2.Clear()
    End Sub
End Class

I tried putting the TextBox.Text equal to a different variable but it had not made a difference. Is my command too complex or have I formatted it wrong maybe? I am pretty new to SQL so any help would be appreciated.

Answer 1

This will perform the database operations off the UI, and moves the UI elements out of the database operations. It uses Async / Await

Private Async Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
    If TextBox1.Text = "" Or TextBox2.Text = "" Then
        MsgBox("Oops ¯\_(ツ)_/¯ " + Err.Description(), MsgBoxStyle.OkOnly, "Enter Value")
    Else
        Try
            Await getDataSet(TextBox1.Text, TextBox2.Text)
        Catch ex As Exception
            'MsgBox("Oops " + Err.Description(), MsgBoxStyle.OkOnly, "Failed to Open")
            'MsgBox("Incorrect login details", MsgBoxStyle.OkOnly)
            System.Windows.Forms.MessageBox.Show(ex.Message)
        End Try
        If TextBox1.Text = "admin" Then
            'If Access_Level = 0 Then
            Me.Hide()
            AdminMainMenu.Show()
        Else
            Me.Hide()
            MainMenu.Show()
        End If
    End If
    TextBox1.Clear()
    TextBox2.Clear()
End Sub

Async Function getDataSet(username As String, password As String) As Task(Of DataSet)
    Return Await Task.Factory.StartNew(
        Function()
            Dim connectionString = "server=localhost; userid=root; password=; database=partstest1; CharSet=utf8;"
            Dim commandText = "SELECT ID, Username, Password FROM `user` WHERE `Username` = '" & username & "' and `Password` = '" & password & "';"
            Using connDB = New MySqlConnection(connectionString), objCmd = New MySqlCommand(), objAdpt = New MySqlDataAdapter()
                connDB.Open()
                objCmd.Connection = connDB
                objCmd.CommandText = commandText 
                objCmd.CommandType = CommandType.Text
                objAdpt.SelectCommand = objCmd
                Dim objDs = New DataSet()
                objAdpt.Fill(objDs)
                Console.WriteLine(objDs)
                Return objDs
            End Using
        End Function)
End Function

I'm not going to address your potential SQL Injection issue. But you can educate yourself if you want to.

If your database connection is timing out then you need to fix your connection string, I guess. I don't think anyone can help you with that without being on site.

This answer will help your application not freeze.

Your application is suffering from not following Separation of Concerns. By not doing so, your long-running tasks are being performed on the UI. The Async / Await here is a way to offload some of the database processing. But you should also understand that zero non-UI processing should be performed on the UI. Keep that in mind and find ways to comply (Async / Await, BackgroundWorker, System.Threading.Thread)