I found out a funny thing today. Or maybe not so funny when I think about it…

The order page on one of my sites have not been working since early November, almost 6 months!! As I’m sure you understand it’s not a very busy site, but usually I get a few orders per month so I should have noticed this a long time ago…

The site is running on IIS/ASP.NET and MySQL server 5.0.45 using the MySQL .NET Connector and I’m using a SQL statement like this on the page:

START TRANSACTION;
INSERT INTO table1 (companyname,street,city,zip) VALUES (‘XS Tech’,'test’,'test’,’12345′);
SELECT @id:=id FROM table1 where id = last_insert_id();
INSERT INTO table2 (id,contactname) VALUES (@id,’Max”);
COMMIT;

This statement was throwing an exception error: “MySql.Data.MySqlClient.MySqlException: Parameter ‘@id’ must be defined”

Since I could not really remember – ie had not properly documented – what changes I may have done to that page six months ago, it made trouble shooting a bit harder. But to make a long story short(er) – It turns out I had upgraded the MySQL .NET Connector and this was causing the error. The site was using MySql.Data.dll version 5.2.1.0. I tried to upgrade to 6.0.3.0 to no avail. But when downgrading to 5.1.4.0 it started to work just fine again.

Finally, I  found that this was indeed a change in the .Net Connector. (I’m not sure what version number but starting from 5.2.0 would be my guess)
Starting from version 5.2.2 of the Connector you should add the Allow User Variables=True Connection String Setting in order to use User Defined Variables in your SQL statements.

Example of Connection String:

Database=testdb;Data Source=localhost;User Id=root;Password=hello;Allow User Variables=True