MySQL Forums :: MySQL for Visual Studio :: Trouble executing MYSQL prepared statement in VB.NET


Advanced Search

Trouble executing MYSQL prepared statement in VB.NET
Posted by: Joshua Ahianyo ()
Date: January 14, 2016 04:06AM

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;

This is how I coded it in Vb.net

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.

Options: ReplyQuote


Subject Views Written By Posted
Trouble executing MYSQL prepared statement in VB.NET 1208 Joshua Ahianyo 01/14/2016 04:06AM
Re: Trouble executing MYSQL prepared statement in VB.NET 656 Luis Penunuri 01/15/2016 12:26PM
Re: Trouble executing MYSQL prepared statement in VB.NET 550 Joshua Ahianyo 01/15/2016 12:38PM
Re: Trouble executing MYSQL prepared statement in VB.NET 458 Shay _Fox 02/08/2016 01:18PM
Re: Trouble executing MYSQL prepared statement in VB.NET 470 Mark Zuekarly 02/22/2016 05:25AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.