Hi all,
whenever i use to delete multiple data it deletes only 1 data,not multiple ? plz help
Inline Code:
<%@ Page Language="C#" AutoEventWireup="true" CodeFile="Gridview.aspx.cs" Inherits="Gridview" %>
<%@ Register assembly="AjaxControlToolkit" namespace="AjaxControlToolkit" tagprefix="asp" %>
<%@ Register assembly="Microsoft.ReportViewer.WebForms, Version=10.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a" namespace="Microsoft.Reporting.WebForms" tagprefix="rsweb" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<title>Institute|View Data</title>
<link href="css.css" rel="stylesheet" type="text/css" />
<style type="text/css">
.style1 {color: #FF8400}
.border
{
width: 102%;
}
.style2
{
width: 67%;
}
.style3
{
font-family: Verdana, Arial, Helvetica, sans-serif;
font-size: 11px;
color: #78818B;
padding-right: 15px;
text-align: justify;
height: 32px;
}
.style4
{
font-family: Verdana, Arial, Helvetica, sans-serif;
font-size: 11px;
color: #78818B;
padding-right: 15px;
text-align: justify;
}
</style>
</head>
<body>
<form id="form1" runat="server">
<asp:ScriptManager ID="ScriptManager1" runat="server">
</asp:ScriptManager>
<div>
<table width="780" border="0" align="center" cellpadding="0" cellspacing="0" bgcolor="#FFFFFF" class="border1">
<tr>
<td valign="top"><table width="780" border="0" cellspacing="0" cellpadding="0">
<tr>
<td width="362" valign="top" style="background-image:url(images/index_02.gif);"
class="style7"><div style="padding-top:20px; padding-left:40px;"><a href="#"><img src="images/index_07.gif" alt="" width="247" height="31" border="0" /></a></div></td>
<td width="137" valign="top" style="background-image:url(images/index_02.gif);"
class="style7"><img src="images/index_04.gif" width="137" height="85" alt="" /></td>
<td width="281" valign="middle"
style="background-image:url(images/index_02.gif);" class="style7">
<table width="100%" border="0" align="right" cellpadding="0" cellspacing="3">
<tr>
<td width="35%" style="color:#FF6600; font-weight:bold;">Site Search</td>
<td width="54%"><asp:TextBox ID="TextBox1" runat="server"></asp:TextBox>
<asp:TextBoxWatermarkExtender ID="TextBox1_TextBoxWatermarkExtender"
runat="server" Enabled="True" TargetControlID="TextBox1"
WatermarkText="Enter Your Query Here" SkinID="style3"
WatermarkCssClass="style4">
</asp:TextBoxWatermarkExtender>
</td>
<td width="11%">
<asp:ImageButton ID="ImageButton1" runat="server"
ImageUrl="~/images/index_10.gif" CausesValidation="False" /></td>
</tr>
</table>
</td>
</tr>
<tr>
<td width="362" valign="top"><img src="images/index_14.gif" width="362" height="177" alt="" /></td>
<td width="137" valign="top"><img src="images/index_15.gif" width="137" height="177" alt="" /></td>
<td width="281" valign="top"><img src="images/index_16.gif" width="281" height="177" alt="" /></td>
</tr>
<tr>
<td colspan="3" valign="top"><table width="780" border="0" cellspacing="0" cellpadding="0">
<tr>
<td width="213" height="31" style="background-image:url(images/index_17.gif); font-weight:bold; color:#FFFF00; padding-left:9px;">News Updates!<br />
——————————</td>
<td width="567" height="31" bgcolor="#78818B"><table width="567" border="0" align="center" cellpadding="0" cellspacing="0">
<tr>
<td width="60" class="menu"><a href="Home.aspx">Home</a></td>
<td width="8"><img src="images/index_21.gif" width="8" height="31" alt="" /></td>
<td width="84" class="menu"><a href="About Us.aspx">About Us</a></td>
<td width="8"><img src="images/index_21.gif" width="8" height="31" alt="" /></td>
<td width="92" class="menu"><a href="Studentinfo.aspx">Student</a></td>
<td width="8"><img src="images/index_21.gif" width="8" height="31" alt="" /></td>
<td width="90" class="menu"><a href="Faculty.aspx">Faculty</a></td>
<td width="10"><img src="images/index_21.gif" width="8" height="31" alt="" /></td>
<td width="110" class="menu"><a href="Login.aspx">Login</a></td>
<td width="9"><img src="images/index_21.gif" width="8" height="31" alt="" /></td>
<td width="90" class="menu"><a href="Contact.aspx">Contact Us</a></td>
</tr>
</table></td>
</tr>
</table></td>
</tr>
</table></td>
</tr>
<tr>
<td valign="top"><table width="780" border="0" cellspacing="0" cellpadding="0">
<tr>
<td width="212" valign="top"><table width="211" border="0" cellspacing="0" cellpadding="0">
<tr>
<td width="190" valign="top" bgcolor="#78818B" class="border"><div class="news" style="padding-bottom:5px; padding-top:5px; padding-left:7px;">News & Events</div>
<div class="nbody"><a><marquee direction="up" scrollamount="2"><strong>03/05/06</strong><br />
Lorem ipsum dolor sit amet, consectetuer adipiscing elit. Nullam quis turpis.<br /></marquee></a>
</div>
<div class="nbody"><a><marquee direction="up" scrollamount="2"><strong>03/05/06</strong><br />
Lorem ipsum dolor sit amet, consectetuer adipiscing elit. Nullam quis turpis.</marquee></a>
<br />
</div>
</td>
</tr>
<tr>
<td height="6"></td>
</tr>
<tr>
<td><a href="#"><img src="images/index_34.gif" alt="" width="211" height="106" border="0" /></a></td>
</tr>
<tr>
<td height="6"></td>
</tr>
<tr>
<td><a href="#"><img src="images/index_37.gif" alt="" width="211" height="138" border="0" /></a></td>
</tr>
<tr>
<td height="8"></td>
</tr>
</table></td>
<td width="568" valign="top" style="padding-top:12px;">
<table
border="0" align="center" cellpadding="0" cellspacing="0"
style="margin-right: 0px; width: 104%;">
<tr>
<td valign="top" class="body">
<table border="0" align="center" cellpadding="2" cellspacing="4" class="border">
<tr>
<td height="35" valign="top" class="heading"
style="padding-top:8px; color:#0066FF;">
<strong>View Your Data Here:</strong>
<asp:Label ID="Label1" runat="server" ForeColor="Red"></asp:Label></td>
</tr>
<tr>
<td width="36%" class="body1">
<asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False"
BackColor="#CCFFFF" BorderColor="#336666" BorderStyle="Double" BorderWidth="3px"
CellPadding="4" GridLines="Horizontal" Width="214px" AllowPaging="True"
DataKeyNames="Name" ForeColor="#0099FF"
onpageindexchanging="GridView1_PageIndexChanging"
onrowdeleting="GridView1_RowDeleting" PageIndex="8">
<Columns>
<asp:BoundField DataField="Name" HeaderText="Name" SortExpression="Name" />
<asp:BoundField DataField="Address" HeaderText="Address"
SortExpression="Address" />
<asp:BoundField DataField="City" HeaderText="City" SortExpression="City" />
<asp:BoundField DataField="Country" HeaderText="Country"
SortExpression="Country" />
<asp:BoundField DataField="PhoneNo" HeaderText="PhoneNo"
SortExpression="PhoneNo" />
<asp:BoundField DataField="Emailid" HeaderText="Emailid"
SortExpression="Emailid" />
<asp:BoundField DataField="Comments" HeaderText="Comments"
SortExpression="Comments" />
<asp:CommandField HeaderText="Delete" ShowDeleteButton="True" />
<asp:TemplateField HeaderText="Delete">
<EditItemTemplate>
<asp:CheckBox ID="CheckBox1" runat="server" />
</EditItemTemplate>
<ItemTemplate>
<asp:CheckBox ID="CheckBox1" runat="server" />
</ItemTemplate>
</asp:TemplateField>
</Columns>
<FooterStyle BackColor="White" ForeColor="#333333" />
<HeaderStyle BackColor="#336666" Font-Bold="True" ForeColor="White" />
<PagerSettings Mode="NumericFirstLast" />
<PagerStyle BackColor="#99FF66" ForeColor="White" HorizontalAlign="Center" />
<RowStyle BackColor="White" ForeColor="#333333" />
<SelectedRowStyle BackColor="#339966" Font-Bold="True" ForeColor="White" />
<SortedAscendingCellStyle BackColor="#F7F7F7" />
<SortedAscendingHeaderStyle BackColor="#487575" />
<SortedDescendingCellStyle BackColor="#E5E5E5" />
<SortedDescendingHeaderStyle BackColor="#275353" />
</asp:GridView>
<br />
<br />
<asp:LinkButton ID="LinkButton1" runat="server" onclick="LinkButton1_Click">Select All</asp:LinkButton>
<asp:LinkButton ID="LinkButton2" runat="server" onclick="LinkButton2_Click">Delete</asp:LinkButton>
<br />
<br />
</td>
</tr>
</table>
</td>
</tr>
</table></td>
</tr>
</table></td>
</tr>
<tr>
<td height="94" valign="top" style="background-image:url(images/index_44.gif);"><pre class="footer"><a href="Home.aspx">Home</a> :: <a href="About Us.aspx">About Us</a> :: <a href="Studentinfo.aspx">Student</a> :: <a href="Faculty.aspx">Faculty</a> :: <a href="Login.aspx">Login</a>
:: <a href="contact.aspx">Contact Us</a>
<span class="style1">Copyright© 2006 companyname.com, All rights Reserved</span></pre></td>
</tr>
</table>
</div>
</form>
</body>
</html>
Code Behind:
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;
public partial class Gridview : System.Web.UI.Page
{
void loaduser()
{
con.Open();
SqlDataAdapter adp = new SqlDataAdapter("Select * from ContactTable", con);
DataSet ds = new DataSet();
adp.Fill(ds);
GridView1.DataSource = ds;
GridView1.DataBind();
}
SqlConnection con = new SqlConnection();
protected void Page_Load(object sender, EventArgs e)
{
con.ConnectionString = "Data Source=.\SQLEXPRESS;AttachDbFilename=C:\Users\hp\Documents\Visual Studio 2010\WebSites\Institutemgmtsystm\App_Data\Database.mdf;Integrated Security=True;User Instance=True";
if (!Page.IsPostBack)
{
loaduser();
}
}
protected void GridView1_PageIndexChanging(object sender, GridViewPageEventArgs e)
{
GridView1.PageIndex = e.NewPageIndex;
loaduser();
}
protected void GridView1_RowDeleting(object sender, GridViewDeleteEventArgs e)
{
con.Open();
SqlCommand cmd = new SqlCommand();
cmd.Connection = con;
String name = GridView1.DataKeys[e.RowIndex].Value.ToString();
cmd.CommandText = "Delete from ContactTable where Name=’" + name + "’";
cmd.ExecuteNonQuery();
Label1.Text = "Your data is deleted!!";
con.Close();
loaduser();
}
protected void LinkButton1_Click(object sender, EventArgs e)
{
con.Open();
CheckBox chk;
for (int i = 0; i <= GridView1.Rows.Count – 1; i++)
{
chk = (CheckBox)GridView1.Rows[i].FindControl("CheckBox1");
chk.Checked = true;
}
}
protected void LinkButton2_Click(object sender, EventArgs e)
{
string strUsername = "", a = "";
CheckBox chk;
for (int i = 0; i <= GridView1.Rows.Count – 1; i++)
{
chk = (CheckBox)GridView1.Rows[i].FindControl("CheckBox1");
if (chk.Checked)
{
con.Open();
strUsername = GridView1.DataKeys[i].Value.ToString();
SqlCommand cmdDelete = new SqlCommand();
cmdDelete.Connection = con;
string strDelete = "Delete from ContactTable where Name=’" + strUsername + "’";
cmdDelete.CommandText = strDelete;
cmdDelete.ExecuteNonQuery();
a = a + strUsername;
con.Close();
Label1.Text = "Your records are deleted";
loaduser();
}
}
}
}
One thing you should fix is the security whole that you’ve created called a SQL inject – don’t manually put your sql strings together like this:
string strDelete = "Delete from ContactTable where Name='" + strUsername + "'";
You should put them together using something called parameterised queries:
<div>So the code should look more like this:</div>
<div>
strUsername = GridView1.DataKeys[i].Value.ToString();
string commandText = "Delete from ContactTable where Name = @username";
using (SqlConnection connection = new SqlConnection(connectionString))
{
SqlCommand command = new SqlCommand(commandText, connection);
command.Parameters.Add("@username", SqlDbType.String);
command.Parameters["@username"].Value = strUsername;
try
{
connection.Open();
Int32 rowsAffected = command.ExecuteNonQuery();
//Console.WriteLine("RowsAffected: {0}", rowsAffected);
}
catch (Exception ex)
{
//Console.WriteLine(ex.Message);
}
}
(adapted from http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlcommand.parameters.aspx)</div>
I also noticed that you are loading the whole table in with a loaduser() call every loop which doens’t seem right.
I’ve looked over the rest of the code and to be honest it doesn’t seem like it has a problem. I would suggest setting a breakpoint and stepping through the code. My guess would be that its something going wrong with the database code and its failing in the
background.
Move loaduser(); to outside the loop, place it after all already deleted.