“在命令执行过程中遇到的致命错误”我使用SET并选择xxx(从xxx选择xxx)

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.

我确信这里没有像数据库连接错误这样的基本错误，作为证明，我有这个函数，我100%确信它可以工作，它与前面的函数几乎是一样的，它不能工作，唯一的区别是查询。

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?

实际上，我直接使用heidiSQL重新创建了那个查询，它运行得很好，所以我有点困在这里，也许我用的是SET @row_number = '0';错了吗?

In Summary:

总而言之:

I am sure that it is not MySQL connection problem
我确信这不是MySQL连接的问题
I am sure that the MySQL query is working (by testing it directly on HeidiSQL)
我确信MySQL查询正在工作(通过在HeidiSQL上直接测试)
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

编辑:遵循@Ken_White的评论控制台。我能很清楚地看到这个错误。

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

2 个解决方案

#1

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

通过向数据库连接添加允许用户变量= True，似乎解决了我的问题。

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

我添加了允许用户变量=True，所以它会变成

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

#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.

不要在2步SQL中设置rn的值，而是使用交叉连接。我认为这个调用不能正确地处理这两个语句。

 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

堆栈示例:ROW_NUMBER()在MySQL中

#1