MySQL Query Execution with Visual Basic

Question

I am attempting to make a connection to MySQL server, take input from a text box, the use the information to run query and store it in a listbox.

To go even further I would like to and make the listbox update as I type in the text box.

Whenever I run run the vb nothing populates. I even tried changing the query to something basic, select * from securePasswordList.users; And I can't get anything to populate.

Any suggestions or criticism? THanks! Still learning quite a bit about VB...and a long ways to go.

  Imports MySql.Data.MySqlClient

Public Class Reveal
    Public Property MyDataClass As SecurePasswordList
    Dim lastNameInput As String
    Dim firstNameInput As String
    Dim connStr As String = "server=0.0.0.0;user=johndoe;database=securePasswordList;port=3306;password=password;"
    Dim conn As New MySqlConnection(connStr)
    Dim SQL As String = "select password from securePasswordList.users where LAST_NAME like ' " & lastNameInput & "%'"
Dim cmd As MySqlCommand = New MySqlCommand(SQL, conn)

    Private Sub Reveal_Load(sender As Object, e As EventArgs) Handles MyBase.Load

    End Sub

    Private Sub firstName_TextChanged(sender As Object, e As EventArgs) Handles lastNameTextBox.TextChanged
        lastNameInput = lastNameTextBox.Text
        listBox.Items.Add(cmd)
    End Sub



    Private Sub lastName_TextChanged(sender As Object, e As EventArgs) Handles firstNameTextBox.TextChanged
        firstNameInput = firstNameTextBox.Text
    End Sub

    Private Sub listBox_SelectedIndexChanged(sender As Object, e As EventArgs) Handles listBox.SelectedIndexChanged

    End Sub
 End Class

Answer 1

One of your issues is that you are not actually executing the MySqlCommand anywhere. Also, I would like to point you to this link, since you have a potential security issue in your current code in the way you build your select-string.

Here is an example of how you could retrieve a password:

Private Function RetrievePassword(ByVal lastName As String) As String
    Using conn As New MySqlConnection(connStr)
        Using comm As New MySqlCommand(query, conn)
            comm.Parameters.AddWithValue("@LastName", lastName & "%'")

            Dim result As Object = comm.ExecuteScalar()

            If result Is Nothing Then
                handle as you like...
            End If

            Return result.ToString
        End Using
    End Using
End Function

query would contain a placeholder for the parameter like so:

    Private Const query As String =
         "select password from securePasswordList.users where LAST_NAME like @LastName"