By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,197 Members | 1,057 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,197 IT Pros & Developers. It's quick & easy.

Trouble executing MYSQL prepared statement in VB.NET

P: 46
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:
Expand|Select|Wrap|Line Numbers
  1. SET @sql = NULL;
  2. SELECT GROUP_CONCAT(DISTINCT CONCAT('max(case when activeexamrecords.subjectid = ''',
  3.   activeexamrecords.subjectid,''' then totalscore end) AS `',
  4.   subjectname, '`'))INTO @sql FROM activeexamrecords JOIN Subjectlist ON Subjectlist.subjectid = activeexamrecords.subjectid;
  5. SET @sql = CONCAT('SELECT activeexamrecords.studentnumber,indexnumber,classname, ', @sql, ' 
  6.               FROM activeexamrecords JOIN studenttable ON activeexamrecords.studentnumber = studenttable.studentnumber 
  7.               GROUP BY studentnumber');
  8. PREPARE stmt FROM @sql; 
  9. EXECUTE stmt; 
In VB.NET this is what I have done and is giving me an error.
Expand|Select|Wrap|Line Numbers
  1. Dim strsql As String
  2.     Dim ds3 As New DataSet
  4.     strsql = "SET @sql = NULL;" & _
  5.              " SELECT GROUP_CONCAT(DISTINCT CONCAT('max(case when activeexamrecords.subjectid = '''," & _
  6.             " activeexamrecords.subjectid,''' then totalscore end) AS `'," & _
  7.             " subjectname, '`'))INTO @sql FROM activeexamrecords JOIN Subjectlist ON Subjectlist.subjectid = activeexamrecords.subjectid;" & _
  8.             " SET @sql = CONCAT('SELECT activeexamrecords.studentnumber,indexnumber,classname, ', @sql, '" & _
  9.             " FROM activeexamrecords JOIN studenttable ON activeexamrecords.studentnumber = studenttable.studentnumber" & _
  10.             " GROUP BY studentnumber');" & _
  11.             " PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;"
  13.     Try
  14.         Using myconn As New MySqlConnection(myconnstring)
  15.             Using sqlconn As New MySqlCommand
  16.                 With sqlconn
  17.                     .Connection = myconn
  18.                     .CommandText = strsql
  19.                     .CommandType = CommandType.Text
  20.                 End With
  21.                 da = New MySqlDataAdapter(sqlconn)
  22.                 ds3.Clear()
  23.                 If (da.Fill(ds3, "searchdata3")) Then
  24.                     DataGrid2.DataSource = ds3.Tables(0)
  25.                 Else
  26.                     MsgBox("No Matches were found according to your search Criteria", vbExclamation, "No record Found")
  27.                 End If
  28.             End Using
  29.         End Using
  30.     Catch ex As MySqlException
  31.         MessageBox.Show(ex.Message, "Mysql Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
  32.     Finally
  33.         myconn.Dispose()
  34.     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.
Jan 14 '16 #1

✓ answered by Luk3r

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

Share this Question
Share on Google+
4 Replies

P: 299
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.
Jan 14 '16 #2

P: 46
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, "searchdata3")) Then DataGrid2.DataSource = ds3.Tables(0)
The error is : "Fatal error encountered during command execution." It is a mysql error. 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.
Jan 14 '16 #3

P: 299
Try adding "Allow User Variables=true" to your connection string.
Jan 14 '16 #4

P: 46
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.
Jan 15 '16 #5

Post your reply

Sign in to post your reply or Sign up for a free account.