Click here to Skip to main content
15,892,298 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
Hai Team,
We are using visual studio 2017 and my sql in our project.
Also used stored procedure for insertion and updation.
While using following code it saves data for first time second time it shows an error
"
procedure has too many arguments specified
"

Note:
We have shared stored procedure code and vb code.
We pass data as xml from windows app to sql database.
GETDEPUTATIONDETAILS() this function returns data as xml (data to insert or save)


What I have tried:

VB Code: on save button click:
Try
            cn.Close()
            cn.Open()
            result = VALIDATEGROUP(UltraGroupBox1)
            Dim DEPUTATIONDETAILS As String = GETDEPUTATIONDETAILS()
            If result = True Then
                com.CommandText = "USP_DEPUTATON_ENTRY"
                com.Parameters.AddWithValue("@deputationdetails", DEPUTATIONDETAILS)
                com.Parameters.Add("@deputation_entrysno", SqlDbType.Int).Value = dp_txtentryno.Text
                com.Parameters.Add("@createdby", SqlDbType.NVarChar).Value = CreatedBy
                com.Parameters.Add("@yearcode", SqlDbType.NVarChar).Value = DateTime.Now.Year.ToString
                com.CommandType = CommandType.StoredProcedure
                com.Connection = cn
                com.ExecuteNonQuery()
                MessageBox.Show("Given Data are Saved Successfully!", "Success")
            End If
        Catch ex As Exception
            MessageBox.Show(ex.Message, "Error")
        End Try



Stored Procedure:

alter PROCEDURE USP_DEPUTATON_ENTRY   
    
(  
 @deputationdetails as xml,
 @createdby nvarchar(50),
 @deputation_entrysno  int,
 @yearcode nvarchar(50)
)  
  
As Begin  
update HR_EMPLOYEE_DEPUTATION set ISDELETED ='y' where DP_ENTRYSNO =  @deputation_entrysno 
end
begin

Insert into HR_EMPLOYEE_DEPUTATION (DP_ENTRYSNO,DP_ECNO,DP_NAME,DP_DATE,DP_FRMBRANCH,DP_DEPARTMENT,DP_DESIGNATION,
DP_TOBRANCH,DP_CONTCNAME,DP_CONTCNUM,DP_INTIME,DP_OUTTIME,DP_PURPOSE,DP_PREECNO,DP_PRENAME,DP_MANGECNO,DP_MANGNAME,
CREATEDDATE,CREATEDBY,ISDELETED,YEARCODE) 
select
[Table].[Column].value('DP_ENTRYSNO[1]','int') as 'DP_ENTRYSNO',
[Table].[Column].value('DP_ECNO[1]','varchar(50)') as 'DP_ECNO',
[Table].[Column].value('DP_NAME[1]','varchar(50)') as 'DP_NAME',
[Table].[Column].value('DP_DATE[1]','datetime') as 'DP_DATE',
[Table].[Column].value('DP_FRMBRANCH[1]','varchar(50)') as 'DP_FRMBRANCH',
[Table].[Column].value('DP_DEPARTMENT[1]','varchar(50)') as 'DP_DEPARTMENT',
[Table].[Column].value('DP_DESIGNATION[1]','varchar(50)') as 'DP_DESIGNATION',
[Table].[Column].value('DP_TOBRANCH[1]','varchar(50)') as 'DP_TOBRANCH',
[Table].[Column].value('DP_CONTCNAME[1]','varchar(50)') as 'DP_CONTCNAME',
[Table].[Column].value('DP_CONTCNUM[1]','varchar(50)') as 'DP_CONTCNUM',
[Table].[Column].value('DP_INTIME[1]','varchar(50)') as 'DP_INTIME',
[Table].[Column].value('DP_OUTTIME[1]','varchar(50)') as 'DP_OUTTIME',
[Table].[Column].value('DP_PURPOSE[1]','varchar(50)') as 'DP_PURPOSE',
[Table].[Column].value('DP_PREECNO[1]','varchar(50)') as 'DP_PREECNO',
[Table].[Column].value('DP_PRENAME[1]','varchar(50)') as 'DP_PRENAME',
[Table].[Column].value('DP_MANGECNO[1]','varchar(50)') as 'DP_MANGECNO',
[Table].[Column].value('DP_MANGNAME[1]','varchar(50)') as 'DP_MANGNAME',
GETDATE(),
@createdby,
'n',
@yearcode

FROM @deputationdetails.nodes('/ RECORD / ENTRY') as [Table]([Column])    
end

begin   
declare @tobranch  as nvarchar(50)
set @tobranch = (select DP_TOBRANCH from HR_EMPLOYEE_DEPUTATION where DP_ENTRYSNO = @deputation_entrysno AND ISDELETED='N')
update HR_EMPLOYEE_MASTER_DETAILS set DEPUTATIONTOBRANCH = @tobranch
end  
Posted
Updated 6-Mar-18 22:49pm

com.Parameters.Clear()

using above comment we can avoid such probelm
 
Share this answer
 
cn.Close()
cn.Open()
result = VALIDATEGROUP(UltraGroupBox1)
Dim DEPUTATIONDETAILS As String = GETDEPUTATIONDETAILS()
If result = True Then
com.CommandText = "USP_DEPUTATON_ENTRY"
com.Parameters.AddWithValue("@deputationdetails", DEPUTATIONDETAILS)
com.Parameters.Add("@deputation_entrysno", SqlDbType.Int).Value = dp_txtentryno.Text
com.Parameters.Add("@createdby", SqlDbType.NVarChar).Value = CreatedBy
com.Parameters.Add("@yearcode", SqlDbType.NVarChar).Value = DateTime.Now.Year.ToString
com.CommandType = CommandType.StoredProcedure
com.Connection = cn
com.ExecuteNonQuery()
MessageBox.Show("Given Data are Saved Successfully!", "Success")
End If
Catch ex As Exception
MessageBox.Show(ex.Message, "Error")
 
Share this answer
 

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