“命令执行期间遇到致命错误”我正在使用SET和SELECT xxx FROM (SELECT xxx FROM xxx)
“命令执行期间遇到致命错误”我正在使用SET和SELECT xxx FROM (SELECT xxx FROM xxx)
提问于 2015-08-28 09:56:27
我有这样的代码：
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我收到此错误：
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.我确信这里有像database connection error一样的no basic errors，作为一个证据，我有这个函数，我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所以仔细看看这个问题，似乎这段代码就是错误的根源。
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 & "';"实际上，我直接使用heidiSQL重新创建了查询，它工作得很好，所以我有点卡在这里了，也许我用错了SET @row_number = '0';？
摘要中的：
- 我确信这不是MySQL连接问题
- 我确信MySQL查询正在工作(通过直接在HeidiSQL)
- The上测试它获取数据的过程似乎正在工作，因为我只是复制了它从工作函数中获取数据的方式。
编辑：通过关注@Ken_关于评论console.writeline的评论，我能够清楚地看到错误
错误是这样的
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回答 3
Stack Overflow用户
回答已采纳
发布于 2015-08-28 10:59:56
似乎通过将 Allow User Variables = 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
End Sub我添加了Allow User Variables=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 SubStack Overflow用户
发布于 2016-04-07 07:50:00
使用交叉联接，而不是在两步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 Overflow用户
发布于 2019-03-19 03:45:14
在连接字符串中添加ConnectionTimeout和默认命令超时修复了这个问题。
例如：
ConnectionTimeout=300000; default command timeout=300000;页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/32262184
复制相关文章
相似问题