How to use SET @var1
Hello at all,
hope I'm right here. I use the .Net Connector in a VB.net Project and want to create a Tree-Structure(Nested Sets). When I try to create an new entry in the tree-tabel
(Tree-table[PKID, Name, lft, rgt]) I need the parent entry, update lft and rgt an greate the new entry. These are the MySQL Commands wihch works in phpmyadmin:
SET @lft := 0; 'variable to store the original value
SET @rgt := 0; 'variable to store the original value
'store the original vlaue
SELECT lft, rgt INTO @lft, @rgt FROM tree WHERE id='4';
'Update all entrys to create the new one
UPDATE tree SET lft=lft+2 WHERE lft > @rgt;
UPDATE tree SET rgt=rgt+2 WHERE rgt >= @rgt;
'create new entry
INSERT INTO tree(name,lft,rgt) VALUES ('Org', @rgt, @rgt+1);
As I wrote, the commads works within phpmyadmin but not in my vb.net Project.
There a all mysql Commands a string
Dim pre_mySQLCommands As String = "SET @lft := 0; SET @rgt := 0;" & _
"SELECT " & TREE_LFT & ", " & TREE_RGT & " INTO @lft, @rgt FROM " & TREE & " WHERE " & TREE_ID & "='" & refid.tostring() &"';"
pre_mySQLCommands& = " UPDATE " & TREE & " SET " & TREE_LFT & " = " & TREE_LFT & " + 2 WHERE " & TREE_LFT & " > @rgt; " & " UPDATE " & TREE & " SET " & TREE_RGT & " = " & TREE_RGT & " + 2 WHERE " & TREE_RGT & " >= @rgt; "
INSERT INTO TREE (....,TREE_LFT ,TREE_RGT ) VALUES (..., @rgt, @rgt+1" );
Try
Dim Command As New MySqlCommand(pre_mySQLCommands & mySQLCommand, Connection)
Connection.Open()
newPKID = Command.ExecuteNonQuery()
Catch ex As MySqlException
MySQL_Error_Message(System.Reflection.MethodBase.GetCurrentMethod().Name, ex)
Finally
Connection.Close()
End Try
This is the error message I get:
"A first chance exception of type 'MySql.Data.MySqlClient.MySqlException' occurred in MySql.Data.dll"
What am I doing wrong?
Thanks Christian