Can't delete User from table through windows form

Question

I am having problem deleting user from table. I can insert the data from form to table but while deleting it only gives else statement result as "SOME ERRORS OCCURRED WHILE PROCESSING THE REQUEST". StaffID is auto increment. Please help.

Delete Button :

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

    }
    }
}

ManageUser Class

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;
        }
    }

Procedure : 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

Load Users To TextBox

    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;
    }

UserClass. SelectAllUsers

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;
        }
    }

Answer 1

You need to pass the value for the parameter @StaffID because the SP requires this parameter for the UPDATE and DELETE parts. It is only the INSERT part that doesn't require the @StaffID value

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);
        ....
}

Of course this means that you need to save somewhere that value when you load your user data.
This could be a global variable or some textbox in readonly mode or hidden in your form or as a property of a User class. (This would a lot better. You could pass the whole instance of a User to your UserManager class instead of a lot of separated parameters)

Also pay attention to the datatype of the parameter @StaffID. The SP expects an integer not a string.

Answer 2

You are passing a NULL value in 'StaffID' column in command parameter but your store procedure has where condition with 'StaffID', first you need to Get the 'StaffID' and then pass the it.

you get the StaffID by simple query

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

You can follow this code to get the 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;
}
}

And Now in ManagerUSer()

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);
    ....
}
}