"Fatal error encountered during command execution" I'm using SET and SELECT xxx FROM (SELECT xxx FROM xxx)

Question

I have this code:

Function Get_Control_Station_Address(counter As Integer)
    Check_DB_Con() 'Check if the connection is okay
    SQL_Query = "SET @row_number = 0; " _
                & "SELECT hardware_add " _
                & "FROM (" _
                    & "SELECT " _
                    & "@row_number:=@row_number + 1 AS num, " _
                    & "hardware_add AS hardware_add " _
                    & "FROM teller_info" _
                & ") AS sub_query " _
                & "WHERE num = " & counter & ";"

    Dim MySQL_CMD As New MySqlCommand(SQL_Query, MysqlConn)

    Try
        MySQL_CMD.Connection.Open()
        MySQL_Reader = MySQL_CMD.ExecuteReader()

        While MySQL_Reader.Read
            MySQL_Result = MySQL_Reader("hardware_add")
        End While

        Return MySQL_Result
        MySQL_Reader.Close()
    Catch myerror As MySqlException
        Console.WriteLine("Failed to run query: " & myerror.Message)
        Return Nothing
    Finally
        MysqlConn.Close()
        MysqlConn.Dispose()
    End Try
End Function

I am receiving this error:

A first chance exception of type 'MySql.Data.MySqlClient.MySqlException' occurred in MySql.Data.dll
A first chance exception of type 'MySql.Data.MySqlClient.MySqlException' occurred in MySql.Data.dll
A first chance exception of type 'MySql.Data.MySqlClient.MySqlException' occurred in MySql.Data.dll
A first chance exception of type 'MySql.Data.MySqlClient.MySqlException' occurred in MySql.Data.dll
A first chance exception of type 'MySql.Data.MySqlClient.MySqlException' occurred in MySql.Data.dll
A first chance exception of type 'MySql.Data.MySqlClient.MySqlException' occurred in MySql.Data.dll
Failed to run query: Fatal error encountered during command execution.

I am sure that there's no basic errors here like database connection error, as a proof I have this function that I am 100% sure working and it is almost the same to the previous function which is not working, the only difference is the query.

Function Get_Control_Station_Total()
    Check_DB_Con() 'Check if the connection is okay
    SQL_Query = "SELECT COUNT(*) AS total_count FROM teller_info"
    Dim MySQL_CMD As New MySqlCommand(SQL_Query, MysqlConn)

    Try
        MySQL_CMD.Connection.Open()
        MySQL_Reader = MySQL_CMD.ExecuteReader()

        While MySQL_Reader.Read
            MySQL_Result = MySQL_Reader("total_count")
        End While

        Return MySQL_Result
        MySQL_Reader.Close()
    Catch myerror As MySqlException

        Return ("Failed to run query: " & myerror.Message)
    Finally
        MysqlConn.Close()
        MysqlConn.Dispose()
    End Try
End Function

So looking close at the problem it seems that this code is the root of error.

SQL_Query = "SET @row_number = '0'; " _
                & "SELECT hardware_add " _
                & "FROM (" _
                    & "SELECT " _
                    & "@row_number:=@row_number + 1 AS num, " _
                    & "hardware_add AS hardware_add " _
                    & "FROM teller_info" _
                & ") AS sub_query " _
                & "WHERE num = '" & counter & "';"

Actually I re-create that query directly using heidiSQL and it working great, so I am a little bit stuck here, maybe I am using SET @row_number = '0'; wrong?

In Summary:

1. I am sure that it is not MySQL connection problem
2. I am sure that the MySQL query is working (by testing it directly on HeidiSQL)
3. The process of getting data seems to be working because I just copy the way it gets data from a working function.

Edit: By following @Ken_White's comment of commenting console.writeline I am able to see clearly the error

Here's the error

    MySql.Data.MySqlClient.MySqlException (0x80004005): Fatal error encountered during command execution. ---> MySql.Data.MySqlClient.MySqlException (0x80004005): Parameter '@row_number' must be defined.
   at MySql.Data.MySqlClient.Statement.SerializeParameter(MySqlParameterCollection parameters, MySqlPacket packet, String parmName, Int32 parameterIndex)
   at MySql.Data.MySqlClient.Statement.InternalBindParameters(String sql, MySqlParameterCollection parameters, MySqlPacket packet)
   at MySql.Data.MySqlClient.Statement.BindParameters()
   at MySql.Data.MySqlClient.PreparableStatement.Execute()
   at MySql.Data.MySqlClient.MySqlCommand.ExecuteReader(CommandBehavior behavior)
   at MySql.Data.MySqlClient.MySqlCommand.ExecuteReader(CommandBehavior behavior)
   at MySql.Data.MySqlClient.MySqlCommand.ExecuteReader()
   at TCP_Client.Module_Database.Get_Control_Station_Address(Int32 counter) in C:\Users\xxxxx\xxx.vb:line 198

Answer 1

It seems that by adding Allow User Variables = True to my database connection solves my problem.

So instead of

Public Sub Check_DB_Con()
    'Declare database credentials so we can refresh it in recheck timer
    server = My.Settings.DB_Server
    username = My.Settings.DB_Username
    password = My.Settings.DB_Password
    database = My.Settings.DB_Database

    'Connection String
    MysqlConn.ConnectionString = "server=" & server & ";" _
    & "user id=" & username & ";" _
    & "password=" & password & ";" _
    & "database=" & database 
End Sub

I add Allow User Variables=True, so it will become

Public Sub Check_DB_Con()
    'Declare database credentials so we can refresh it in recheck timer
    server = My.Settings.DB_Server
    username = My.Settings.DB_Username
    password = My.Settings.DB_Password
    database = My.Settings.DB_Database

    'Connection String
    MysqlConn.ConnectionString = "server=" & server & ";" _
    & "user id=" & username & ";" _
    & "password=" & password & ";" _
    & "database=" & database & ";Allow User Variables=True"
End Sub

Answer 2

Instead of setting the value of the rn in a 2 step SQL, use a cross join. I don't think the call handles the two statements correctly.

 SQL_Query = "SELECT hardware_add " _
            & "FROM (" _
                & "SELECT " _
                & "@row_number:=@row_number + 1 AS num, " _
                & "hardware_add AS hardware_add " _
                & "FROM teller_info" _
                & "CROSS JOIN (SELECT @row_number:=0) AS t " _
            & ") AS sub_query " _
            & "WHERE num = '" & counter & "';"

Stack Example: ROW_NUMBER() in MySQL