title: "Fatal error encountered during command execution" I'm using SET and SELECT xxx FROM (SELECT xxx FROM xxx)
body: <p>I have this code:</p>
  
  <pre><code>Function Get_Control_Station_Address(counter As Integer)
      Check_DB_Con() 'Check if the connection is okay
      SQL_Query = "SET @row_number = 0; " _
                  &amp; "SELECT hardware_add " _
                  &amp; "FROM (" _
                      &amp; "SELECT " _
                      &amp; "@row_number:=@row_number + 1 AS num, " _
                      &amp; "hardware_add AS hardware_add " _
                      &amp; "FROM teller_info" _
                  &amp; ") AS sub_query " _
                  &amp; "WHERE num = " &amp; counter &amp; ";"
  
      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: " &amp; myerror.Message)
          Return Nothing
      Finally
          MysqlConn.Close()
          MysqlConn.Dispose()
      End Try
  End Function
  </code></pre>
  
  <p>I am receiving this error:</p>
  
  <pre><code>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.
  </code></pre>
  
  <p>I am sure that there's <strong><code>no basic errors</code></strong> here like <code>database connection error</code>, 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.</p>
  
  <pre><code>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: " &amp; myerror.Message)
      Finally
          MysqlConn.Close()
          MysqlConn.Dispose()
      End Try
  End Function
  </code></pre>
  
  <p>So looking close at the problem it seems that this code is the root of error.</p>
  
  <pre><code>SQL_Query = "SET @row_number = '0'; " _
                  &amp; "SELECT hardware_add " _
                  &amp; "FROM (" _
                      &amp; "SELECT " _
                      &amp; "@row_number:=@row_number + 1 AS num, " _
                      &amp; "hardware_add AS hardware_add " _
                      &amp; "FROM teller_info" _
                  &amp; ") AS sub_query " _
                  &amp; "WHERE num = '" &amp; counter &amp; "';"
  </code></pre>
  
  <p>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 <strong><code>SET @row_number = '0';</code></strong> wrong?</p>
  
  <p><strong>In Summary:</strong></p>
  
  <ol>
  <li>I am sure that it is not MySQL connection problem</li>
  <li>I am sure that the MySQL query is working (by testing it directly on HeidiSQL)</li>
  <li>The process of getting data seems to be working because I just copy the way it gets data from a working function.</li>
  </ol>
  
  <p><strong>Edit:</strong> By following @Ken_White's comment of commenting <code>console.writeline</code> I am able to see clearly the error</p>
  
  <p>Here's the error</p>
  
  <pre><code>    MySql.Data.MySqlClient.MySqlException (0x80004005): Fatal error encountered during command execution. ---&gt; 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
  </code></pre>