c# - Error -> Parameter 'string' not found in the collection.

Hi Experts,

           I have written a SP in MYSQL to check the login information and returning a value to the c# code.I am getting the following error :

Aurgument exception was unhandled.
Parameter 'string' not found in the collection.

C# :
 
 private void btnOK_Click(object sender, EventArgs e)
        {
            MySqlConnection myConn = new MySqlConnection(ConfigurationSettings.AppSettings["Con"].ToString());
            MySqlCommand myCommand = new MySqlCommand("sp_LoginCheck", myConn);
            myCommand.CommandType = CommandType.StoredProcedure;
            MySqlParameter myParm = new MySqlParameter();
            myParm = myCommand.Parameters.Add("@UserName", MySqlDbType.VarChar, 50);
            myParm.Value = "srk1";
            myParm = myCommand.Parameters.Add("@Password", MySqlDbType.VarChar, 50);
            myParm.Value = "pwd1";
            myParm = myCommand.Parameters.Add("@DoesExist", MySqlDbType.Bit);
            myParm.Direction = ParameterDirection.Output;
            myConn.Open();
            myCommand.ExecuteNonQuery();
            bool doesExist = (bool)myCommand.Parameters["@DoesExist"].Value;
            if (!doesExist)
            {
                MessageBox.Show("UserName/Password invalid.");
            }
            else
            {
                MessageBox.Show("Logged in successfully.");
            }
            myConn.Close();
        }
________________________________________________
Mysql SP :
 
DELIMITER $$
 
DROP PROCEDURE IF EXISTS `demo`.`sp_LoginCheck`$$
 
CREATE PROCEDURE `demo`.`sp_LoginCheck`(
   
 IN UserName1 varchar(50),
 IN Password1 varchar(50),
 OUT DoesExist bit)   
    BEGIN
 declare userCount INT default 0;
 SET DoesExist = 0; -- set to false initially, we'll assume for now it doesn't exist
 SELECT COUNT(Uid) INTO userCount
 FROM tbl_Users
 WHERE username = username1 and password=password1;
 IF userCount > 0 THEN
  SET DoesExist = 1;  -- more then one returned, return true
 END IF;
    END$$
 
DELIMITER ;
________________________________________________

Open in new window

srk1982Asked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
philipjonathanConnect With a Mentor Commented:
I search some example on the net, and saw that some don't have '?' in front of argument names.
http://www.aspcode.net/MySQL-DAL-stored-procedures-support-howto-part-14.aspx
http://forums.asp.net/p/988462/1309349.aspx#1309349

I'm not suppose to do trial-and-error with you, but one last try, change the following lines:
myParm = myCommand.Parameters.Add("UserName1", MySqlDbType.VarChar, 50);
myParm = myCommand.Parameters.Add("Password1", MySqlDbType.VarChar, 50);
myParm = myCommand.Parameters.Add("DoesExist", MySqlDbType.Bit);
bool doesExist = (bool)myCommand.Parameters["DoesExist"].Value;

My apologies if this doesn't work
0
 
jjardineCommented:
What line is this casuing an error on?   What happens if you close the connection after running the ExecuteNonQuery and then check the value of the parameter?
0
 
philipjonathanCommented:
Try if changing the following lines help (use ? instead of @):

myParm = myCommand.Parameters.Add("?UserName", MySqlDbType.VarChar, 50);
myParm = myCommand.Parameters.Add("?Password", MySqlDbType.VarChar, 50);
myParm = myCommand.Parameters.Add("?DoesExist", MySqlDbType.Bit);
bool doesExist = (bool)myCommand.Parameters["?DoesExist"].Value;
0
Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
srk1982Author Commented:
Hi jjardine,

Error is coming in the following line...

myCommand.ExecuteNonQuery();
0
 
srk1982Author Commented:
Hi philipjonathan,

      I tried your idea, but still the same erorr.


Error is coming in the following line...

myCommand.ExecuteNonQuery();
0
 
philipjonathanCommented:
Try to use the same name for the parameter (UserName1 and Password1)
myParm = myCommand.Parameters.Add("?UserName1", MySqlDbType.VarChar, 50);
myParm = myCommand.Parameters.Add("?Password1", MySqlDbType.VarChar, 50);
0
 
srk1982Author Commented:
Hi philipjonathan,

 still same problem !!! :(
0
 
jjardineCommented:
Your parameters in the code have the @ symbol in front of them, but in the Stored Proc they do not.   Could that be causing an issue?   I don't work with MySQL but within SQL the parameter names are the same so in the sproc they would be @userName   and in the parameter for the command object it would be @userName.
0
 
srk1982Author Commented:
No opologies philipjonathan.....
Its working great.........
0
 
srk1982Author Commented:
Thanks.
0
 
philipjonathanCommented:
Phew! :)
Now I know, I always thought MySQL needs the '?', I was wrong.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.