mysql - Parameter '@myLeft' must be defined

Question

My question is quite similar to already answered ones but yet not. I'd like some help sorting this out.

I'm trying to add data to a database table but I get keeping the titled error. Here's my MySQL-code:

Dim objCmdInsert As New MySqlCommand("LOCK TABLE threads WRITE;SET @myLeft := lft;SELECT @myLeft FROM threads WHERE threadid = '" & Request.QueryString("id") & "';UPDATE threads SET rgt = rgt + 2 WHERE rgt > @myLeft;UPDATE threads SET lft = lft + 2 WHERE lft > @myLeft;INSERT INTO threads(threadid, category, userid, ipaddress, section, subject, summary, body, tags, postedat, updatedat, rating, status, lft, rgt) VALUES(?threadid, ?category, ?userid, ?ipaddress, ?section, ?subject, ?summary, ?body, ?tags, ?postedat, ?updatedat, ?rating, ?status, @myLeft + 1, @myLeft + 2);UNLOCK TABLES;", objConn)

objCmdInsert.Parameters.Add("?threadid", MySqlDbType.VarChar, 36).Value = Guid.NewGuid.ToString()
objCmdInsert.Parameters.Add("?category", MySqlDbType.VarChar, 36).Value = "articlecomment"
objCmdInsert.Parameters.Add("?ipaddress", MySqlDbType.VarChar, 20).Value = HttpContext.Current.Request.ServerVariables("REMOTE_ADDR")
objCmdInsert.Parameters.Add("?section", MySqlDbType.VarChar, 36).Value = DBNull.Value
objCmdInsert.Parameters.Add("?userid", MySqlDbType.VarChar, 36).Value = GetStubs.GetUserGuid(HttpContext.Current.User.Identity.Name.ToString())
objCmdInsert.Parameters.Add("?subject", MySqlDbType.VarChar, 500).Value = DBNull.Value
objCmdInsert.Parameters.Add("?summary", MySqlDbType.Text).Value = DBNull.Value
objCmdInsert.Parameters.Add("?body", MySqlDbType.Text).Value = CommentBody.Text
objCmdInsert.Parameters.Add("?tags", MySqlDbType.VarChar, 500).Value = DBNull.Value
objCmdInsert.Parameters.Add("?postedat", MySqlDbType.DateTime).Value = DateTime.Now.ToString()
objCmdInsert.Parameters.Add("?updatedat", MySqlDbType.DateTime).Value = DateTime.Now.ToString()
objCmdInsert.Parameters.Add("?rating", MySqlDbType.Int32).Value = "0"
objCmdInsert.Parameters.Add("?status", MySqlDbType.VarChar, 200).Value = "Open"

What am I doing wrong?

Answer 1

I found the solution to this problem by making an extensive Google search for undefined parameters in MySQL. This article (MySql.Data.MySqlClient.MySqlException: Parameter ‘@id’ must be defined) covers the basics of the problem. It's as far as I understand related to an upgrade of the connector.

You need to add Allow User Variables=True in the connection string in order to use custom variables. Pretty simple solution to an equally tough problem. ;)