无法在VB.NET中执行MYSQL预处理语句 [英] Trouble executing MYSQL prepared statement in VB.NET

问题描述

我有一个MYSQL动态Pivoting预处理语句，可以在MYSQL命令行和Workbench中完美地运行，但是当我在VB.NET中运行代码时会产生致命错误。



这是我的代码：

I have a MYSQL dynamic Pivoting prepared statement that works perfectly in MYSQL commandline and Workbench but when I run the code in VB.NET it gives Fatal Error.

This is my code:

SET @sql = NULL;
SELECT GROUP_CONCAT(DISTINCT CONCAT('max(case when activeexamrecords.subjectid = ''',
  activeexamrecords.subjectid,''' then totalscore end) AS `',
  subjectname, '`'))INTO @sql FROM activeexamrecords JOIN Subjectlist ON Subjectlist.subjectid = activeexamrecords.subjectid;
SET @sql = CONCAT('SELECT activeexamrecords.studentnumber,indexnumber,classname, ', @sql, '
              FROM activeexamrecords JOIN studenttable ON activeexamrecords.studentnumber = studenttable.studentnumber
              GROUP BY studentnumber');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

在VB.NET中是我做了什么，并给我一个错误。

In VB.NET this is what i have done and is giving me an error.

Dim strsql As String
   Dim ds3 As New DataSet

   strsql = "SET @sql = NULL;" & _
            " SELECT GROUP_CONCAT(DISTINCT CONCAT('max(case when activeexamrecords.subjectid = '''," & _
           " activeexamrecords.subjectid,''' then totalscore end) AS `'," & _
           " subjectname, '`'))INTO @sql FROM activeexamrecords JOIN Subjectlist ON Subjectlist.subjectid = activeexamrecords.subjectid;" & _
           " SET @sql = CONCAT('SELECT activeexamrecords.studentnumber,indexnumber,classname, ', @sql, '" & _
           " FROM activeexamrecords JOIN studenttable ON activeexamrecords.studentnumber = studenttable.studentnumber" & _
           " GROUP BY studentnumber');" & _
           " PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;"

   Try
       Using myconn As New MySqlConnection(myconnstring)
           Using sqlconn As New MySqlCommand
               With sqlconn
                   .Connection = myconn
                   .CommandText = strsql
                   .CommandType = CommandType.Text
               End With
               da = New MySqlDataAdapter(sqlconn)
               ds3.Clear()
               If (da.Fill(ds3, "searchdata3")) Then
                   DataGrid2.DataSource = ds3.Tables(0)
               Else
                   MsgBox("No Matches were found according to your search Criteria", vbExclamation, "No record Found")
               End If
           End Using
       End Using
   Catch ex As MySqlException
       MessageBox.Show(ex.Message, "Mysql Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
   Finally
       myconn.Dispose()
   End Try

它给我的错误是：命令执行期间遇到致命错误



请帮忙！

The error that it is giving me is: Fatal error encountered during command execution

Please help!

推荐答案

根据上面的代码，查询似乎有点复杂。因此执行可能需要一些时间。



所以最好明确增加执行时间。



设置' CommandTimeout '属性为一些大值。这可能有助于查询执行超过默认时间。

As per the above code, the query seems to be a little complex. Therefore the execution might take time.

So better increase the execution time explicitly.

set the 'CommandTimeout' property to some big values. This may help the query to execute more than the default time.

我增加了命令限制，但它仍然给我错误。这是我怀疑可能导致的问题：

I increased the commandtimout but it is still giving me the error. This is what I suspect may be causing the problem that is:

SET @sql = NULL

由于vb在参数化查询中识别mysql中的符号@，我怀疑这是问题但是我做了没有解决它。



正如我所说，代码在mysql命令行和工作台中完美运行

Since vb recognizes the symbol @ in mysql in parameterized queries, I suspect that is the problem but I do not have a work around for it.

As I said the code works perfectly in mysql command line and workbench

这篇关于无法在VB.NET中执行MYSQL预处理语句的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！