Procedure or function InsertUser has too many arguments specified

Question

miamia11 0 Newbie Poster

14 Years Ago

Stored Procedure "InsertUser"

ALTER procedure [dbo].[InsertUser]
(
@username as varchar(50),
@email as varchar(50),
@password as varchar(20)
)
as

if(not exists(select email from dataUser where email=@email))
begin

declare @lastnumber as integer
set @lastnumber =0
select top 1@lastnumber =cast(substring(user_id,8,9) as integer) from dataUser order by user_id desc
--insert
insert into dataUser (user_id,username,email,password,roleuser)
values ('USE-'+right(str(1000000000+(@lastnumber+1)),9),@username,@email,@password,'user')
return 0
end
else
return 1

this sp work fine when i execute it in SQL Server.
But can't work when i calling the SP from asp.net.

Public Class UserClass

        Private _User_ID As String
        Private _UserName As String
        Private _Email As String
        Private _Password As String
        Private _RoleUser As String


        Public Property User_ID() As String
            Get
                Return _User_ID
            End Get
            Set(ByVal value As String)
                _User_ID = value
            End Set
        End Property
        Public Property UserName() As String
            Get
                Return _UserName
            End Get
            Set(ByVal value As String)
                _UserName= value
            End Set
        End Property
        Public Property Email() As String
            Get
                Return _Email
            End Get
            Set(ByVal value As String)
                _Email = value
            End Set
        End Property
        Public Property Password() As String
            Get
                Return _Password
            End Get
            Set(ByVal value As String)
                _Password = value
            End Set
        End Property

        Public Property RoleUser() As String
            Get
                Return _RoleUser
            End Get
            Set(ByVal value As String)
                _RoleUser = value
            End Set
        End Property

        Private Sub closeDBcon(ByVal con As SqlConnection, ByVal cmd As SqlCommand)
            If Not IsDBNull(cmd) Then
                cmd.Dispose()
            End If
            If Not IsDBNull(con) Then
                If (con.State = ConnectionState.Open) Then
                    con.Close()
                    con.Dispose()
                End If
            End If
        End Sub

Public Function InsertNewUser() As String
            Dim mycon As SqlConnection = New SqlConnection(ConfigurationManager.ConnectionStrings("WebConnectionString").ConnectionString)
            Dim insertcommand As SqlCommand = New SqlCommand("InsertUser", mycon)

            insertcommand.CommandType = CommandType.StoredProcedure

            insertcommand.Parameters.Add("paramuser_id", SqlDbType.VarChar, 15, "@user_id").Direction = ParameterDirection.Output
            insertcommand.Parameters.Add("paramusername", SqlDbType.VarChar, 50, "@username").Value = UserName
            insertcommand.Parameters.Add("paramemail", SqlDbType.VarChar, 50, "@email").Value = Email
            insertcommand.Parameters.Add("parampassword", SqlDbType.VarChar, 20, "@password").Value = Password

            Try
                mycon.Open()
                insertcommand.ExecuteNonQuery()
                Return (insertcommand.Parameters("paramuser_id").Value.ToString())
            Finally
                closeDBcon(mycon, insertcommand)
            End Try
        End Function
End Class

Public Class UserManage
        Inherits System.Web.UI.Page

        Public Function InsertNewUser(ByVal _UserClass As UserClass)
            Return (_UserClass.InsertNewUser())
        End Function
End Class

