调用存储过程不会导致在表中插入行

本文关键字：插入 存储过程 调用 | 更新日期: 2023-09-27 17:56:53

我的存储过程是：

ALTER PROCEDURE [dbo].[ThemTram] 
(   
    @Ten nvarchar(50),
    @KhuVucID int,
    @TenTinh nvarchar(50),
    @TinhID int,
    @TenTram nvarchar(50)
)
AS
set @KhuVucID = (select KhuVuc.KhuVucID from KhuVuc where KhuVuc.Ten=@ten)
set @TinhID =(select Tinh.TinhID from Tinh Where (Tinh.TenTinh=@TenTinh and Tinh.KhuVucID=@KhuVucID))
if not exists (select Tram.TenTram from Tram where (Tram.TenTram=@TenTram and Tram.TinhID = @TinhID))
Begin
    insert into Tram(TenTram,TinhID) values (@TenTram,@TinhID)
end

和我的 C# 程序：

    private void ThemTramBT_Click(object sender, 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 = "ThemTram";
        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 = ThemTramTB.Text;
        command.Dispose();
        connect.Close();
    }

但是在 C# 代码中有一个问题。 我无法在其中插入新数据。请帮助我。

调用存储过程不会导致在表中插入行

您没有执行该命令。作为旁白，您实际上应该将using语句与SqlConnection和（不太重要）SqlCommand一起使用：

private void ThemTramBT_Click(object sender, EventArgs e)
{
    string conn = "Data Source=USER-PC;Initial Catalog=NCKHmoi;Integrated Security=True";
    using (SqlConnection connect = new SqlConnection(conn))
    {
        using (SqlCommand command = new SqlCommand("ThemTram", conn))
        {
            connect.Open();
            command.CommandType = CommandType.StoredProcedure;
            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 = ThemTramTB.Text;
            command.Parameters.AddWithValue("@KhuVucID", DBNull.Value); //need to pass even if not used
            command.Parameters.AddWithValue("@TinhID", DBNull.Value); //need to pass even if not used
            command.ExecuteNonQuery();
        }
    }
}

编辑：

根据您的评论，不清楚为什么您的 sproc 是这样编码的。我建议你按如下方式更改它：

ALTER PROCEDURE [dbo].[ThemTram] 
(   
    @Ten nvarchar(50),
    @TenTinh nvarchar(50),
    @TenTram nvarchar(50)
)
AS
declare @KhuVucID int
declare @TinhID int
select @KhuVucID = KhuVuc.KhuVucID from KhuVuc where KhuVuc.Ten = @ten
select @TinhID = Tinh.TinhID from Tinh Where Tinh.TenTinh = @TenTinh and Tinh.KhuVucID = @KhuVucID
if not exists (select Tram.TenTram from Tram where Tram.TenTram = @TenTram and Tram.TinhID = @TinhID)
begin
    insert into Tram(TenTram, TinhID) values (@TenTram, @TinhID)
end
相关文章：