locked
Deleting items in ArrayList from Access Database RRS feed

  • Question

  • User-947628898 posted

    Hi I am trying to delete records in a database by selecting the rows in a gridview using a checkbox and then saving the selected items to an arraylist. Then I want to iterate through the arraylist and delete the corresponding rows in the database. The code I am using is below. I'm not getting any errors but the records aren't being deleted from the database. Any help on this would be greatly appriciated. It might be something really simple but I haven't got that much experience so i'm stumped. 

    protected void Page_Load(object sender, EventArgs e)
         {
             if (!Page.IsPostBack)
             {
                 BindData();
    
                 btnDelete.Attributes.Add("OnClick",
                    "return confirm('Are you sure you want to delete selected item(s) ?');");
    
    
             }
         }
         protected void BindData()
         {
             System.Data.OleDb.OleDbConnection connection = null;
             System.Data.OleDb.OleDbDataReader reader = null;
             try
             {
    
                 string constr = @"PROVIDER=Microsoft.ACE.OLEDB.12.0; Data Source=C:\Users\Marie\Documents\Visual Studio 2010\Projects\BCIStatScheme\Begin\BCIStatScheme\App_Data\BCIStatScheme.accdb";
    
    
                 connection = new System.Data.OleDb.OleDbConnection(constr);
                 connection.Open();
    
                 System.Data.OleDb.OleDbCommand cmd =
                 new System.Data.OleDb.OleDbCommand("Select * FROM PromoList", connection);
                 reader = cmd.ExecuteReader();
    
                 GridView1.DataSource = reader;
                 GridView1.DataBind();
             }
    
             finally
             {
                 if (reader != null) reader.Close();
                 if (connection != null) connection.Close();
             }
         }
    
     
    protected void btnDelete_Click(object sender, EventArgs e)
            {
                ArrayList productsToDelete = new ArrayList();
    
                for (int i = 0; i < GridView1.Rows.Count; i++)
                {
                   
                  
                        CheckBox chkDelete = (CheckBox)GridView1.Rows[i].Cells[0].FindControl("chkDelete");
    
                        if (chkDelete != null)
                        {
                            if (chkDelete.Checked)
                            {
                                string promoId = GridView1.Rows[i].Cells[1].Text;
                                productsToDelete.Add(promoId);
                                
                            }
                      
                    }
    
                    
                }
                
                DeleteProducts(productsToDelete);
                BindData();
                
            }
    
           
            private void DeleteProducts(ArrayList productsToDelete)
            {
                 // Execute SQL DELETE Command to Delete all Products 
                // available in productsToDelete collection 
                for (int i = 0; i < productsToDelete.Count; i++)
                {
    
                    int j = (int.Parse(productsToDelete[i].ToString()));
                    string sql = "Delete FROM PromoList WHERE PromoID=" + j;
                }
                // You can also pass productsToDelete ArrayList to 
                // your Data Access Layer component for delete operation 
            }
    </script>
    
    
    My gridview is as follows:
    
    <asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" 
                     Width="98%">
       <Columns>
          <asp:TemplateField HeaderText="Delete">
             <ItemTemplate>
                <asp:CheckBox runat="server" ID="chkDelete" />
             </ItemTemplate>
          </asp:TemplateField>
          
          <asp:BoundField DataField="PromoID" HeaderText="Promotion ID">
             <HeaderStyle HorizontalAlign="Center" />
             <ItemStyle HorizontalAlign="Center" />
          </asp:BoundField>
          
          <asp:BoundField DataField="CategoryID" HeaderText="Category ID">
             <HeaderStyle HorizontalAlign="Left" />
             <ItemStyle HorizontalAlign="Left" />
          </asp:BoundField>
    
          <asp:BoundField DataField="Category" HeaderText="Category">
             <HeaderStyle HorizontalAlign="Left" />
             <ItemStyle HorizontalAlign="Left" />
          </asp:BoundField>
    
          <asp:BoundField DataField="Product" HeaderText="Product">
             <HeaderStyle HorizontalAlign="Left" />
             <ItemStyle HorizontalAlign="Left" />
          </asp:BoundField>
    
          <asp:BoundField DataField="Promotion" HeaderText="Promotion">
             <HeaderStyle HorizontalAlign="Left" />
             <ItemStyle HorizontalAlign="Left" />
          </asp:BoundField>
       </Columns>
    </asp:GridView>
    
            <br />
    <asp:Button ID="btnDelete" runat="server" OnClick="btnDelete_Click" Text="Delete Selected Records" />
    					
    			    
    Friday, July 29, 2011 10:37 AM

