VWD + ASP.NET + MySql + parameterized queries

As you'll know, MS-SQL-Server uses parameter names beginning with '@' in parameterized queries, whilst MySql uses a single '?' for each parameter.

Whilst struggling to get Visual Web Designer 2010 Express to recognise parameterized MySql queries, I hit on the following technique:

• Build and test the MySql connection in the usual way
• using /ODBC version as /Net version is incompatible with Express
• Load the .xsd file in the designer (or add new item "dataset" if necessary)
• Build and test your MySql query in the usual way, with no parameters
• Add another query, and give it a hard coded parameter (e.g. WHERE id = 'x')
• Save the query and exit the designer
• Right-click on the .xsd file and choose "Open with" > "Source Code (Text) Editor"
• Find the appropriate TableAdapter part of the xml file
• the main query will be a DbSource tag within MainSource tag
• the secondary queries will be DbSource tags within the Sources tag
• Edit the sql code in the CommandText tag, changing the 'x' to be a single ? with no quotes, eg
• SELECT ... FROM ... WHERE id = ?
• Add a Parameters tag immediately after the closing CommandText tag and then add a Parameter tag to look something like this:
• <Parameters>
• <Parameter AllowDbNull="false" AutogeneratedName="?search" ColumnName="" DataSourceName="" DataTypeServer="varchar" DbType="AnsiString" Direction="Input" ParameterName="?search" Precision="0" Scale="0" Size="1024" SourceColumn="" SourceColumnNullMapping="false" SourceVersion="Current" />
• Add other parameters as required. Each one needs a separate ? in the WHERE clause and they are substituted in the same order
• </Parameters>
• Note: you may need to change some of the attributes to suit your purpose
• Now save the .xsd file and exit
• Reload it in the designer in the normal way
• When you preview the data, it should ask for a parameter(s) and handle it correctly

Voilà, mission accomplished!