Trouble executing MYSQL prepared statement in VB.NET

[yashuaking]

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;

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

Can someone help me out? I suspect the problem comes from how VB.Net Treats @Sql but do not know how to deal with it.

[Luk3r]

What is the full error that you receive? I don't see where you're actually executing your commands, opening/closing the MYSQL connection, or creating a reader to actually read the results of the executed commands.

[yashuaking]

The error has to do with the @sql you know Vb.Net the @ symbol is used for parameterized queries. So there should be a way to by pass it. The Error is occurs at this line:If (da.Fill(ds3, "searchdata 3")) Then DataGrid2.DataS ource = ds3.Tables(0)
The error is : "Fatal error encountered during command execution." It is a mysql error.

Vb.net does not even send the command to MYSQL to be processed because of the @ symbol that precedes the sql but the command works fine in mysql and workbench.

[Luk3r]

Try adding "Allow User Variables=true" to your connection string.

[yashuaking]

Wow! I have not tried it yet but I can see it would work. This is the best work around. This is just what I needed! Thank you Luk3r. I have been looking for a way to escape the @ symbol and this is it.