Answers

  • User1224194097 posted

    string sql = "Delete FROM PromoList WHERE PromoID=" + j;

    Are you sure you are called ExecuteNonQuery method for the delete command you specified?

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, July 29, 2011 10:48 AM
  • User-2010311731 posted

    A suggestion on your current code would be to change your for loop into a foreach loop.  It makes it much easier to work with...

    foreach (GridViewRow row in GridView1.Rows)
    {
        CheckBox chkDelete = (CheckBox)row.FindControl("chkDelete");
    
        if (chkDelete != null)
        {
            if (chkDelete.Checked)
            {
                string promoId = row.Cells[1].Text;
                productsToDelete.Add(promoId);
            }
        }
    }

    That being said, GridView has quite a few add/change/delete functions built into it.  Here are some references you might explore that could make the whole thing quite a bit easier to develop.

    http://www.asp.net/data-access/tutorials/an-overview-of-inserting-updating-and-deleting-data-cs

    Matt

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, July 29, 2011 10:49 AM
  • User1266497125 posted

    Hi,

    The problem is in your method private void DeleteProducts(ArrayList productsToDelete). Here you are just iterating over items in an array and building an SQL DELETE in a string (string sql = "Delete FROM PromoList WHERE PromoID=" + j;), but you never call OleDbCommand object to execute the command on the server. Your method should look something like this:

    private void DeleteProducts(ArrayList productsToDelete)
            {
                 // Execute SQL DELETE Command to Delete all Products 
                // available in productsToDelete collection 
    			
    			string constr = @"PROVIDER=Microsoft.ACE.OLEDB.12.0; Data Source=C:\Users\Marie\Documents\Visual Studio 2010\Projects\BCIStatScheme\Begin\BCIStatScheme\App_Data\BCIStatScheme.accdb";
    			System.Data.OleDb.OleDbConnection connection = new System.Data.OleDb.OleDbConnection(constr);
    			try
    			{
    				connection.Open();
    					 
    				for (int i = 0; i < productsToDelete.Count; i++)
    				{
    
    					int j = (int.Parse(productsToDelete[i].ToString()));
    					string sql = "Delete FROM PromoList WHERE PromoID=" + j;
    				
    					 System.Data.OleDb.OleDbCommand cmd =
    					 new System.Data.OleDb.OleDbCommand(sql, connection);
    					 int rowsAffected = cmd.ExecuteNonQuery();
    				 }
    			 }
    			 catch(Exception)
    			 {
    			 
    			 }
    			 finally
    			 {
    				connection.Close();
    			 }
            }

    Best regards,

    Huske

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, July 29, 2011 10:53 AM

All replies

  • User1224194097 posted

    string sql = "Delete FROM PromoList WHERE PromoID=" + j;

    Are you sure you are called ExecuteNonQuery method for the delete command you specified?

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, July 29, 2011 10:48 AM
  • User-2010311731 posted

    A suggestion on your current code would be to change your for loop into a foreach loop.  It makes it much easier to work with...

    foreach (GridViewRow row in GridView1.Rows)
    {
        CheckBox chkDelete = (CheckBox)row.FindControl("chkDelete");
    
        if (chkDelete != null)
        {
            if (chkDelete.Checked)
            {
                string promoId = row.Cells[1].Text;
                productsToDelete.Add(promoId);
            }
        }
    }

    That being said, GridView has quite a few add/change/delete functions built into it.  Here are some references you might explore that could make the whole thing quite a bit easier to develop.

    http://www.asp.net/data-access/tutorials/an-overview-of-inserting-updating-and-deleting-data-cs

    Matt

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, July 29, 2011 10:49 AM
  • User1266497125 posted

    Hi,

    The problem is in your method private void DeleteProducts(ArrayList productsToDelete). Here you are just iterating over items in an array and building an SQL DELETE in a string (string sql = "Delete FROM PromoList WHERE PromoID=" + j;), but you never call OleDbCommand object to execute the command on the server. Your method should look something like this:

    private void DeleteProducts(ArrayList productsToDelete)
            {
                 // Execute SQL DELETE Command to Delete all Products 
                // available in productsToDelete collection 
    			
    			string constr = @"PROVIDER=Microsoft.ACE.OLEDB.12.0; Data Source=C:\Users\Marie\Documents\Visual Studio 2010\Projects\BCIStatScheme\Begin\BCIStatScheme\App_Data\BCIStatScheme.accdb";
    			System.Data.OleDb.OleDbConnection connection = new System.Data.OleDb.OleDbConnection(constr);
    			try
    			{
    				connection.Open();
    					 
    				for (int i = 0; i < productsToDelete.Count; i++)
    				{
    
    					int j = (int.Parse(productsToDelete[i].ToString()));
    					string sql = "Delete FROM PromoList WHERE PromoID=" + j;
    				
    					 System.Data.OleDb.OleDbCommand cmd =
    					 new System.Data.OleDb.OleDbCommand(sql, connection);
    					 int rowsAffected = cmd.ExecuteNonQuery();
    				 }
    			 }
    			 catch(Exception)
    			 {
    			 
    			 }
    			 finally
    			 {
    				connection.Close();
    			 }
            }

    Best regards,

    Huske

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, July 29, 2011 10:53 AM
  • User-947628898 posted

    Hi Matt, Huske, Santhosh,

    Thank you all so very much for your replies, you have solved my problem and its working!:-) 

    Much appriciated!

    Marie

    Saturday, July 30, 2011 5:58 AM