无法通过窗口窗体从表中删除用户

本文关键字：删除 用户 窗体 窗口 | 更新日期: 2023-09-27 18:33:06

我从表中删除用户时遇到问题。我可以将数据从表单插入到表格，但在删除时只会给出 else 语句结果，因为"处理请求时发生一些错误"。员工 ID 是自动递增的。请帮忙。

删除按钮 ： 

 private void btnDeleteUser_Click(object sender, EventArgs e)
    {
        try
        {
            int result = uc.ManageUser(txtFullName.Text, txtAddress.Text, txtPhone.Text, txtEmail.Text, Convert.ToDateTime(dateTimePickerJoinedDate.Text), txtUserame.Text, txtPassword.Text, Convert.ToDateTime(dateTimePickerCreatedDate.Text), "D");
            if (result == 1)
            {
                MessageBox.Show("User Deleted");
                dgvUserDetails.DataSource = uc.SelectAllUsers();
                //MakeFieldsBlank();
            }
            else
            {
                MessageBox.Show("SOME ERRORS OCCURRED WHILE PROCESSING THE REQUEST");
            }
        }
        catch (Exception ex)
        {
            MessageBox.Show(ex.Message);
        }
    }
    private void panel1_Paint(object sender, PaintEventArgs e)
    {
    }
    }
}

管理用户类

public int ManageUser(String Name, String Address, String Phone, String Email, DateTime JoinedDate, String Username, String Password, DateTime CreatedDate, String Mode)
    {
        try
        {
            int result = 0;
            SqlCommand cmd = new SqlCommand("sp_ManageUser", conn);
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.AddWithValue("@StaffID",DBNull.Value);
            cmd.Parameters.AddWithValue("@Name", Name);
            cmd.Parameters.AddWithValue("@Address", Address);
            cmd.Parameters.AddWithValue("@Phone", Phone);
            cmd.Parameters.AddWithValue("@Email", Email);
            cmd.Parameters.AddWithValue("@JoinedDate", JoinedDate);
            cmd.Parameters.AddWithValue("@Username", Username);
            cmd.Parameters.AddWithValue("@Password", Password);
            cmd.Parameters.AddWithValue("@CreatedDate", CreatedDate);
            //cmd.Parameters.AddWithValue("@IsActive", IsActive);
            cmd.Parameters.AddWithValue("@Mode", Mode);

            conn.Open();
            result = cmd.ExecuteNonQuery();
            conn.Close();
            return result;
        }
        catch (Exception ex)
        {
            throw ex;
        }
    }

程序 ： sp_ManageUser

USE [db_ProjectStatusManager]
GO
/****** Object:  StoredProcedure [dbo].[sp_ManageUser]    Script Date: 12/05/2014 01:29:05 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
   -- Author:       <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
ALTER PROCEDURE [dbo].[sp_ManageUser]
-- Add the parameters for the stored procedure here
@StaffID int,
@Name nvarchar(100),
@Address nvarchar(500),
@Phone nvarchar(100),
@Email nvarchar(100),
@JoinedDate date,
@Username nvarchar(50),
@Password nvarchar(max),
@CreatedDate date,
@Mode varchar(1)
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT OFF;

-- Insert statements for procedure here
  if(@Mode='I')
insert into tbl_Staff (Name,Address,Phone,Email,JoinedDate,Username,Password,CreatedDate) values(@Name,@Address,@Phone,@Email,@JoinedDate,@Username,@Password,@CreatedDate)
if(@Mode='U')
Update tbl_Staff set Name=@Name,Address=@Address,Phone=@Phone,Email=@Email,JoinedDate=@JoinedDate,Username=@Username,Password=@Password,CreatedDate=@CreatedDate where StaffID=@StaffID
if(@Mode='D')
 Delete from tbl_Staff where StaffID=@StaffID
end

将用户加载到文本框

    private void FrmUsers_Load(object sender, EventArgs e)
    {
        UserClass uc = new UserClass();
        dgvUserDetails.DataSource = uc.SelectAllUsers();
        dgvUserDetails.AllowUserToAddRows = false;
        dgvUserDetails.AllowUserToOrderColumns = false;
                    panel1.Enabled = false;
    }

用户类。选择所有用户

public DataTable SelectAllUsers()
    {
        try
        {
            SqlCommand cmd = new SqlCommand("Select * from tbl_Staff", conn);
            DataTable dt = new DataTable();
            conn.Open();
            SqlDataReader dr = cmd.ExecuteReader();
            dt.Load(dr);
            conn.Close();
            return dt;
        }
        catch (Exception ex)
        {
            throw ex;
        }
    }

无法通过窗口窗体从表中删除用户

您需要传递参数的值@StaffID因为 SP 需要更新和删除部件的此参数。只有插入部分不需要@StaffID值 

uc.ManageUser(txtStaffID.Text, txtFullName.Text, .......
....
public int ManageUser(string staffID, String Name, ......)
{
    try
    {
        int result = 0;
        SqlCommand cmd = new SqlCommand("sp_ManageUser", conn);
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.Parameters.AddWithValue("@StaffID", Convert.ToInt32(staffID));
        cmd.Parameters.AddWithValue("@Name", Name);
        ....
}

当然，这意味着您需要在加载用户数据时保存该值的位置。
这可以是全局变量或只读模式下的某个文本框，或者隐藏在窗体中或作为 User 类的属性。 （这会好得多。您可以将用户的整个实例传递给您的 UserManager 类，而不是许多单独的参数）

还要注意参数@StaffID的数据类型。SP 需要整数而不是字符串。

您在命令参数的"StaffID"列中传递一个 NULL 值，但您的存储过程具有带有"StaffID"的 where 条件，首先您需要获取"StaffID"，然后传递它。

您可以通过简单的查询获得员工ID

Select StaffID from tbl_Staff where Name=@Name and Username = @Username  ;

您可以按照此代码获取 Staffid 

public int getstaffid()
{ 
int staffid = 0;
SqlCommand cmd = con.CreateCommand();
cmd.CommandType = CommandType.Text;
string query = " Select StaffID from tbl_Staff where Name=@Name and Username = @Username";
cmd.CommandText = query;
SqlParameter param = new SqlParameter("@Name", txtFullName.Text);
cmd.Parameters.Add(param);
SqlParameter param = new SqlParameter("@Username", txtUserame.Text);
cmd.Parameters.Add(param);
 try
{
    con.Open();
    staffid= (Int32)cmd.ExecuteScalar();    
    return staffid;       
}
catch (Exception ex)
{
    throw;
}
}

现在在管理器USer（）

public int ManageUser(String Name, ......)
{
try
{
    int Staffid = getstaffid();
    int result = 0;
    SqlCommand cmd = new SqlCommand("sp_ManageUser", conn);
    cmd.CommandType = CommandType.StoredProcedure;
    cmd.Parameters.AddWithValue("@StaffID",Staffid);
    cmd.Parameters.AddWithValue("@Name", Name);
    ....
}
}
相关文章：