Solved

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

Posted on 2009-05-18
11
1,295 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
  • 5
  • 4
  • 2
11 Comments
 
LVL 14

Expert Comment

by:jjardine
Comment Utility
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
Comment Utility
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
Comment Utility
Hi jjardine,

Error is coming in the following line...

myCommand.ExecuteNonQuery();
0
 

Author Comment

by:srk1982
Comment Utility
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
Comment Utility
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
How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

 

Author Comment

by:srk1982
Comment Utility
Hi philipjonathan,

 still same problem !!! :(
0
 
LVL 14

Expert Comment

by:jjardine
Comment Utility
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 500 total points
Comment Utility
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
Comment Utility
No opologies philipjonathan.....
Its working great.........
0
 

Author Closing Comment

by:srk1982
Comment Utility
Thanks.
0
 
LVL 18

Expert Comment

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

Featured Post

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

This document covers how to connect to SQL Server and browse its contents.  It is meant for those new to Visual Studio and/or working with Microsoft SQL Server.  It is not a guide to building SQL Server database connections in your code.  This is mo…
Calculating holidays and working days is a function that is often needed yet it is not one found within the Framework. This article presents one approach to building a working-day calculator for use in .NET.
Illustrator's Shape Builder tool will let you combine shapes visually and interactively. This video shows the Mac version, but the tool works the same way in Windows. To follow along with this video, you can draw your own shapes or download the file…
This video explains how to create simple products associated to Magento configurable product and offers fast way of their generation with Store Manager for Magento tool.

771 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now