Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

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

Posted on 2009-05-18
11
Medium Priority
?
1,513 Views
Last Modified: 2013-12-17
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

0
Comment
Question by:srk1982
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 4
  • 2
11 Comments
 
LVL 14

Expert Comment

by:jjardine
ID: 24417905
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
 
LVL 18

Expert Comment

by:philipjonathan
ID: 24417908
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
 

Author Comment

by:srk1982
ID: 24417946
Hi jjardine,

Error is coming in the following line...

myCommand.ExecuteNonQuery();
0
URL rewriting in AWS CloudFront

A quick how-to guide to implement with a Lambda function!

 

Author Comment

by:srk1982
ID: 24417951
Hi philipjonathan,

      I tried your idea, but still the same erorr.


Error is coming in the following line...

myCommand.ExecuteNonQuery();
0
 
LVL 18

Expert Comment

by:philipjonathan
ID: 24418014
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
 

Author Comment

by:srk1982
ID: 24418034
Hi philipjonathan,

 still same problem !!! :(
0
 
LVL 14

Expert Comment

by:jjardine
ID: 24418049
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
 
LVL 18

Accepted Solution

by:
philipjonathan earned 2000 total points
ID: 24418108
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
 

Author Comment

by:srk1982
ID: 24418136
No opologies philipjonathan.....
Its working great.........
0
 

Author Closing Comment

by:srk1982
ID: 31582833
Thanks.
0
 
LVL 18

Expert Comment

by:philipjonathan
ID: 24418284
Phew! :)
Now I know, I always thought MySQL needs the '?', I was wrong.
0

Featured Post

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In my previous two articles we discussed Binary Serialization (http://www.experts-exchange.com/A_4362.html) and XML Serialization (http://www.experts-exchange.com/A_4425.html). In this article we will try to know more about SOAP (Simple Object Acces…
Many of us here at EE write code. Many of us write exceptional code; just as many of us write exception-prone code. As we all should know, exceptions are a mechanism for handling errors which are typically out of our control. From database errors, t…
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…
In this video, Percona Director of Solution Engineering Jon Tobin discusses the function and features of Percona Server for MongoDB. How Percona can help Percona can help you determine if Percona Server for MongoDB is the right solution for …

722 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question