Procedure or function has too many arguments

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Procedure or function has too many arguments

Author

Topic

monfu
Yak Posting Veteran

81 Posts

Posted - 2005-11-09 : 00:06:26

Dear all,

I have the following stored proc:-

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS OFF
GO

ALTER PROCEDURE dbo.stpLoginUser
(
@login Varchar(255),
@password Varchar(255),
@email Varchar(255) OUTPUT,
@username Varchar(255) OUTPUT,
@id int OUTPUT
)
AS
BEGIN
/*
Check for valid login and password
*/

SET @username = ' '

Select @id = id_u, @username = first_name, @email = email from pod_Users where email = @login AND u_password = @password

END
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

and basically the user enters a username and a password.

However I got the following error:-

Procedure or function has too many arguments

I tried renaming my stored proc name but to no avail.

I am calling this stored proc from a vb.net program as follows:-

Dim conn As SqlConnection
Dim cmd As SqlCommand
Dim RetVal As Integer = -1
Dim FullName As String

' create the conection object for scalar reader to use
conn = New SqlConnection
conn.ConnectionString = ConnectionString

' return the players pk_player
cmd = New SqlCommand
cmd.CommandType = CommandType.StoredProcedure
cmd.Connection = conn
cmd.CommandText = "stpLoginUser"
cmd.Parameters.Add("@login", SqlDbType.NVarChar, 255)
cmd.Parameters("@login").Value = username
cmd.Parameters.Add("@password", SqlDbType.NVarChar, 255)
cmd.Parameters("@password").Value = password
cmd.Parameters.Add("@username", SqlDbType.NVarChar, 255)
cmd.Parameters("@username").Direction = ParameterDirection.Output
cmd.Parameters.Add("@email", SqlDbType.NVarChar, 255)
cmd.Parameters("@email").Direction = ParameterDirection.Output
cmd.Parameters.Add("@id", SqlDbType.Int)
cmd.Parameters("@id").Direction = ParameterDirection.Output

Try
conn.Open()
cmd.ExecuteScalar()
RetVal = cmd.Parameters("@id").Value()
FullName = cmd.Parameters("@username").Value()
'Catch
Catch e As Exception
Console.WriteLine(e.ToString()) ' Print the error message to the user.
RetVal = -1
Finally
conn.Close()
End Try

Can anybody help me pls?

Thanks for your help

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2005-11-09 : 00:35:06

well your code to me seems to be correct..

Just try to run this procedure from Query Analyser and see whether its works for you..

And one more thing.. i dont know which may makes sense or not..

you are passing the datatype nVarchar.. instead of which pass Varchar which is defined in your stored procedure..??

Hope this helps..

Complicated things can be done by simple thinking

monfu
Yak Posting Veteran

81 Posts

Posted - 2005-11-09 : 00:42:39

Hello chiragkhabaria,

With SQL Query Analizer it's working fine.

I did the amendments as u suggested to the nVarChar :-

cmd = New SqlCommand
cmd.CommandType = CommandType.StoredProcedure
cmd.Connection = conn
cmd.CommandText = "stpLoginUser"
cmd.Parameters.Add("@login", SqlDbType.VarChar, 255)
cmd.Parameters("@login").Value = username
cmd.Parameters.Add("@password", SqlDbType.VarChar, 255)
cmd.Parameters("@password").Value = password
cmd.Parameters.Add("@username", SqlDbType.VarChar, 255)
cmd.Parameters("@username").Direction = ParameterDirection.Output
cmd.Parameters.Add("@email", SqlDbType.VarChar, 255)
cmd.Parameters("@email").Direction = ParameterDirection.Output
cmd.Parameters.Add("@id", SqlDbType.Int)
cmd.Parameters("@id").Direction = ParameterDirection.Output

but still the same problem mate.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-11-09 : 00:52:55

See if this helps you
http://www.mindsdoor.net/aSP/DBAccess.inc.html

Madhivanan

Failing to plan is Planning to fail

monfu
Yak Posting Veteran

81 Posts

Posted - 2005-11-09 : 01:00:00

Sorry mate but that did not help :(

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2005-11-09 : 01:00:21

Look out for this sample code at MSDN..

http://samples.gotdotnet.com/quickstart/util/srcview.aspx?path=%2fquickstart%2fhowto%2fsamples%2fadoplus%2foutparamswithacommand%2foutparamswithacommand.src

Check if you have missed out somthing???

Complicated things can be done by simple thinking