Delete multiple records using checkboxes

[narmi2]

Hi

Im Trying to make a datagrid using db connection and asp.net(vb.net).

So far I have got it to display db contents in the grip, allowing user to add new records, delete 1 record at a time and edit 1 record at a time. This is down by having the delete and edit buttons with every row.

I now want to allow user to check the checkboxes next to each record so multiple records can be deleted at once.

How do I do this. I think I need to use some sort of loop.

This is what I have so far:

<%@ Page Language="VB" runat="server" debug="true" explicit="true" aspcompat=true validateRequest=false%> 
<%@ Import Namespace="System.Data" %>
<%@ Import Namespace="System.Data.SqlClient" %>
 
<script language="VB" runat="server">
Dim MyConn As SqlConnection                         'declare connection
Dim MySQL as String 'store sql string
Sub Page_Load(sender as Object, e as EventArgs)     'for page load
    MyConn = New SqlConnection("abcd")              'connects to intranet db
    page_title.text="test"                          'label on top of page
    If Not Page.IsPostBack                          'if not submit
      BindData()                                    'first procedure to carry out
    End If      
End Sub
  
Sub BindData()
    MySQL = "SELECT * FROM abctest"                 'query to connect for abctest table
    MyConn.Open() 'opens db connection
    Dim strCmd as New SQLCommand(MySQL, MyConn)     'use conn and query to open table
    
    Dim SQLDR as SQLDataReader                      ' declare a datareader
    SQLDR = strCmd.ExecuteReader()                  'translates info in open table
    MyDataGrid.DataSource = SQLDR                   'assign info to datagrid
    MyDataGrid.DataBind() ' loads data
    MyConn.Close() ' closes conn
End Sub  
 
Sub MyDataGrid_Edit(sender As Object, e As DataGridCommandEventArgs) ' highlights the line you want to edit
    MyDataGrid.ShowFooter = False 'hides footer
    MyDataGrid.EditItemIndex = e.Item.ItemIndex     ' display edititemtemplate
    BindData() 'rebind data
End Sub
 
Sub MyDataGrid_Cancel(sender As Object, e As DataGridCommandEventArgs) ' highlights in reverse
    MyDataGrid.ShowFooter = True                    'shows footer
    MyDataGrid.EditItemIndex = -1                   'hides the edititemtemplate
    BindData()                                      'rebinds
End Sub
 
Sub MyDataGrid_ItemDataBound(sender as Object, e as DataGridItemEventArgs) '
If e.Item.ItemType <> ListItemType.Header AND e.Item.ItemType <> ListItemType.Footer then ' if not header or footer, then its delete
    Dim deleteButton as Button = e.Item.FindControl("Delete")   'button variable which is assigned values of delete button
    deleteButton.Attributes.Add("onclick","return confirm('Are you sure you want to delete everything linked to this course?');")    
End If
End Sub
 
Sub MyDataGrid_Delete(sender As Object, e As DataGridCommandEventArgs) '
    MySQL = "DELETE from abctest where id = @Id"    'delete string
    Dim DelCmd as New SQLCommand(MySQL, MyConn)     'executes connection string and delete query
    
    DelCmd.Parameters.Add(New SqlParameter("@Id", MyDataGrid.DataKeys(e.Item.ItemIndex))) '
 
    MyConn.Open()
    DelCmd.ExecuteNonQuery()
    MyConn.Close()
    BindData()
End Sub
 
Sub MyDataGrid_Update(sender As Object, e As DataGridCommandEventArgs)
    MySQL = "UPDATE [abctest] SET [name] = @name, [phone] = @phone WHERE [id] = @Id"
    Dim UpCmd as New SQLCommand(MySQL, MyConn)
 
    Dim Up_name As TextBox
    Dim Up_phone As TextBox
    
    Up_name = e.Item.FindControl("name")
    Up_phone = e.Item.FindControl("phone")
        
    UpCmd.Parameters.Add(New SqlParameter("@Id", MyDataGrid.DataKeys(e.Item.ItemIndex)))
    UpCmd.Parameters.Add(New SQLParameter("@name", Up_name.Text ))
    UpCmd.Parameters.Add(New SQLParameter("@phone", Up_phone.Text))
    
    MyConn.Open()
    UpCmd.ExecuteNonQuery()
    MyDataGrid.ShowFooter = True
    MyDataGrid.EditItemIndex = -1
    MyConn.close()
    BindData()
End Sub
 
Sub MyDataGrid_Add(sender as Object, e as DataGridCommandEventArgs)
If e.CommandName = "Insert" Then
    MySQL = "INSERT INTO abctest (name,phone) VALUES (@name,@phone)"
    Dim InCmd as New SQLCommand(MySQL, MyConn)
    
    Dim In_name As TextBox
    Dim In_phone As TextBox
    
    In_name = e.Item.FindControl("add_name")
    In_phone = e.Item.FindControl("add_phone")
 
    InCmd.Parameters.Add(New SQLParameter("@name", In_name.text))
    InCmd.Parameters.Add(New SQLParameter("@phone", In_phone.text))
 
    MyConn.Open()
    InCmd.ExecuteNonQuery()
    MyConn.Close()
    BindData()
