procedure or function Designation_INSERT has too many arguments specified.

[maliknouman]

Hi please help me in this code, it gives always error on execution of command is comes.

Try
            DBconnect()

            cmd.CommandText = "Designation_INSERT" 
            cmd.Connection = con

            cmd.CommandType = CommandType.StoredProcedure
            'Fill the Parameters of Store Procedure
            Param = cmd.Parameters.AddWithValue("@DesignationID", TxtDesigCode.Text)
            Param = cmd.Parameters.AddWithValue("@DesignationName", TxtDesigName.Text)
            Param = cmd.Parameters.AddWithValue("@ApprovedBy", TxtDesigApproved.Text)
            cmd.ExecuteNonQuery()  'Error Comes on this line
            MsgBox("Data Successfully Added!")
            DBClose()
        Catch ex As Exception
            MsgBox(ex.Message, MsgBoxStyle.OkOnly, "Please Check!")
        End Try

[TomW]

Can you show your insert statement from the stored procedure? Without seeing it, I'm guessing either the parameters are wrong, missing or misspelled.

Also assuming that DesignationID is an numeric field I would suggest converting the textbox value to a numeric datatype (CInt(TxtDesigCode.Text))

Lastly there is no need of assigning anything to Param (Param = ...) change that so it just starts with the cmd.Parameters.AddWithValue(...

[maliknouman]

Here is my procedure of Designation_INSERT

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO


ALTER PROCEDURE [dbo].[Designation_INSERT]
	-- Add the parameters for the stored procedure here
	   @DesignationID int,
       @DesignationName varchar(30),
       @ApprovedBy varchar(50) 
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT OFF;

    -- Insert statements for procedure here
	INSERT INTO Designation--(DesigID,DesigName,ApprovedBy) 
          
     VALUES (@DesignationID,@DesignationName,@ApprovedBy)
END

[TomW]

The field parameters seem to be correct unless im missing a misspelling somewhere between them. I would start by making two changes and see if you still have the same problem.

01) Convert the @DesignationID into an in (CInt(TxtDesigCode.Text))
02) Remove assigning them into PARAM (no clue what that is even doing there or where you are using it. However cmd is your command object and adding the parameters directly to that is all that is needed.

Last suggest is while stepping through the code, see exactly what values are being passed at the time of execution. If you are still having problems please give more detail to the error message that you are receiving.

[maliknouman]

Hi I have Still facing a problem in insert a value in Designation Table in SQL Server, When i added data into table first time this will added sucessfully but after this the Error MSG is comes that "procedure or function Designation_INSERT has too many arguments specified. ", after this again i added value this error is comming. When i check database table first data is added but second data not to be add in table.

My Store procedure and VB.net Code are given Above, Please Check it and help me in finding mistake.

[GeekByChoiCe]

can you please show us the complete code? the code you posted in your first post cant be complete. where and how you define the variable "Param"? you might keep adding parameters into param without cleaning before?
so first run Param has 3 parameters, second run Param has 6 parameters and so on.
worth to check that out

[maliknouman]

Hi This is my complete code of GlobleModule , frmDesignation and also Store procedure used in this code.

The error is comes that "procedure or function Designation_INSERT has too many arguments specified" and also now error msg is comes that volilation of primary key also but my database table is completly empty and i refresh my table for SQL server. Now Please help me what is this problem and how it recovers.

' Code of GlobleModule present in my Application
Imports System.Data.SqlClient

Module MainModule

    Public con As SqlConnection
    Public cmd As New SqlCommand
    Public flag As Boolean

    Public Function DBconnect()
        Try
            If flag = False Then
                con = New SqlConnection("Data Source=NOUMAN\SQLEXPRESS;Initial Catalog=PaySystem;Integrated Security=True")
                con.Open()
                flag = True
            End If
        Catch ex As SqlException
            MsgBox(ex.Message, MsgBoxStyle.OkOnly)
        End Try
        Return flag
    End Function

    Public Function DBClose()
        Try
            If flag = True Then
                con.Close()
                flag = False
            End If
        Catch ex As Exception

        End Try
        Return flag
    End Function


End Module


'===================================================================
'===================================================================

''''' Code Behind frmDesignation Form

Imports System.Data.SqlClient

Public Class FrmDesigDefinition

    Private Sub BtnSave_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles BtnSave.Click, BtnSave.Click

        Try
            DBconnect()

            cmd.CommandText = "Designation_INSERT" 
            cmd.Connection = con

            cmd.CommandType = CommandType.StoredProcedure
            'Fill the Parameters of Store Procedure
            cmd.Parameters.Clear()
            cmd.Parameters.AddWithValue("@DesignationID", CInt(TxtDesigCode.Text))
            cmd.Parameters.AddWithValue("@DesignationName", TxtDesigName.Text)
            cmd.Parameters.AddWithValue("@ApprovedBy", TxtDesigApproved.Text)
            cmd.ExecuteNonQuery()  'Error Comes on this line
            cmd.Dispose()

            MsgBox("Data Successfully Added!")

            DBClose()
        Catch ex As SqlException
            MsgBox(ex.Message & " SQL Exception ")


        Catch ex As Exception
            MsgBox(ex.ToString, MsgBoxStyle.OkOnly, "Please Check!")
        End Try
    End Sub

    Private Sub BtnCancel_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles BtnCancel.Click
        Me.Close()
    End Sub
   
End Class

'===================================================================
'===================================================================

''''MY Store Procedure Designation_INSERT  is Given Below


set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[Designation_INSERT]
	-- Add the parameters for the stored procedure here
       @DesignationID int,
       @DesignationName varchar(30),
       @ApprovedBy varchar(50) 
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT OFF;

    -- Insert statements for procedure here
	INSERT INTO Designation(DesigID,DesigName,ApprovedBy) 
          
     VALUES (@DesignationID,@DesignationName,@ApprovedBy)
END

[GeekByChoiCe]

ok i have tested your code and it works perfect after small changes to the procedure

my procedure:

ALTER PROCEDURE [dbo].[Designation_INSERT]
	-- Add the parameters for the stored procedure here
       @DesignationID int,
       @DesignationName varchar(30),
       @ApprovedBy varchar(50) 
AS

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON

BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT OFF;

    -- Insert statements for procedure here
	INSERT INTO Designation(DesigID,DesigName,ApprovedBy) 
          
     VALUES (@DesignationID,@DesignationName,@ApprovedBy)
END
	RETURN

how did u define "DesigID" in your database table? is it autoincrement or you just take the id and insert it no matter if it already exists?

[maliknouman]

The error of "procedure or function Designation_INSERT has too many arguments specified." is being solved
But the second error "Volilation of PRIMARY KEY constraint 'PK_Designation'. Cannot insert duplicate key in object 'dbo.Designation'. The statement has been terminated." is still not solved.
DesigID is define as an int datatype and this is not an autoincrement, i will enter this manually in the Designation Table, and also my whole table is empty but still this message is generated. Please help me.