how to delete using checkbox in asp repeater control with button?

Question

hi can some one help me please. My objective is to allow users to delete multiple rows in the data base called 'messages'.

By selecting the checkboxes user will be able to delete multiple rows after pressing the button.

However nothing happen when i use the codes below. Can some one help me see if there is anything wrong with my codes? Thanks =)

source code

<%@ Page Title="" Language="C#" MasterPageFile="~/MainMasterPage.master" AutoEventWireup="true" CodeFile="Messages.aspx.cs" Inherits="Messages" %>
<%@ Import Namespace="System.Data" %>
<%@ Import Namespace="System.Data.SqlClient" %>

<asp:Content ID="Content1" ContentPlaceHolderID="ContentPlaceHolder1" Runat="Server">

<SCRIPT runat="server">
private void Page_Load(object sender, System.EventArgs e)
{
    String userLog = Session["loginuser"].ToString();
    if (!IsPostBack)
    {
        LoadData();


    }
}
public void LoadData()
{
String userLog = Session["loginuser"].ToString();
SqlDataAdapter adapter = new SqlDataAdapter("SELECT * FROM Messages WHERE receiver = '" + userLog + "'",
    "server=19-20\\sqlexpress;database=mpsip;Integrated Security=SSPI");
DataTable table = new DataTable();
adapter.Fill(table);
Repeater1.DataSource = table;
Repeater1.DataBind();

PagedDataSource pds = new PagedDataSource();
pds.DataSource = table.DefaultView;
pds.AllowPaging = true;
pds.PageSize = 10;

int currentPage;

if (Request.QueryString["page"] != null)
{
    currentPage = Int32.Parse(Request.QueryString["page"]);
}
else
{
    currentPage = 1;
}

pds.CurrentPageIndex = currentPage - 1;
Label1.Text = "Page " + currentPage + " of " + pds.PageCount;

if (!pds.IsFirstPage)
{
    linkPrev.NavigateUrl = Request.CurrentExecutionFilePath + "?page=" + (currentPage - 1);
}

if (!pds.IsLastPage)
{
    linkNext.NavigateUrl = Request.CurrentExecutionFilePath + "?page=" + (currentPage + 1);
}

Repeater1.DataSource = pds;
Repeater1.DataBind();

}

    &nbsp;<asp:Repeater ID="Repeater1" runat="server">

            <itemtemplate>
                <asp:CheckBox ID="CheckBox1" runat="server" />
          <b><%# DataBinder.Eval(Container.DataItem, "title") %></b>

          <br>From UserID: <%# DataBinder.Eval(Container.DataItem, 
                  "mlogin", "{0:d}") %>
              <br />
              Date: <%# DataBinder.Eval(Container.DataItem, 
                  "dateandtime", "{0:d}") %>
              <br />
              MessageID: <%# DataBinder.Eval(Container.DataItem, 
                  "messageID", "{0:d}") %>
                  <br />


       </itemtemplate>
       <separatortemplate>
          <hr>
       </separatortemplate>
        </asp:Repeater>




        <br />
        <asp:HyperLink ID="linkPrev" runat="server">Previous Page</asp:HyperLink>&nbsp;
        <asp:HyperLink ID="linkNext" runat="server">Next Page</asp:HyperLink>

    <br />
    <asp:Button ID="Button7" runat="server" onclick="Button7_Click" 
    Text="Button" />
    <br />
    <br />
    <br />

    <asp:Label ID="Label1" runat="server"></asp:Label>
    <br />
    <asp:Button ID="Button1" runat="server" onclick="Button1_Click" 
        Text="Compose Message" />
    <asp:Button ID="Button2" runat="server" onclick="Button2_Click" 
        Text="Sent Messages" />

</div>

   protected void Button7_Click(object sender, EventArgs e)
{

    using (SqlConnection conn = new SqlConnection("Data Source=19-20\\sqlexpress;" + "Initial Catalog = mpsip; Integrated Security = SSPI"))
    {
        conn.Open();
        SqlCommand cmdDel = conn.CreateCommand();
        SqlTransaction transaction = conn.BeginTransaction("MyTransaction");
        cmdDel.Connection = conn;
        cmdDel.Transaction = transaction;
        try
        {
            for (int i = 0; i < Repeater1.Items.Count; i++)
            {
                //This assumes data type of messageID is integer, change (int) to the right type
                cmdDel.CommandText = "delete from messages where messageID = '" + ((String)((DataRow)Repeater1.Items[i].DataItem)["messageID"]) + "'";
                cmdDel.ExecuteNonQuery();

                // Continue your code here

            }
            transaction.Commit();
        }
        catch (Exception ex)
        {
            try
            {
                transaction.Rollback();
            }
            catch (Exception ex1)
            {
                //TODO: write log
            }
        }
    }


}

}

