c# too many arguments specified in a procedure or a function

Question

I wrote a stored procedure to check whether the username and password exists in the database and return the results accordingly

This is the stored procedure

 ALTER proc [dbo].[checkuser]
 @userid varchar(20),
 @password varchar(20),
 @ReturnVal varchar(50) output
 As 
 Begin
 if exists(select userid from users where userid=@userid)
 set @ReturnVal='Logged in Successfully'
 else
 set @ReturnVal='Login Failed'
 end

and this is my c# code

private void button1_Click(object sender, EventArgs e)
{
    OleDbConnection conn = new OleDbConnection(
      "File Name=E:\\Vivek\\License Manager\\License Manager\\login.udl");

    try
    {  
        conn.Open();               
        OleDbCommand cmd = new OleDbCommand("checkuser",conn);              
        cmd.CommandType = CommandType.StoredProcedure;                
        OleDbParameter p1=new OleDbParameter("userid",username.Text); 
        OleDbParameter p2 = new OleDbParameter("password",password.Text);
        cmd.Parameters.Add(p1);                
        cmd.Parameters.Add(p2);                
        var returnParameter = cmd.Parameters.Add("ReturnVal",OleDbType.Integer);
        returnParameter.Direction = ParameterDirection.ReturnValue;
        cmd.ExecuteNonQuery();
    }
    catch (Exception ex)
    {
        MessageBox.Show(ex.Message);

    }
}

I am new to c#.I want to check whether the username exists from the database.But when I execute the code I get an error saying "too many arguments specified in a procedure or a function ".

Can anyone tell me where am I making a mistake?

Thanks

Not sure about `OleDb` but in Sql, you'll have to use `@` prefix for the parameters. I.e `new OleDbParameter("@userid",username.Text);` — Sriram Sakthivel, Oct 06 '14 at 06:00
Declare your return value as an [output](http://technet.microsoft.com/en-us/library/ms187004(v=sql.105).aspx) parameter in the stored procedure, or don't add the `ReturnVal` to the parameters. — Yurii, Oct 06 '14 at 06:01
@Yuriy Thanks for the reply.Can you give me a code snippet in c# to get the value from the stored procedure? — user2614235, Oct 06 '14 at 06:03
@SriramSakthivel I tried adding @ but still showing the same error — user2614235, Oct 06 '14 at 06:04
Use `@` prefix for all parameters and change the `ReturnVal` to `@ReturnValue`. Did you tried all of them? — Sriram Sakthivel, Oct 06 '14 at 06:05
@user2614235 again you forgot to add `@` before parameters in c# code and and change to this `var returnParameter = cmd.Parameters.Add("@ReturnVal",OleDbType.VarChar,20)` — Dgan, Oct 06 '14 at 07:27

Dgan · Answer 1 · 2014-10-06T06:12:53.583

2

Try like this:

Replace Your Code with Followings Changes

 OleDbParameter p1=new OleDbParameter("@userid",username.Text);


OleDbParameter p2 = new OleDbParameter("@password",password.Text);

var returnParameter = cmd.Parameters.Add("@ReturnVal",OleDbType.Integer);

returnParameter.Direction = ParameterDirection.ReturnValue;

cmd.ExecuteNonQuery();

 int Result= Convert.ToInt32(returnParameter.Value);

In the Procedure

create proc checkuser
 @userid varchar(20),
 @password varchar(20),
@ReturnVal int output

 As 
 Begin
 if exists(select userid from users where userid=@userid)
 set @ReturnVal= 0
 else

 set @ReturnVal= 1
 end

edited Oct 06 '14 at 06:12

answered Oct 06 '14 at 06:02

Dgan

10,077
1
29
51

Thanks Ganesh but the options shown here int Result= Convert.ToInt(returnParameter.Value); is int16 and not int – user2614235 Oct 06 '14 at 06:11
Stored procedure throwing errors Msg 102, Level 15, State 1, Procedure checkuser, Line 4 Incorrect syntax near '@ReturnVal'. Msg 137, Level 15, State 1, Procedure checkuser, Line 8 Must declare the scalar variable "@ReturnVal". Msg 137, Level 15, State 1, Procedure checkuser, Line 10 Must declare the scalar variable "@ReturnVal". – user2614235 Oct 06 '14 at 06:14
1

@user2614235 Its working Fine For me did you copy paste that stored procedure?? – Dgan Oct 06 '14 at 06:17
Hey My application does not proceed further when I click the login button :( – user2614235 Oct 06 '14 at 06:26

Neel · Accepted Answer · 2014-10-06T06:30:39.207

2

Procedure never returns a value.You have to use a output parameter in stored procedure.

Changes in SP as shown here:

create proc checkuser
    @userid varchar(20),
    @password varchar(20)
    @result int output
As 
Begin
   if exists(select userid from users where userid=@userid)
      set @result = 0
   else
      set @result = 1
   end

and in C# code:

 SqlParameter returnParameter = cmd.Parameters.Add("result", SqlDbType.Int);
  returnParameter.Direction = ParameterDirection.ReturnValue;
  int id = (int) returnParameter.Value;

edited Oct 06 '14 at 06:30

answered Oct 06 '14 at 06:03

Neel

11,625
3
43
61

Thanks for the reply. How to display the return parameter.Eg if my database sends a message in the form of return parameter "Login does not exist" how to display that message in c# I hope my doubt is clear – user2614235 Oct 06 '14 at 06:19
2

@Neel I think It should `@result int output` instead of `@result output int ` – Dgan Oct 06 '14 at 06:21
1

you can take "varchar(50)" instead of "int" in above code @user2614235 – Neel Oct 06 '14 at 06:33
@Neel it is throwing error converting varchar to string – user2614235 Oct 06 '14 at 06:46
u need to convert like string id = (string) returnParameter.Value; @user2614235 – Neel Oct 06 '14 at 06:48
it would be better if u update ur question @user2614235 – Neel Oct 06 '14 at 06:54
Let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/62502/discussion-between-user2614235-and-neel). – user2614235 Oct 06 '14 at 06:57
sorry cant use chat from office @user2614235 – Neel Oct 06 '14 at 07:54
var returnParameter = cmd.Parameters.Add("@ReturnVal",OleDbType.VarChar,50) – Neel Oct 06 '14 at 07:55

score 0 · Answer 3 · edited May 23 '17 at 12:20

0

If you are using SQL-Server,Use Sql data base connection and command

Check this

edited May 23 '17 at 12:20

Community

1
1

answered Oct 06 '14 at 06:04

Prasanth V J

1,126
14
32

c# too many arguments specified in a procedure or a function

3 Answers3