Bug #66060 "Parameter '?' must be defined" error, when using unnamed parameters.
Submitted: 27 Jul 2012 22:35 Modified: 28 Sep 2012 19:01
Reporter: Todd Powers Email Updates:
Status: Closed Impact on me:
None 
Category:Connector / NET Severity:S1 (Critical)
Version:6.5.4 OS:Microsoft Windows (Server 2003)
Assigned to: Gabriela Martinez Sanchez

[27 Jul 2012 22:35] Todd Powers
Description:

.ExecuteNonQuery() returns "Parameter '?' must be defined." error, when attempting to execute the query, "insert into table_name (Field1, Field1) VALUES(?,?)"

Exception received:

[MySqlException (0x80004005): Parameter '?' must be defined.]
   MySql.Data.MySqlClient.Statement.SerializeParameter(MySqlParameterCollection parameters, MySqlPacket packet, String parmName) +160
   MySql.Data.MySqlClient.Statement.InternalBindParameters(String sql, MySqlParameterCollection parameters, MySqlPacket packet) +357
   MySql.Data.MySqlClient.Statement.BindParameters() +50
   MySql.Data.MySqlClient.PreparableStatement.Execute() +28
   MySql.Data.MySqlClient.MySqlCommand.ExecuteReader(CommandBehavior behavior) +948

How to repeat:

1. Create form with two text boxes and a button (txtField1, txtField2, & btnAdd)

2. Use code below to implement button handler and SQL interface.

3. Modify connection information and table/field names in provided code, for your environment.

C# Button Click handler which excercises the functionality:
============================================================
protected void btnAdd_Click(object sender, EventArgs e) {
        SQLSystem sql = new SQLSystem();
        sql.insertIntoSQL("insert into table_name (Field1, Field2) VALUES(?,?)", txtField1.Text, txtField2.Text);
    }

C# SQLSystem class which duplicates this issue.
=====================================================

using System;
using System.Data;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;

using MySql.Data.MySqlClient;

public class SQLSystem {

    private MySqlConnection conn;

    public SQLSystem() {
        try {
            conn = new MySqlConnection();

            conn.ConnectionString = "SERVER=********;" +
                "PORT=######;" +
                "DATABASE=*******;" +
                "UID=********;" +
                "PASSWORD=*******;" +
                "Allow User Variables=True;";

        } catch (Exception e) {
            throw new Exception("Error creating instance of SQLSystem.", e);
        }
    }

    public DataSet runQuery(String SQL) {

        DataSet ds = new DataSet();

        try {
            MySqlDataAdapter data = new MySqlDataAdapter(SQL, conn);
            conn.Open();
            data.Fill(ds);
            Close();

        } catch (Exception e) {
            throw new Exception("Error running query.", e);
        } 
        
        return ds;
        
    }

    public void insertIntoSQL(params string[] args) {
        try {
            MySqlCommand command = conn.CreateCommand();
            command.CommandText = args[0];

            int x = args.GetLength(0);
            for (int i = 1; i < x; i++) {
                command.Parameters.Add(new MySqlParameter("", args[i]));
            }

            conn.Open();
            command.ExecuteNonQuery();
            Close();

        } catch (MySqlException e) {
            throw new Exception("Error executing insert sql statement.", e);
        }
    }

    public void Close() {
        try {
            conn.Close();
        } catch (Exception e) {
            throw new Exception("Error closing connection.", e);
        }
    }

}

Suggested fix:
Fix the provider so that unnamed parameters work properly without throwing the error.
[11 Sep 2012 17:55] Gabriela Martinez Sanchez
A fix has been commited and pushed to the following branches : 6.4.x, 6.5.x,
6.6.x. This code will be release in the upcoming versions 6.4.6, 6.5.5,
6.6.3.
[28 Sep 2012 19:01] John Russell
Added to changelog for 6.4.6, 6.5.5, 6.6.3: 

The ExecuteNonQuery() could return an error Parameter '?' must be
defined, when attempting to execute a statement such as: insert into
table_name (Field1, Field1) VALUES(?,?) That is, when referencing the
same field twice with two different ? placeholders.