MySQL Bugs: #69152: Parameter is missing during execution to fill data adapter

Bug #69152	Parameter is missing during execution to fill data adapter
Submitted:	6 May 2013 10:04	Modified:	8 Jun 2013 4:07
Reporter:	Suman Biswas	Email Updates:
Status:	No Feedback	Impact on me:	None
Category:	Connector / NET	Severity:	S1 (Critical)
Version:	6.6.5	OS:	Windows (Windows 7, MySQL Server 5.6, ASP.Net 4)
Assigned to:	Assigned Account	CPU Architecture:	Any
Tags:	Parameter missing ASP.Net

Description:
Please read my post, here it seems its a bug of MySQL because there I can not see any error in C# code. I have post my question in Stackoverflow few days ago but expert cant solve this problem.
URL
1. http://stackoverflow.com/questions/16379061/mysql-and-c-net-stored-procedure-and-multiple-...
2. http://forums.mysql.com/read.php?38,585575,585575#msg-585575

Details
I am developing (converting application db from MS SQL to MySQL) an application using C#.Net and MySQL. My C# code and stored procedure is working perfect in MS SQL but when trying to ingrate with MySQL getting parameter error. My C# Code is as below and MySQL Stored Procedure is running perfectly (tested in editor using CALL key work and parameter) 

public DataTable AlapValidateUser(string email, string password,string Type) 
{ 
DataTable dt = new DataTable(); 
cmd = new MySqlCommand("UserIdValidation"); 
cmd.CommandType = CommandType.StoredProcedure; 
cmd.Connection = cnn; 
string pass = reEncryptpassword(password); 

MySqlParameter pramEmail = new MySqlParameter("@v_emailId", email); 
pramEmail.Direction = ParameterDirection.Input; 
cmd.Parameters.Add(pramEmail); 

MySqlParameter pramPassword = new MySqlParameter("@v_password", pass); 
pramPassword.Direction = ParameterDirection.Input; 
cmd.Parameters.Add(pramPassword); 

MySqlDataAdapter adap = new MySqlDataAdapter(cmd); 
if (cnn.State != ConnectionState.Open || 
cnn.State == ConnectionState.Broken || 
cnn.State != ConnectionState.Connecting || 
cnn.State != ConnectionState.Executing || 
cnn.State != ConnectionState.Fetching) 
cnn.Open(); 

adap.Fill(dt); 
cnn.Close(); 
return dt; 
} 
MySQL Stored Procedure is here: 

CREATE DEFINER=`root`@`localhost` PROCEDURE `UserIdValidation`(v_emailId NATIONAL VARCHAR(100),v_password 
NATIONAL VARCHAR(50)) 
BEGIN 
SELECT UserId ,eMail,BloodGroup 
,BloodGroupID,Country AS CountrySlNo ,CountryName ,State ,District 
,Location,fName,lName ,DonorType ,LastLogIn ,Validated ,ProfileImage 
,MaritalStatus ,Sex ,Height ,Weight ,HealthStatus 
,MyFileLocation FROM vwUser WHERE eMail = v_emailId AND 
PASSWORD = v_password AND Validated = 'Y'; 
END$$ 
During execution exception as below: 

"Incorrect number of arguments for PROCEDURE alap.UserIdValidation; expected 2, got 1" 

Already I have tried by changing parameter entry by below code: 

cmd.Parameters.Add(new MySqlParameter("@v_emailId", email)); cmd.Parameters.Add(new MySqlParameter("@v_password", pass)); 

and @ with ? and without @ or ? but nothing works. 

Can you please help me to find out the error. Is it a bug of mysql connector? 

UPDATE: My MySQL connector is v.6.6.5. I have checked in debug mode in C# parameter is correct and can see both paramter in command object. Next it is trying to filling Adapter hence this command object is passing to MySQL from Connector and there paramter is missing. I have tried to add same 1st parameter by creating 3rd line then gettinng error that same paramter already exist. 
From this test I am sure it is purely MySQL or mysql connector bug. 
I dont know how this bug can exists in such DB where so many people is using mysql. 

Thanks Suman

How to repeat:
Code is not running always getting same error that parameter not correct. 

"Incorrect number of arguments for PROCEDURE alap.UserIdValidation; expected 2, got 1" 

Suggested fix:
Unknown

Hi Suman, 

I have reproduced the issue you described, and exists a workaround that works perfectly fine and hopefully it will keep you up to speed in your migration to MySql. The problem here is the NATIONAL usage in the definition of the parameters for the routine. If you want to define a particular character set you can define the routine like this: 

CREATE DEFINER=`root`@`localhost` PROCEDURE `UserIdValidation`(v_emailId VARCHAR(100) CHARACTER SET utf8,v_password VARCHAR(50) CHARACTER SET utf8) 
BEGIN 
.... rest of code 

Or you just can use 

CREATE DEFINER=`root`@`localhost` PROCEDURE `UserIdValidation`(v_emailId VARCHAR(100),v_password VARCHAR(50)) 
BEGIN 

.... 

the default character set in the server is utf8 and is equivalent to NATIONAL according to the documentation. 

You can check more information here: 

http://dev.mysql.com/doc/refman/5.5/en/charset-national.html 

Please let me know if the workaround worked for you. 

Thanks in advance for your feedback.

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".