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