<body>
    <form id="form1" runat="server">
    <div>
        <table bordercolor="#000000" bordercolordark="#000000" cellpadding="4" cellspacing="2"
            width="85%">
            <tr>
                <td align="center" colspan="2" width="27%">
                    <strong>REGISTER</strong></td>
            </tr>
            <tr>
                <td width="27%">
                    &nbsp;UserName</td>
                <td width="73%">
                    <asp:TextBox ID="txtUserName" runat="server"></asp:TextBox>
                    <asp:RequiredFieldValidator ID="RequiredFieldValidator4" runat="server" ControlToValidate="txtUserName"
                        Display="dynamic" ErrorMessage="Required" Font-Size="9pt"></asp:RequiredFieldValidator></td>
            </tr>
            <tr>
                <td width="27%">
                    &nbsp;Email</td>
                <td width="73%">
                    <asp:TextBox ID="txtEmail" runat="server" Width="77%"></asp:TextBox>
                    <asp:RequiredFieldValidator ID="RequiredFieldValidator1" runat="server" ControlToValidate="txtEmail"
                        Display="dynamic" ErrorMessage="Required" Font-Size="9pt"></asp:RequiredFieldValidator>
                    <asp:RegularExpressionValidator ID="RegularExpressionValidator1" runat="server" ControlToValidate="txtEmail"
                        Display="Dynamic" ErrorMessage="Invalid" Font-Size="9pt" ValidationExpression="[\w\.-]+(\+[\w-]*)?@([\w-]+\.)+[\w-]+"></asp:RegularExpressionValidator></td>
            </tr>
            <tr>
                <td width="27%">
                    &nbsp;Password</td>
                <td width="73%">
                    <asp:TextBox ID="txtPassword" runat="server"></asp:TextBox>&nbsp;<asp:RequiredFieldValidator
                        ID="RequiredFieldValidator2" runat="server" ControlToValidate="txtPassword" Display="dynamic"
                        ErrorMessage="Required" Font-Size="9pt"></asp:RequiredFieldValidator>
                </td>
            </tr>
            <tr>
                <td width="27%">
                    &nbsp;Password Confirmation</td>
                <td width="73%">
                    <asp:TextBox ID="txtConfirm" runat="server"></asp:TextBox>
                    <asp:RequiredFieldValidator ID="RequiredFieldValidator3" runat="server" ControlToValidate="txtConfirm"
                        Display="dynamic" ErrorMessage="Required" Font-Size="9pt"></asp:RequiredFieldValidator>
                    <asp:CompareValidator ID="CompareValidator1" runat="server" ControlToCompare="txtPassword"
                        ControlToValidate="txtKonfirmasi" Display="Dynamic" ErrorMessage="Not matched!"
                        Font-Size="9pt"></asp:CompareValidator></td>
            </tr>
            <tr>
                <td width="27%">
                </td>
                <td width="73%">
                    <asp:Button ID="ButtonSubmit" runat="server" BackColor="Silver" BorderColor="Silver"
                        ForeColor="Black" OnClick="ButtonSubmit_Click" Text="SUBMIT" /></td>
            </tr>
            <tr>
                <td colspan="2">
                    <asp:Label ID="ErrorMsg" runat="Server" CssClass="ErrorText" EnableViewState="False"
                        Width="540px"></asp:Label></td>
            </tr>
        </table>
    </div>
    </form>
</body>

Partial Class Register
        Inherits System.Web.UI.Page

        Private Sub SetUserData(ByVal user As UserClass)
            user.UserName = txtUserName .Text.Trim
            user.Email = txtEmail.Text.Trim
            user.Password = txtPassword.Text.Trim
        End Sub

        Protected Sub ButtonSubmit_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles ButtonSubmit.Click
           
          If Page.IsValid Then
                Dim USERS As UserClass = New UserClass()
                SetUserData(USERS)

                Try
                    Dim USERSMANAGE As UserManage = New UserManage()

                    If USERSMANAGE.ValidateUser(USERS) Then
                        Dim user_id As String = USERSMANAGE.InsertNewUser(USERS)

                        FormsAuthentication.SetAuthCookie(user_id, False)
                        Response.Cookies("MyWeb_cookies").Value = Server.HtmlEncode(txtUserName.Text)
                        Response.Redirect("Home.aspx")
                       
                    End If
                    'Catch oe As SqlException
                    'If (oe.ErrorCode = 1) Then
                    'ErrorMsg.Text = "ERROR: This email address already registered.."
                    'Else
                    'ErrorMsg.Text = "ERROR: Could not add record, please " + " ensure the fields are correctly filled out"
                    'End If
                Catch ex As Exception
                    Response.Write(ex.Message)
                End Try
            End If
        End Sub

    End Class

An error message "Procedure or function InsertUser has too many arguments specified", when i click on the SUBMIT button.
Please help me...as soon as possible.
Thank in advance.

asp.net

3 Contributors
10 Replies
410 Views
1 Day Discussion Span
Latest Post 14 Years Ago Latest Post by miamia11

kvprajapati 1,826 Posting Genius

14 Years Ago

Stored-procedure "InsertUser" has three parameters and you are adding four parameters. (There is no out parameter).

insertcommand.Parameters.AddWithValue("@username",UserName)
insertcommand.Parameters.AddWithValue("@email",Email)
insertcommand.Parameters.AddWithValue("@password",Password)

Reply to this topic

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.

mithranvm 0 Newbie Poster · Answer 1 · 2010-02-22T11:42:54+00:00

@username as varchar(50),
@email as varchar(50),
@password as varchar(20)

these are your input parameters there is no parameter by the name @user_id thats why this error occurs

solution is

insted of

insertcommand.Parameters.Add("paramuser_id", SqlDbType.VarChar, 15, "@user_id").Direction = ParameterDirection.Output

just replace

DbParameter returnvalue;
returnvalue = insertcommand.CreateParameter();
returnvalue.Direction = ParameterDirection.ReturnValue;
insertcommand.Parameters.Add(returnvalue);
mycon.Open();
insertcommand.ExecuteNonQuery();
Result = Convert.ToInt32(returnvalue.Value);

hope you got your answer.

miamia11 0 Newbie Poster · Answer 2 · 2010-02-22T11:49:05+00:00

