CODEWITHSUNDEEP
c#sql-server
xVenum.dll
xVenum.dll

Reputation: 73

How to create "Delete button for deleting grid columns from SQL Server database in C#"?

I need a part of the code where I can delete a grid column or row from the database by clicking a delete button, and it should be done with C#.

I tried deleting it by MVVM architecture and I believe I wrote something wrong. Is there any other way to do this?

public void DeleteAuction()
{
     using (SqlConnection conn = new SqlConnection())
     {
         conn.ConnectionString = ConfigurationManager.ConnectionStrings["ConnString"].ToString();
         conn.Open();

         SqlCommand command = new SqlCommand("UPDATE AuctionTbl2 SET deleted = 1 WHERE id = @Id", conn);

         SqlParameter myParam = new SqlParameter("@Id", SqlDbType.Int, 11);
         myParam.Value = this.Id;

         command.Parameters.Add(myParam);

         int rows = command.ExecuteNonQuery();
     }
}

Now I made constructors, properties and fields, and that works okay. I tried to connect data from SQL Server to C#, that worked okay as well, but when I press the delete button it doesn't work. No errors, no nothing. Please help, I'm getting very desperate now.

[This is when delete is clicked, it should go in DB from 0 --> 1 for deleted[1]

Like this

Upvotes: 3

Views: 1069

Answers (2)

Zain Arshad
Zain Arshad

Reputation: 1907

UPDATE: You should try this code, I have tested it personally at it is running like a charm. Do tell me if it gets you what you want.

public void DeleteAuction()
{
    try
    {
        bool isSuccess = false;
        DataTable dt = new DataTable(); 
        using (SqlConnection conn = new SqlConnection())
        {
            conn.ConnectionString = ConfigurationManager.ConnectionStrings["ConnString"].ToString();
            conn.Open();
            SqlCommand command = new SqlCommand("UPDATE AuctionTbl2 SET deleted = 1 WHERE id = @Id", conn);
            command.Parameters.AddWithValue("@Id",Id);
            int rows = command.ExecuteNonQuery();
            if(rows>0)
            {
                MessageBox.Show("Deletion Successfull");
                dt = selectAuction();
                auctionDataGridView.DataSource = dt;
            }
            else
                MessageBox.Show("Deletion Unsuccessfull");
        }
    }
}
catch(Exception e)
{
    MessageBox.Show(e.Message);
}

//method to update the dataGridView after deletion
public DataTable selectAuction()
{
    try
    {
        DataTable table = new DataTable(); 
        using (SqlConnection conn = new SqlConnection())
        {
            conn.ConnectionString = ConfigurationManager.ConnectionStrings["ConnString"].ToString();
            conn.Open();
            String sql = "select * from AuctionTbl2";          
            SqlCommand cmd = new SqlCommand(sql, con);         
            SqlDataAdapter adaptor = new SqlDataAdapter(cmd);                                         
            adaptor.Fill(table);    
        }               
    }
    catch (Exception e)
    {
        MessageBox.Show(e.Message);                 
    }
    finally
    {
        con.Close();                 
    }
    return table;
}

NOTE: I have tried to use variables just like yours, but if there is one sneaky different variable name, then pardon me.

Upvotes: 1

cSkillzMartin
cSkillzMartin

Reputation: 152

There are a couple of ways you could approach the problem. 1) Execute an SQL command to set the record's deleted column to one. Then rebind the DataGridView. 2) Delete the row from the DataGridView, then execute a SQL command to set the record's Deleted column to 1.

I like the number 2 option because it moves the least amount of data around. Assuming method 2, it would go something like this:

private void DeleteSale_Click(object sender, EventArgs e)
{ 
      var RowId = dataGridView.Rows[dataGridView.CurrentCell.RowIndex].Cells[0].Value.ToString(); 
    dataGridView.Rows.RemoveAt(dataGridView.CurrentCell.RowIndex);


    using(var connection = new SqlConnection("myConnectionString")
    {
         using(var command = new SqlCommand("myStoredProcedureName", connection))
         {
              command.CommandType = CommandType.StoredProcedure;
              command.Parameters.Add(new SqlParameter("Id", RowId));
              command.Connection.Open();
              command.ExecuteNonQuery();
              command.Connection.Close();
          }
    }    
}

Just FYI: Never write inline SQL statements like that, always use parameter stored procedures.

Upvotes: 0

Related Questions