Add .propertyName of the DataItem (i.e. ((Messages)Repeater1.Items[i].DataItem).messageID — Thinhbk
– Thinhbk, Commented Jul 25, 2012 at 4:34

Thinhbk · Accepted Answer · 2012-07-26 06:20:41Z

1

There's something wrong with your code, at this line:

mysql = "delete from messages where messageID = '" + CheckBox1.Checked + "'";

It should be:

mysql = "delete from messages where messageID = '" + ((YourClass)Repeater1.Items[i].DataItem).messageID + "'";

And you should use SqlParameter instead of concatenating the String.

Besides, SqlCommand was not executed, you should add this line:

cmdDel.ExecuteNonQuery();

And you must reload data after deleting message.

Besides, you should initialize SqlConnection Object out of the for loop, and use SqlTransaction to manage the transaction.

 using (SqlConnection conn = new SqlConnection("Data Source=19-20\\sqlexpress;" + "Initial Catalog = mpsip; Integrated Security = SSPI"))
 {     
    conn.Open();
    SqlCommand cmdDel = conn.CreateCommand();
    SqlTransaction transaction = conn.BeginTransaction("MyTransaction");
    cmdDel.Connection = conn;
    cmdDel.Transaction = transaction;
    try {
        for (int i = 0; i < Repeater1.Items.Count; i++)
        { 
         //This assumes data type of messageID is integer, change (int) to the right type
         cmdDel.CommandText = "delete from messages where messageID = '" + ((int)((DataRow)Repeater1.Items[i].DataItem)["messageID"]) + "'";
         cmdDel.ExecuteNonQuery();

         // Continue your code here

         }
         transaction.Commit();
         //TODO: reload data here and binding to Repeater
    }
    catch(Exception ex) {
        try {
            transaction.Rollback();
        }
        catch(Exception ex1) {
            //TODO: write log
        }
    }
}

edited Jul 26, 2012 at 6:20

answered Jul 25, 2012 at 3:53

Thinhbk

2,2221 gold badge25 silver badges34 bronze badges

Sign up to request clarification or add additional context in comments.

18 Comments

user1550383 Over a year ago

Thanks alot for the answer! im going to try it now.

user1550383 Over a year ago

hi i tried your answer but there are some errors which i do not understand. can u help me look at them? thank you so much =)

Thinhbk Over a year ago

@user1550383: what error? I've just edited the answer, pls check.

user1550383 Over a year ago

@thinkbk i posted the errors on the question. thank you so much for replying so quickly.

user1550383 Over a year ago

error at 'messageID' , (exception e) and transaction.rollback. Having same errors after using the edited codes.

|

user1550383 · Accepted Answer · 2012-07-31 07:02:17Z

protected void Button7_Click(object sender, EventArgs e)
{
    bool BindNeeded = false;

    SqlConnection connDelete = new SqlConnection("Data Source=19-20\\sqlexpress;" + "Initial Catalog = mpsip; Integrated Security = SSPI");
    connDelete.Open();
    String mySQL;


    try
    {

        for (int i = 0; i < Repeater1.Items.Count; i++)
        {
            CheckBox CheckBox1 = (CheckBox)
            Repeater1.Items[i].FindControl("CheckBox1");
            if (((CheckBox)Repeater1.Items[i].FindControl("CheckBox1")).Checked)
            {

                //This assumes data type of messageID is integer, change (int) to the right type
                CheckBox CheckBox = (CheckBox)Repeater1.Items[i].FindControl("CheckBox1");
                Literal litMessageId = (Literal)Repeater1.Items[i].FindControl("litMessageId");

                string messageId = litMessageId.Text;
                mySQL = string.Format("delete from messages where messageID = '{0}'", messageId);

                SqlCommand cmdDelete = new SqlCommand(mySQL, connDelete);
                cmdDelete.ExecuteNonQuery();




                // Continue your code here
            }
            else
            {

            }
        }
            if (BindNeeded)
            {
                Repeater1.DataBind();
            }
            else
            {
                Response.Redirect("Messages.aspx");
            }

    }
        catch
    {
        Response.Redirect("Messages.aspx");
    }

}

Collectives™ on Stack Overflow

how to delete using checkbox in asp repeater control with button?

2 Answers 2

18 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

18 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related