Error when getting MySQL data in .NET

Question

I'm creating a .NET web application that retrieves data from a database. I keep getting the following error when running the below code though.

Fatal error encountered during command execution.

The InnerException is {"Parameter '?Username' must be defined."}, but I can see that ?Username is defined and I know that Session("Username") is correct and exists.

Here is the code. It fails on the SupervisorNameAdapter.Fill(SupervisorNameData, "Data") line.

Dim SupervisorID As String = Session("Username")

    Dim qryGetSupervisorName As String = "select * from USERS where UserID = ?Username"

    Using cn As New MySqlConnection(ConfigurationManager.ConnectionStrings("ConnectionInfo").ConnectionString), cmd As New MySqlCommand(qryGetSupervisorName, cn)

        cmd.Parameters.AddWithValue("?Username", SupervisorID)

        cn.Open()
        cmd.ExecuteNonQuery()
        cn.Close()

        Dim SupervisorNameAdapter As New MySqlDataAdapter(qryGetSupervisorName, cn)
        Dim SupervisorNameData As New DataSet
        SupervisorNameAdapter.Fill(SupervisorNameData, "Data")

        If SupervisorNameData.Tables(0).Rows.Count = 0 Then

            MsgBox("An error has occured. Please refresh the page.", vbOKOnly, "Error!")

        Else

            SupervisorName.Text = SupervisorNameData.Tables(0).Rows(0).Item(1) & " " & SupervisorNameData.Tables(0).Rows(0).Item(2)

        End If

    End Using

Does anyone know why this is happening?

Answer 1

You are not setting correctly the command to be used by the MySqlAdapter. You just pass the command text string and with that string the adapter build another command that is missing the required parameter

Just change your code to

Dim SupervisorID As String = Session("Username")
Dim qryGetSupervisorName As String = "select * from USERS where UserID = ?Username"

Using cn As New MySqlConnection(ConfigurationManager.ConnectionStrings("ConnectionInfo").ConnectionString), cmd As New MySqlCommand(qryGetSupervisorName, cn)

    cmd.Parameters.AddWithValue("?Username", SupervisorID)
    Dim SupervisorNameAdapter As New MySqlDataAdapter(cmd)
    Dim SupervisorNameData As New DataSet
    ....

End Using