Delete Row

[dawsonz]

Hey,

I currently have a dataGridView which displays information from my SQL database. I have a button that I use to update/add into the SQL database - for example a user ammends a field in the dataGridView this automatically updates the database.

I now want to implement a button that deletes the selected row from the database.

My dataGridView loadData:

private void LoadData()
    {
        string connectionString = @"Data Source=localhost\sqlexpress;Initial Catalog=coffee;Integrated Security=True;Pooling=False";
            conn = new SqlConnection(connectionString);
            sql = "SELECT * FROM coffeeType2";

            da = new SqlDataAdapter(sql, conn);
            conn.Open();
            ds = new DataSet();
            SqlCommandBuilder commandBuilder = new SqlCommandBuilder(da);
            da.Fill(ds, "coffeeType2");
            bsource.DataSource = ds.Tables["coffeeType2"];
            dataGridView1.DataSource = bsource;

            // this.dataGridView1.AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.Fill;
                        
            dataGridView1.AutoResizeColumns();
            dataGridView1.AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.AllCells;
        }

Add/Update Button:

private void btnAdd_Click(object sender, EventArgs e)
        {
            DataTable dt = ds.Tables["coffeeType2"];
            this.dataGridView1.BindingContext[dt].EndCurrentEdit();
            this.da.Update(dt);

            LoadData();
        }

How can I now create a button to delete that particular row from the database?

Thank you.

[phoenix911]

http://www.codeguru.com/csharp/.net/net_data/datagrid/article.php/c13041
http://social.msdn.microsoft.com/forums/en-US/winformsdatacontrols/thread/fbe691ae-ec83-4a09-8766-0088cfcc1ea3/

thers many posts out there concerning this a quick google search got me this...

[dawsonz]

Ok thank you i'll have a look.

Is there anyway i'm able to get the ID of a select row at all?

Say my datagridview has ID, name, location.

When I click a cell can I get the ID of that particular row?

[phoenix911]

there should be a way yes

[dawsonz]

Ok, do you have any idea how I would do this?

[dawsonz]

Can anyone be kind enough to help me with this?

[yaninea]

Add a column in your grid with a button or an image button as is set on my example:

protected void ImageButton2_Click(object sender, ImageClickEventArgs e)
    {
        ImageButton Button1 = (ImageButton)sender;
        GridViewRow grdRow = (GridViewRow)Button1.Parent.Parent;
        DataSourceSelectArguments args = new DataSourceSelectArguments();
        DataView view = (DataView)this.SqlDataSource2.Select(args);
        DataTable table = view.ToTable();
        string id = table.Rows[grdRow.RowIndex].ItemArray[0].ToString();
     }

Hope it helps. let me know if you need a futher explanation. Good luck.

[dawsonz]

Add a column in your grid with a button or an image button as is set on my example:

protected void ImageButton2_Click(object sender, ImageClickEventArgs e)
    {
        ImageButton Button1 = (ImageButton)sender;
        GridViewRow grdRow = (GridViewRow)Button1.Parent.Parent;
        DataSourceSelectArguments args = new DataSourceSelectArguments();
        DataView view = (DataView)this.SqlDataSource2.Select(args);
        DataTable table = view.ToTable();
        string id = table.Rows[grdRow.RowIndex].ItemArray[0].ToString();
     }

Hope it helps. let me know if you need a futher explanation. Good luck.

Hi i've added a button onto my datagridivew its on their as column.

I'm having trouble getting the above working however.

I've got a button (btnDelete) am I able to link that up, is there anyway I can simply get the coffeeID from the datagridview row that has been selected?

I have something like this:

DataTable dt = ds.Tables["coffeeType2"];
            label1.Text = System.Convert.ToString(dt.Rows[0][2]);

However that just selects the DataTable id second row - how can I do it dependent on what row the user has selected?

[dawsonz]

I've now sorted this

lblCustomerID.Text = dataGridView1.CurrentRow.Cells["customerID"].FormattedValue.ToString();

            string connectionString = @"Data Source=localhost\sqlexpress;Initial Catalog=coffee;Integrated Security=True;Pooling=False";
            conn = new SqlConnection(connectionString);
            sqlDelete = "DELETE FROM customer WHERE customerID = '" + lblCustomerID.Text + "'";
            SqlCommand cmd = new SqlCommand();
            cmd.CommandText = sqlDelete;
            cmd.Connection = conn;
            conn.Open();
            cmd.ExecuteNonQuery();
            conn.Close();

Thanks for any help anyhow.

[yaninea]

You will now have a gridview with a column that cointains a button "btnDelete".
You need to create an event for the btnDelete_Click and on it add the code I posted.
The code will get the row id from the row where the button was clicked, or well you can actually obtain any value from the datasource related to the grid, you just need to set the value position that you need:
string id = table.Rows[grdRow.RowIndex].ItemArray[[B]0[/B]].ToString();
In the explample, the row id is the first value from the query in the SqlDataSource2, meaning, the position 0.
You need to create an event for the btnDelete_Click and on it add the code I posted.

#
protected void ImageButton2_Click(object sender, ImageClickEventArgs e)
#
{
#
ImageButton Button1 = (ImageButton)sender;
#
GridViewRow grdRow = (GridViewRow)Button1.Parent.Parent;
#
DataSourceSelectArguments args = new DataSourceSelectArguments();
#
DataView view = (DataView)this.SqlDataSource2.Select(args);
#
DataTable table = view.ToTable();
#
string id = table.Rows[grdRow.RowIndex].ItemArray[0].ToString();
#
string name= table.Rows[grdRow.RowIndex].ItemArray[1].ToString();
}

Let me know how it went, I can send you an app if you need.