ASP.NET / MySQL
Using Variables in a MySQL query.
After installing VWD or Visual Studio 2008 and attempting to use it with MySQL, the very first thing you will run into is that Microsoft designed both programs with Microsoft SQL server in mind. Of course, Microsoft wants to sell MS SQL licenses, they want to make money. But Microsoft seems to go out of its way to make MySQL, a billion dollar database program with millions of users, almost unusable with asp.net. At the least, it is frustrating enough that a person may give up on using MySQL and switch to MS SQL, which is exactly what Microsoft wants, I guess.
I have no problem with Microsoft wanting to make money. What I have a problem with is Microsoft supports ODBC, and given that there is an ODBC MySQL client, then Microsoft should create its software to work correctly with any ODBC compliant database. After all, that was one of the purposes of ODBC.
Using MySQL easily with VWD or VS2008 would have been trivial for Microsoft. It is this behavior by Microsoft that has generated much hate for it. Personally, I think Microsoft's software would be much easier to swallow if they weren't constantly trying to cram it down my throat.
But it is possible to use Visual Web Developer or Visual Studio 2008 with MySQL. Here are the tricks:
Here is a working SQL query for Microsoft SQL:
SelectCommand="SELECT [ProductID], [ProductName], [CategoryID] FROM [Products] WHERE ([CategoryID] = @CategoryID)"
To adapt this to MySQL, it would have to become:
SelectCommand="SELECT ProductID, ProductName, CategoryID FROM Products WHERE (CategoryID = @CategoryID)"
But notice the SelectCommand contains a variable: @CategoryID. MySQL will return an error on the @ sign. A working variable in a MySQL query would look like this:
(CategoryID = ?) or (CategoryID = ?CategoryID)
Note that while a single question mark will work as a variable, it will not work if more than one variable is needed in the query. The second example of "?CategoryID" would be the more correct way of using variables in a MySQL query.
Now let's say that we are setting a variable in asp.net, but this value needs to be concatenated in order to actually use it in a query. For example, we set a variable from a list like 123456, but need to actually use it as part of a query in a format like this, "123456-1, or "_123456"
What we would need to do is use the CONCAT command. The way to do the above examples would be like this:
WHERE CategoryID=CONCAT(?,'-1') OR CategoryID=CONCAT('_',?)
or WHERE CategoryID=CONCAT(?CatergoryID,'-1') OR CategoryID=CONCAT('_',?CategoryID)
A little playing with the above examples will show that it is possible to get them to work.
But while it will work, there a few places that it is impossible to use CONCAT in a MySQL query. In fact, there are some places it is not even possible to use a variable in the query. Here are a few of those places:
Select * FROM (CONCAT("_",?CategoryID)SELECT * FROM tbl LIMIT ?Var1
If a situation arises where CONCAT would be needed after a FROM command, see this example of how to handle it.
Tags: asp.net 3.5, Visual Basic, VB, MySQL 5.1, MySQL .net connector, Variables, CONCAT, LIMIT X, LIMIT @VAR1, LIMIT ?VAR1