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?

Remy Lebeau · Accepted Answer · 2009-07-02 23:37:35Z

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.

answered Jul 2, 2009 at 23:37

Remy Lebeau

609k36 gold badges516 silver badges875 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

John M Over a year ago

Thanks for the clarification Remy. What is confusing me is that for a UPDATE query the ?id syntax is used but for the DELETE query the ?id doesn't work.

Remy Lebeau Over a year ago

The syntax should be the same for both, as it is based on what SQL provider is being used, not what kind of SQL statement is being executed.

John M · Accepted Answer · 2009-07-07 20:19:41Z

0

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"

answered Jul 7, 2009 at 20:19

John M

14.7k29 gold badges95 silver badges143 bronze badges

Collectives™ on Stack Overflow

ASP .NET with MySQL - DeleteCommand syntax not working

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related