End if
End Sub
 
Sub post_form(sender As Object, e As EventArgs)
page_title.text = ""
end sub
</script>
<form runat="server">
 
<asp:Label id="page_title" runat="server"
Font-Size="11"
Font-Name="Arial" 
Font-Bold="true"
ForeColor="#00436c"
/>
<br />
<asp:DataGrid id="MyDataGrid" runat="server"
    width="650"
    GridLines="Horizontal"
    BorderColor="#CCCCCC" 
    CellPadding="4"
    CellSpacing="0"
    BorderWidth="1"
    Font-Name="Arial" 
    Font-Size="8"    
    AutoGenerateColumns="False" 
    OnEditCommand="MyDataGrid_Edit"
    OnUpdateCommand="MyDataGrid_Update"
    OnCancelCommand="MyDataGrid_Cancel"
    OnDeleteCommand="MyDataGrid_Delete"
    OnItemDataBound="MyDataGrid_ItemDataBound"
    DataKeyField="id" 
    EditItemStyle-BackColor="#eeeeee"
    OnItemCommand="MyDataGrid_Add"
    ShowFooter="True"    
>
<headerstyle Font-Size="8" 
    Font-Name="Arial" 
    Font-Bold="true" 
    BackColor="#CCCCCC" 
    VerticalAlign="top" />
 
<footerstyle Font-Size="8" 
    Font-Name="Arial" 
    BackColor="#FFFFFF" 
    VerticalAlign="top" />
 
<itemstyle Font-Size="8" 
    Font-Name="Arial" 
    VerticalAlign="top" 
    BackColor="#FFFFFF" />
 
<EditItemStyle Font-Size="8" 
    Font-Name="Arial" 
    VerticalAlign="top" 
    BackColor="#FFFFFF" />  
    <Columns>
        <asp:TemplateColumn visible="false">
            <FooterTemplate>
            </FooterTemplate>
            <ItemTemplate>
                <%# Container.DataItem("id") %>
            </ItemTemplate>
            <EditItemTemplate>
                <%# Container.DataItem("id") %>
            </EditItemTemplate>        
        </asp:TemplateColumn>
      
        <asp:TemplateColumn HeaderText="Name">
            <FooterTemplate>
                <asp:TextBox ID="add_name"
                    Font-Size="8" 
                    Font-Name="Arial"                
                    Columns="40" 
                    Runat="Server" />
            </FooterTemplate>
            <ItemTemplate>
                <%# Container.DataItem("name") %>
            </ItemTemplate>
            <EditItemTemplate>
                <asp:TextBox id="name" Text='<%# Container.DataItem("name") %>'
                    Font-Size="8" 
                    Font-Name="Arial"               
                    Columns="40" 
                    Runat="Server" />
            </EditItemTemplate>
        </asp:TemplateColumn>
 
        <asp:TemplateColumn HeaderText="Phone">
            <FooterTemplate>
                <asp:TextBox ID="add_phone"
                    Font-Size="8" 
                    Font-Name="Arial"                
                    Columns="40" 
                    Runat="Server" />
            </FooterTemplate>
            <ItemTemplate>
                <%# Container.DataItem("phone") %>
            </ItemTemplate>
            <EditItemTemplate>
                <asp:TextBox id="phone" Text='<%# Container.DataItem("phone") %>'
                    Font-Size="8" 
                    Font-Name="Arial"               
                    Columns="40" 
                    Runat="Server" />
            </EditItemTemplate>
        </asp:TemplateColumn>
 
        <asp:EditCommandColumn EditText="Edit Info" ButtonType="PushButton" UpdateText="Update" CancelText="Cancel" />    
 
        <asp:TemplateColumn HeaderText="Delete">
            <FooterTemplate>
                <asp:Button CommandName="Insert" Text="Add" Runat="server" width="55"/>
            </FooterTemplate>
            <ItemTemplate>
                <asp:Button CommandName="Delete" id="Delete" Text="Delete" Runat="server" />
            </ItemTemplate>
        </asp:TemplateColumn>          
        
        <asp:TemplateColumn HeaderText="Delete All">
            <FooterTemplate>
            </FooterTemplate>
            <ItemTemplate>
                <asp:CheckBox id="CheckBox1" value='<%# Container.DataItem("id") %>' runat="server" />
            </ItemTemplate>
            <EditItemTemplate>
            </EditItemTemplate>
        </asp:TemplateColumn>
                     
    </Columns>        
</asp:DataGrid>
<table Border="0" cellspacing="0" cellpadding="5" width="600">  
    <tr>
        <td align="center">
            <asp:Button ID="Submit" text="Delete Checked" 
                OnClick="post_form"
                Runat="server"              
                Font-Size="8" 
                Font-Name="Arial"
                width="100"
                />
        </td>
    </tr>                           
</table>
</form>

[Lethal]

What you can do is create a templated columns with a checkbox and when the user clicks the 'delete' button, simply iterate through the grid's rows collection. As you iterate through, do a FindControl on the checkbox and check if it is checked. Hope this makes sense. I do this on one of my pages if you need an example

[narmi2]

Thanks

If you do have sample code for this, can you please tell me where to find it.