Bug #23884 Calling Stored Procedure Return Error

• Submitted: 2 Nov 2006 12:10
• Modified: 3 Nov 2006 9:41
• Reporter: Sori Hutagalung
• Status: Can't repeat
• Category: Connector / NET
• Severity: S2 (Serious)
• Version: 1.08
• OS: Windows (Windows 2003)
• CPU Architecture: Any

[2 Nov 2006 12:10] Sori Hutagalung

Description:
when I calling stored procedure from my C# program I found this error :

The Error Message :
1. Error Message : Exception during execution of 'KodingBarang': Parameter '?_kodeKelompok' is not defined
2. Error Number : 0
3. Error Source : MySql.Data

How to repeat:
private void button1_Click(object sender, System.EventArgs e)
		{
			string strKoneksi = "server=localhost;user id=xxx;password=xxx;database=apotik;pooling=false";
			MySqlConnection koneksiDatabase;
			MySqlCommand perintahDatabase = new MySqlCommand();
			MySqlParameter _kodeKelompok, _koding, _konterBatal;
			
			koneksiDatabase = new MySqlConnection(strKoneksi);
			perintahDatabase.CommandType = CommandType.StoredProcedure;
			perintahDatabase.CommandText = "KodingBarang";
			perintahDatabase.Connection = koneksiDatabase;

			_kodeKelompok = new MySqlParameter();
			_kodeKelompok.ParameterName = "_kodeKelompok";
			_kodeKelompok.MySqlDbType= MySqlDbType.Int16;
			_kodeKelompok.Direction = ParameterDirection.Input;
			_kodeKelompok.Value = 1;
			perintahDatabase.Parameters.Add(_kodeKelompok);

			_koding = new MySqlParameter();
			_koding.ParameterName = "_koding";
			_koding.MySqlDbType  = MySqlDbType.VarChar;
			_koding.Size = 20;
			_koding.Direction = ParameterDirection.Input;
			perintahDatabase.Parameters.Add(_koding);

			_konterBatal = new MySqlParameter();
			_konterBatal.ParameterName = "_konterBatal";
			_konterBatal.MySqlDbType = MySqlDbType.Int16;
			_konterBatal.Direction = ParameterDirection.Input;
			perintahDatabase.Parameters.Add(_konterBatal);
			try
			{
				koneksiDatabase.Open();
				int oce;
				oce = perintahDatabase.ExecuteNonQuery();
				koneksiDatabase.Close();
				Console.WriteLine(_koding.Value.ToString());
			}
			catch (MySqlException ex)
			{
				Console.WriteLine(ex.Message);
				Console.WriteLine(ex.Number);
				Console.WriteLine(ex.Source);
				Console.WriteLine(ex.StackTrace);
				Console.WriteLine(ex.HelpLink);
				Console.WriteLine(ex.InnerException);
				Console.WriteLine(ex.TargetSite);

			}
		}

[3 Nov 2006 9:41] Tonci Grgin

Hi Sori and thanks for you problem report. Please provide as much data as necessary to reproduce reported behavior.
Consider this example which produces correct results:
 - MySQL 5.0.27BK on WinXP Pro SP2 localhost
 - connector/NET 1.0.8RC SVN
 - NET fw 2.0
 - DDL:
DROP DATABASE IF EXISTS SpTest;
CREATE DATABASE SpTest;
USE SpTest;

CREATE TABLE Table1 (
	Table1Id INT AUTO_INCREMENT NOT NULL,
	Name VARCHAR(100) NOT NULL,
	PRIMARY KEY(Table1Id)
);

CREATE TABLE Table2 (
	Table2Id INT AUTO_INCREMENT NOT NULL,
	PRIMARY KEY(Table2Id)
);

CREATE TABLE Table3 (
	Table3Id INT AUTO_INCREMENT NOT NULL,
	Table1Id INT NOT NULL,
	Table2Id INT NOT NULL,
	PRIMARY KEY(Table3Id)
);

INSERT INTO Table1 (Table1Id, Name) VALUES (1, "Item1");
INSERT INTO Table1 (Table1Id, Name) VALUES (2, "Item2");
INSERT INTO Table2 (Table2Id) VALUES (1);
INSERT INTO Table3 (Table3Id, Table1Id, Table2Id) VALUES (1, 1, 1);
INSERT INTO Table3 (Table3Id, Table1Id, Table2Id) VALUES (2, 2, 1);

delimiter //
CREATE PROCEDURE GetT1Data
(
	Name VARCHAR(100),
	OUT Table1Id INT
)
BEGIN
	SELECT t1.Table1Id INTO Table1Id FROM Table1 t1 WHERE t1.Name LIKE Name;
	SELECT t3.Table2Id
	FROM Table3 t3
	WHERE t3.Table1Id = Table1Id;

END
//
 - Test case:
namespace SpTest {
	class Program {

		static void Test(string name) {
		MySqlConnection cnn = new MySqlConnection("Database=SpTest;Uid=root");
		cnn.Open();

		MySqlCommand cmd = new MySqlCommand("GetT1Data",cnn);
            	cmd.CommandType = CommandType.StoredProcedure;
            	cmd.Parameters.Clear();
            	cmd.Parameters.Add(new MySqlParameter("?Name", MySqlDbType.VarChar));
            	cmd.Parameters[0].Direction = ParameterDirection.Input;
            	cmd.Parameters[0].Value = name;
		cmd.Parameters.Add(new MySqlParameter("?Table1Id", MySqlDbType.Int32));
            	cmd.Parameters[1].Direction = ParameterDirection.Output;
            	cmd.Prepare();
            	cmd.ExecuteScalar();

		Console.WriteLine("Table1Id: {0}", cmd.Parameters[1].Value);
            	cmd.Dispose();
		cnn.Close();
		cnn.Dispose();
		}

	static void Main(string[] args) {
		Test("Item1");
		Test("Item3");
		}
	}
}