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.
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.