如何避免存储过程有太多参数错误来自下面的代码？ [英] How to avoid stored procedure has too many arguments error from following code?

问题描述

Hai团队，

我们在项目中使用visual studio 2017和我的sql。

还使用存储过程进行插入和更新。

使用以下代码时，它第一次保存数据第二次显示错误

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

注意：

我们共享存储过程代码和vb代码。

我们将数据作为xml从windows app传递到sql数据库。

"

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)

我尝试过：



VB代码：点击保存按钮点击：

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  

推荐答案

com.Parameters.Clear()

使用上面的评论我们可以避免这样的问题

using above comment we can avoid such probelm

cn.Close（）

cn.Open（）

result = VALIDATEGROUP（UltraGroupBox1）

Dim DEPUTATIONDETAILS As String = GETDEPUTATIONDETAILS（）

如果result = True则

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（给定数据保存成功！，成功）

结束如果

Catch ex As Exception

MessageBox.Show（ex.Message，Error）

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")

这篇关于如何避免存储过程有太多参数错误来自下面的代码？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！