Click here to Skip to main content
15,879,535 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
I am trying to call a simple SP in c#, and when I execute my login method, it throws the following exception --> Procedure or function Validate_User has too many arguments specified..

Here is my code:
C#
int user_id = 0;
            string constr = ConfigurationManager.ConnectionStrings["dummyConnectionString"].ConnectionString;
            using (SqlConnection con = new SqlConnection(constr))
            {
                using (SqlCommand cmd = new SqlCommand("Validate_User"))
                {
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.Parameters.AddWithValue("@Username", Login1.UserName);
                    cmd.Parameters.AddWithValue("@Password", Login1.Password);
                    cmd.Parameters.AddWithValue("@id", user_id);
                    cmd.Connection = con;
                    con.Open();


                    SqlDataReader dr = cmd.ExecuteReader();
                    if (dr.Read())
                    {
                        con.Close();
                        Session["UserAuthentication"] = user_id;
                        Response.Redirect("~/Port/insert.aspx");
                       // FormsAuthentication.RedirectFromLoginPage(Login1.UserName, Login1.RememberMeSet);
                    }


SP
SQL
ALTER PROCEDURE [dbo].[Validate_User]
      @Username NVARCHAR(50),
      @Password NVARCHAR(50)
AS
BEGIN
      SET NOCOUNT ON;
     
      SELECT username, [password], id
      FROM [dbo].[User] 
	  WHERE Username = @Username AND [password] = @Password
     

END


Is there issue with my SP or am I missing something my C# code.
Thank you for your help.
Posted

Isn't it apparent that you are passing three parameters in your C# code while your SQL Stored Proc only accepts two paranmeters?

C#
cmd.Parameters.AddWithValue("@Username", Login1.UserName);
cmd.Parameters.AddWithValue("@Password", Login1.Password);
cmd.Parameters.AddWithValue("@id", user_id);


Where does the @id parameters comes from?
 
Share this answer
 
You need to specify that @id is an output parameter.

SQL
ALTER PROCEDURE [dbo].[Validate_User]
      @Username NVARCHAR(50),
      @Password NVARCHAR(50)
      @id INT OUTPUT
AS
...

In c# you also need to specify that the id is for output, something like this:
C#
SqlParameter outParam = cmd.Parameters.AddWithValue("@id", user_id);
outParam.Direction = ParameterDirection.Output;

After executing you also need to read back the value, something like this:
C#
int return_id = cmd.Parameters["@id"].Value;

Good luck!
 
Share this answer
 
v2
Comments
DamithSL 21-Nov-14 10:31am    
set the @id value inside the SP
E.F. Nijboer 21-Nov-14 12:32pm    
Yes indeed, also very important to get it all working.
miss786 24-Nov-14 6:02am    
Thank you very much for the your help and explanation.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900