ASP .NET with MySQL - DeleteCommand syntax not working

Question

Working on setting up a SqlDataSource connecting MySQL database to a ASP .NET GridView.

The Delete Command is:

DeleteCommand="DELETE FROM troutetracking WHERE id=?id"

The Delete parameter is:

<DeleteParameters>
     <asp:Parameter Name="id"  Type="Int32" />
</DeleteParameters>

When I run a delete link on the Gridview the following error message appears:

MySql.Data.MySqlClient.MySqlException: Parameter '?id' must be defined.

What is the proper syntax?

Answer 1

That is covered in MSDN's documentation:

Because different database products use different varieties of SQL, the syntax of the SQL string depends on the current ADO.NET provider being used, which is identified by the ProviderName property. If the SQL string is a parameterized query or command, the syntax of the parameter also depends on the ADO.NET provider being used. For example, if the provider is the System.Data.SqlClient, which is the default provider for the SqlDataSource class, the syntax of the parameter is '@parameterName'. However, if the provider is set to the System.Data.Odbc or System.Data.OleDb, the placeholder of the parameter is '?'. For more information about parameterized SQL queries and commands, see Using Parameters with the SqlDataSource Control.

Answer 2

This blog ran into a similar issue:

link text

What I needed to do was update the connection to string to contain "Allow User Variables=True".

I did this by using the 'Advanced' button when setting up the data source.

The delete query also needed to be tweaked to be:

DeleteCommand="DELETE FROM troutetracking WHERE id=@id"

Answer 3

Now desperate:

"...What I needed to do was update the connection to string to contain "Allow User Variables=True"..."

Please, carefully, in concise grammar for complete idiots, please can you explain the above Answer? I do not understand.

Thanks