过程或函数有太多参数指定sql

问题描述：

我的PS是：

My PS is:

ALTER PROCEDURE [dbo].[ThemGioLLNuocGio] 
(   
    @Ten nvarchar(50),
    @TenTinh nvarchar(50),
    @TenTram nvarchar(50),
    @Nam int,
    @Thang int,
    @Ngay int,
    @Gio int,
    @LLNuoc int,
    @LLNuocC bit 
)
AS
declare @KhuVucID int
declare @TinhID int
declare @TramID int
declare @NamID int
declare @ThangID int
declare @NgayID int
declare @GioID int
select @KhuVucID =(select KhuVuc.KhuVucID from KhuVuc where  KhuVuc.Ten=@Ten)
select @TinhID =(select Tinh.TinhID from Tinh where  Tinh.TenTinh like @TenTinh)
select @TramID =(select Tram.TramID from Tram Where Tram.TenTram like @TenTram)
if not exists (select * from Nam where (Nam.Nam =@Nam and Nam.TramID=@TramID))
begin
insert into Nam(Nam,TramID) values (@Nam,@TramID)
end
select @NamID =(select Nam.NamID from Nam where  (Nam.Nam =@Nam and Nam.TramID=@TramID) )
if not exists(select Thang.Thang from Thang where (Thang.Thang = @Thang and Thang.NamID=@NamID))
begin
insert into Thang(Thang,NamID) values (@Thang, @NamID)      
end
select @ThangID =(select Thang.ThangID from Thang where (Thang.Thang = @Thang and Thang.NamID=@NamID))
if not exists (select Ngay.NgayID from Ngay where (Ngay.Ngay=@Ngay and Ngay.ThangID=@ThangID))
begin
insert into Ngay(Ngay,ThangID) values (@Ngay,@ThangID)
end
select @NgayID =(select Ngay.NgayID from Ngay where (Ngay.Ngay=@Ngay and Ngay.ThangID=@ThangID))
if not exists (select Gio.GioID from gio where (Gio.Gio=@gio and Gio.NgayID=@NgayID))
begin
insert into Gio(Gio,NgayID) values (@Gio,@NgayID)
end
select @GioID =(select Gio.GioID from Gio where (Gio.Gio=@gio and Gio.NgayID=@NgayID))
if not exists (select SoLieuGio.LLNuoc from SoLieuGio where (SoLieuGio.LLNuoc=@LLNuoc and SoLieuGio.GioID= @GioID)) 
begin
insert into SoLieuGio(Gio,LLNuoc,LLNuocC,GioID) values (@Gio,@LLNuoc,@LLNuocC,@GioID)
end

我的代码c＃是：

My code c# is:

private void UpdateLLNUOCBT_Click(object sender, System.EventArgs e)
{
string conn = "Data Source=USER-PC;Initial Catalog=NCKHmoi;Integrated Security=True";
SqlConnection connect = new SqlConnection(conn);
SqlCommand command = new SqlCommand();
command.Connection = connect;
connect.Open();
command.CommandType = CommandType.StoredProcedure;
command.CommandText = "ThemGioLLNuocGio";
command.Parameters.Add("@Ten", SqlDbType.NVarChar, 50).Value = KhuVucComboBox.Text;
command.Parameters.Add("@TenTinh", SqlDbType.NVarChar, 50).Value = TinhComboBox.Text;
command.Parameters.Add("@TenTram", SqlDbType.NVarChar, 50).Value = TramComboBox.Text;
 command.Parameters.Add("@Nam", SqlDbType.Int).Value = nam;
command.Parameters.Add("@Thang", SqlDbType.Int).Value = thang;
command.Parameters.Add("@Ngay", SqlDbType.Int).Value = ngay;
command.Parameters.Add("@Gio", SqlDbType.Int).Value = gio;
command.Parameters.Add("@LLNuoc", SqlDbType.Int).Value = mua;
command.Parameters.Add("@LLNuocC", SqlDbType.Int).Value = 0;
command.ExecuteNonQuery();
}

当我运行代码时。代码很糟糕。程序或功能指定了太多的参数

我不知道它是什么问题。请帮我修理一下。非常感谢。

When i run the code. the code is eror. "procedure or function has too many arguments specified"
I don''t know what problem it is. please help me to fix it. Thank you a lot.

答

检查你的参数。

你可能有一些参数包含但未被您的c＃代码使用

Check your parameters.

There might be some parameters that you have included but is not used by your c# code

以上错误发生在：

1）sql查询/存储过程错误，

2）代码错误（绑定上下文）。

正如您所写，您的SP在MS SQL SMS中执行时没有错误。所以......我们需要在你的C＃代码中找到错误。

初看起来似乎没问题，但试着更换：

Above error occurs when:
1) sql query/stored procedure is wrong,
2) code is wrong (binding context).

As you wrote, your SP executes without errors in MS SQL SMS. So... we need to find errors in your C# code.

At the first look, it seems to be OK, but try to replace:

command.Parameters.Add("@Ten", SqlDbType.NVarChar, 50).Value = KhuVucComboBox.Text;

with

command.Parameters.Add("@Ten", SqlDbType.NVarChar);
command.Parameters["@Ten"].Value = KhuVucComboBox.Text;

或使用 SqlParameterCollection.AddWithValue [ ^ ]方法到添加带参数的参数。

or use SqlParameterCollection.AddWithValue[^] method to add parameter with value.

过程或函数有太多参数指定sql

相关推荐