Public Function InsertNewUser() As String
            Dim mycon As SqlConnection = New SqlConnection(ConfigurationManager.ConnectionStrings("WebConnectionString").ConnectionString)
            Dim insertcommand As SqlCommand = New SqlCommand("InsertUser", mycon)

            insertcommand.CommandType = CommandType.StoredProcedure

            'insertcommand.Parameters.Add("paramuser_id", SqlDbType.VarChar, 15, "@user_id").Direction = ParameterDirection.Output
            insertcommand.Parameters.Add("paramusername", SqlDbType.VarChar, 50, "@username").Value = UserName
            insertcommand.Parameters.Add("paramemail", SqlDbType.VarChar, 50, "@email").Value = Email
            insertcommand.Parameters.Add("parampassword", SqlDbType.VarChar, 20, "@password").Value = Password

            Try
                mycon.Open()
                insertcommand.ExecuteNonQuery()
                Return (insertcommand.Parameters("paramuser_id").Value.ToString())
            Finally
                closeDBcon(mycon, insertcommand)
            End Try
        End Function

When I delete the output parameter as you said, this error message shown:
Procedure or Function 'InsertUser' expects parameter '@username', which was not supplied.

What's wrong??
*confused*

Thanks in advance.

miamia11 0 Newbie Poster · Answer 3 · 2010-02-22T11:53:41+00:00

DbParameter returnvalue;
returnvalue = insertcommand.CreateParameter();
returnvalue.Direction = ParameterDirection.ReturnValue;
insertcommand.Parameters.Add(returnvalue);
mycon.Open();
insertcommand.ExecuteNonQuery();
Result = Convert.ToInt32(returnvalue.Value);
hope you got your answer.

thank you...
but where the code must be writen??
and why the returnvalue must be convert to int?

mithranvm 0 Newbie Poster · Answer 4 · 2010-02-22T12:01:09+00:00

remove this line
Return (insertcommand.Parameters("paramuser_id").Value.ToString())

use this part


Dim returnvalue As DbParameter
returnvalue = insertcommand.CreateParameter()
returnvalue.Direction = ParameterDirection.ReturnValue
insertcommand.Parameters.Add(returnvalue)
mycon.Open()
insertcommand.ExecuteNonQuery()
Result = Convert.ToInt32(returnvalue.Value)

mithranvm 0 Newbie Poster · Answer 5 · 2010-02-22T12:05:13+00:00

i use c# thats why its converted in vb.net no need of conversion.

mithranvm 0 Newbie Poster · Answer 6 · 2010-02-22T12:26:52+00:00

mithranvm 0 Newbie Poster

14 Years Ago

Your problem resolved?

miamia11 0 Newbie Poster · Answer 7 · 2010-02-22T12:39:59+00:00

Public Function InsertNewUser() As String
            Dim mycon As SqlConnection = New SqlConnection(ConfigurationManager.ConnectionStrings("GoldenWebConnectionString").ConnectionString)
            Dim insertcommand As SqlCommand = New SqlCommand("InsertUser", mycon)

            insertcommand.CommandType = CommandType.StoredProcedure


            insertcommand.Parameters.Add("paramusername", SqlDbType.VarChar, 50, "@username").Value = UserName
            insertcommand.Parameters.Add("paramemail", SqlDbType.VarChar, 50, "@email").Value = Email
            insertcommand.Parameters.Add("parampassword", SqlDbType.VarChar, 20, "@password").Value = Password

            Dim returnvalue As SqlParameter
            returnvalue = insertcommand.CreateParameter
            returnvalue.Direction = ParameterDirection.ReturnValue
            insertcommand.Parameters.Add(returnvalue)
            mycon.Open()
            insertcommand.ExecuteNonQuery()
            closeDBcon(mycon, insertcommand)
         
        End Function

Correct me if i am wrong...
there's no Return value in the function above.

mithranvm 0 Newbie Poster · Answer 8 · 2010-02-22T13:24:17+00:00

Public Function InsertNewUser() As String
            Dim mycon As SqlConnection = New SqlConnection(ConfigurationManager.ConnectionStrings("GoldenWebConnectionString").ConnectionString)
            Dim insertcommand As SqlCommand = New SqlCommand("InsertUser", mycon)

            insertcommand.CommandType = CommandType.StoredProcedure


            insertcommand.Parameters.Add("paramusername", SqlDbType.VarChar, 50, "@username").Value = UserName
            insertcommand.Parameters.Add("paramemail", SqlDbType.VarChar, 50, "@email").Value = Email
            insertcommand.Parameters.Add("parampassword", SqlDbType.VarChar, 20, "@password").Value = Password

            Dim returnvalue As SqlParameter
            returnvalue = insertcommand.CreateParameter
            returnvalue.Direction = ParameterDirection.ReturnValue
            insertcommand.Parameters.Add(returnvalue)
            mycon.Open()
            insertcommand.ExecuteNonQuery()
           
           [B]return returnvalue.Value [/B]

           // this line will return the 0 or 1 from your procedure
          
  closeDBcon(mycon, insertcommand)
         
        End Function

miamia11 0 Newbie Poster · Answer 9 · 2010-02-23T13:09:55+00:00

That's did not work...:-(
The error message :
"Procedure or Function 'InsertUser' expects parameter '@username', which was not supplied."

What's wrong...?