CODEWITHSUNDEEP
c#mysql
Suman Biswas
Suman Biswas

Reputation: 169

MySQL and C#.Net Stored Procedure and multiple parameter

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

Can you please help me to find out the error.

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

Upvotes: 1

Views: 13244

Answers (2)

Suman Biswas
Suman Biswas

Reputation: 169

This is a BUG of MY SQL and I have received solution from MySQL Team. I have implemented this changes in my MySQL Stored Procedure and got solution. MySQL reply as below

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.

Upvotes: 1

TheLukeMcCarthy
TheLukeMcCarthy

Reputation: 2283

I think you might need to change the way you call the stored proc. Take a look at this example http://forums.asp.net/t/988462.aspx

Upvotes: 0

Related Questions