i am trying the code for deleting records in the gridview, but after selecting ckeckbox and clicking on delete button all the rows data is getting deleted, i wanted to delete only check box marked row data. i am not getting what mistake i have did. my sample code is shown below.
my .aspx code is
<%@ Page Language="C#" AutoEventWireup="true" CodeFile="Default3.aspx.cs" Inherits="Default3" Debug="true"%>
and my .aspx.cs code is
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data;
using System.Data.SqlClient;
using System.Collections.Specialized;
using System.Text;
using System.Collections;
public partial class Default3 : System.Web.UI.Page
{
SqlConnection con = new SqlConnection();
SqlCommand cmd = new SqlCommand();
string connectionString = "Data Source=hobvision07;Initial Catalog=master; user id=sa;password=hobvision";
protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
BindData();
btnDelete.Attributes.Add("onclick",
"return confirm('Are you sure you want to delete selected item(s) ?');");
}
}
private void BindData()
{
SqlConnection con = new SqlConnection("Data source=hobvision07; initial catalog=master; user id=sa; password=hobvision");
SqlDataAdapter da = new SqlDataAdapter("SELECT cat_id, cat_name,location,date FROM quest_categories", con);
DataTable dt = new DataTable();
da.Fill(dt);
GridView1.DataSource = dt;
GridView1.DataBind();
}
protected void Button1_Click(object sender, EventArgs e)
{
SqlConnection con = new SqlConnection("Data source=hobvision07; initial catalog=master; user id=sa; password=hobvision");
SqlDataAdapter da = new SqlDataAdapter("SELECT cat_id, cat_name,location,date FROM quest_categories", con);
DataTable dt = new DataTable();
da.Fill(dt);
// Here we'll add a blank row to the returned DataTable
DataRow dr = dt.NewRow();
dt.Rows.InsertAt(dr, 0);
//Creating the first row of GridView to be Editable
GridView1.EditIndex = 0;
GridView1.DataSource = dt;
GridView1.DataBind();
//Changing the Text for Inserting a New Record
((LinkButton)GridView1.Rows[0].Cells[1].Controls[0]).Text = "Insert";
}
protected void GridView1_RowEditing(object sender, GridViewEditEventArgs e)
{
GridView1.EditIndex = e.NewEditIndex;
BindData();
}
protected void GridView1_RowCancelingEdit(object sender, GridViewCancelEditEventArgs e)
{
GridView1.EditIndex = -1;
BindData();
}
protected void GridView1_RowUpdating(object sender, GridViewUpdateEventArgs e)
{
if (((LinkButton)GridView1.Rows[0].Cells[1].Controls[0]).Text == "Insert")
{
SqlConnection con = new SqlConnection("Data source=hobvision07; initial catalog=master; user id=sa; password=hobvision");
SqlCommand cmd = new SqlCommand();
cmd.CommandText = "INSERT INTO quest_categories(cat_name,location,date) VALUES(@cat_name,@location,@date)"; cmd.Parameters.Add("@cat_name", SqlDbType.VarChar).Value = ((TextBox)GridView1.Rows[0].Cells[3].Controls[0]).Text;
cmd.Parameters.Add("@location", SqlDbType.VarChar).Value = ((TextBox)GridView1.Rows[0].Cells[4].Controls[0]).Text;
cmd.Parameters.Add("@date", SqlDbType.VarChar).Value = ((TextBox)GridView1.Rows[0].Cells[5].Controls[0]).Text;
cmd.Connection = con;
con.Open();
cmd.ExecuteNonQuery();
con.Close();
}
else
{
SqlConnection con = new SqlConnection("Data source=hobvision07; initial catalog=master; user id=sa; password=hobvision");
SqlCommand cmd = new SqlCommand();
cmd.CommandText = "UPDATE quest_categories SET cat_name=@cat_name,location=@location,date=@date WHERE cat_id=@cat_id";
cmd.Parameters.Add("@cat_name", SqlDbType.VarChar).Value = ((TextBox)GridView1.Rows[e.RowIndex].Cells[3].Controls[0]).Text;
cmd.Parameters.Add("@location", SqlDbType.VarChar).Value = ((TextBox)GridView1.Rows[e.RowIndex].Cells[4].Controls[0]).Text;
cmd.Parameters.Add("@date", SqlDbType.VarChar).Value = ((TextBox)GridView1.Rows[e.RowIndex].Cells[5].Controls[0]).Text;
cmd.Parameters.Add("@cat_id", SqlDbType.Int).Value = Convert.ToInt32(GridView1.Rows[e.RowIndex].Cells[2].Text);
cmd.Connection = con;
con.Open();
cmd.ExecuteNonQuery();
con.Close();
}
GridView1.EditIndex = -1;
BindData();
}
protected void btnDelete_Click(object sender, EventArgs e)
{
ArrayList productsToDelete = new ArrayList();
foreach (GridViewRow row in GridView1.Rows)
{
if (row.RowType == DataControlRowType.DataRow)
{
CheckBox chkDelete = (CheckBox)row.Cells[0].FindControl("chkDelete");
if (chkDelete != null)
{
if (chkDelete.Checked)
{
string productId = row.Cells[1].Text;
productsToDelete.Add(productId);
}
}
}
}
DeleteProducts(productsToDelete);
BindData();
}
private void DeleteProducts(ArrayList productsToDelete)
{
foreach (GridViewRow row in GridView1.Rows)
{
string productId = row.Cells[2].Text;
SqlConnection con = new SqlConnection("Data source=hobvision07; initial catalog=master; user id=sa; password=hobvision");
SqlDataAdapter cmd = new SqlDataAdapter("delete from quest_categories where cat_id='" + productId + "'", con);
DataTable dt = new DataTable();
cmd.Fill(dt);
GridView1.DataSource = dt;
GridView1.DataBind();
//BindData();
}
}
}
Please help me regarding this.
AshwiniPosted Nov 8, 2011, 4:12 AM
i got the solution for my problem i have done just like this
private void DeleteProducts(ArrayList productsToDelete)
{
private void DeleteProducts(StringCollection idCollection)
{
//Create sql Connection and Sql Command
SqlConnection con = new SqlConnection("data source=hobvision07; initial catalog=master; user id=sa; password=hobvision");
SqlCommand cmd = new SqlCommand();
string IDs = "";
foreach (string id in idCollection)
{
IDs += id.ToString() + ",";
}
try
{
string strIDs =
IDs.Substring(0, IDs.LastIndexOf(","));
string strSql = "Delete from quest_categories WHERE cat_id in (" + strIDs + ")";
cmd.CommandType = CommandType.Text;
cmd.CommandText = strSql;
cmd.Connection = con;
con.Open();
cmd.ExecuteNonQuery();
}
catch (SqlException ex)
{
string errorMsg = "Error in Deletion";
errorMsg += ex.Message;
throw new Exception(errorMsg);
}
finally
{
con.Close();
}
}
AshwiniPosted Nov 8, 2011, 2:32 AM
TulasiPosted Nov 8, 2011, 1:24 AM
AshwiniPosted Nov 8, 2011, 1:14 AM
I have tried but after selecting checkbox and click on delete selected records button the
values are not getting deleted. I think you missed some thing in that code. please help me.
TulasiPosted Nov 8, 2011, 12:57 AM
HI Kavay
Check the below code
private void DeleteProducts(ArrayList productsToDelete)
{
for(int i=0;i<productsToDelete.Count;i++)
{
string productId =productsToDelete[i].ToString();
SqlConnection con = new SqlConnection("Data source=hobvision07; initial catalog=master; user id=sa; password=hobvision");
SqlCommand cmd = new SqlCommand("delete from quest_categories where cat_id=" + productId, con);
con.Open();
cmd.ExecuteNonQuery();
}
SqlDataAdapter dad = new SqlDataAdapter("select * from quest_categories ", con);
DataSet ds = new DataSet();
dad.Fill(ds);
con.Close();
GridView1.DataSource = ds.Table[0];
GridView1.DataBind();
//BindData();
}
}