Tools for my website:
I am using GridView. Because of my search code I am not able to use the automatic delete function that GridView has to offer.
I clicked on GridView, said "Add New Column" and added a checkbox column.
I want to be able to check one or more boxes and select a button that will delete those rows from the database using a stored procedure.
Below is the ASP.net part of my code
Default.aspx
<%@ Page Title="Home Page" Language="vb" MasterPageFile="~/Site.Master" AutoEventWireup="false" CodeBehind="Default.aspx.vb" Inherits="ContactList._Default" %>
<%@ Register assembly="AjaxControlToolkit" namespace="AjaxControlToolkit" tagprefix="asp" %>
<%-- CONNECTION STRING--%>
<%--string Connection = "server=Local; uid=sa; pwd=; database=TGMInfo; Connect Timeout=10000";--%>
<asp:Content ID="HeaderContent" runat="server" ContentPlaceHolderID="HeadContent">
<link href="Styles/Site.css" rel="stylesheet" type="text/css" />
</asp:Content>
<asp:Content ID="BodyContent" runat="server" ContentPlaceHolderID="MainContent">
<h2>
Welcome to THE TGM CONTACTS SITE</h2>
<p>
</p>
<p></p>
<center>
<asp:ToolkitScriptManager ID="ToolkitScriptManager1" runat="server">
</asp:ToolkitScriptManager>
Add a new record to the database<br />
<br />
<%--Text boxes for First Name, Last Name and Main Phone Number--%>
<asp:Label ID="lblFirstName" runat="server" Text="First Name:"></asp:Label>
<asp:TextBox ID="txtFirstName" runat="server" Width="160px"></asp:TextBox>
<asp:Label ID="LblLastName" runat="server" Text="Last Name:"></asp:Label>
<asp:TextBox ID="txtLastName" runat="server" Width="160px"></asp:TextBox>
<asp:Label ID="lblMainPhone" runat="server" Text="Main Phone #:"></asp:Label>
<asp:TextBox ID="txtMainPhone" runat="server" Width="160px"></asp:TextBox>
<asp:MaskedEditExtender ID="txtMainPhone_MaskedEditExtender" runat="server"
ErrorTooltipEnabled="True" Mask="(999) 999-9999" MaskType="Number"
TargetControlID="txtMainPhone">
</asp:MaskedEditExtender>
<br />
<br />
<%--Button adds information from the text fields to the SQL Database--%>
<asp:Button ID="btnAdd" runat="server" Text="Add Record" />
<br />
<br />
<br />
Search by Last Name<br />
<asp:Label ID="lblQuery" runat="server" Text="Last Name:"></asp:Label>
<asp:TextBox ID="txtQuery" runat="server" ToolTip="Search by last name"></asp:TextBox>
<br />
<br />
<asp:Button ID="BtnQuery" runat="server" Text="Search" />
<br />
</center>
<p>
</p>
<p>
</p>
<center>
<asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False"
DataKeyNames="EmpId"
EmptyDataText="There are no data records to display." CellPadding="4"
ForeColor="#333333" GridLines="None" Height="136px" Width="299px">
<AlternatingRowStyle BackColor="White" />
<Columns>
<%-- <asp:BoundField DataField="MainPhoneNumber" HeaderText="Main Phone Number"
SortExpression="MainPhoneNumber" />--%>
<asp:BoundField DataField="EmpId" HeaderText="EmpId" ReadOnly="True"
SortExpression="EmpId" Visible="False" />
<asp:BoundField DataField="FirstName" HeaderText="First Name"
SortExpression="FirstName" />
<asp:BoundField DataField="LastName" HeaderText="Last Name"
SortExpression="LastName" />
<asp:TemplateField HeaderText="Main Phone Number">
<ItemTemplate>
<asp:Literal ID="litPhone" runat="server" Text='<%# string.Format("{0:(###) ###-####}", Int64.Parse(Eval("MainPhoneNumber").ToString())) %>' />
</ItemTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="Check For Deletion">
<ItemTemplate>
<asp:CheckBox ID="CheckBox1" runat="server" />
</ItemTemplate>
<EditItemTemplate>
<asp:CheckBox ID="CheckBox1" runat="server" />
</EditItemTemplate>
</asp:TemplateField>
</Columns>
<FooterStyle BackColor="#990000" Font-Bold="True" ForeColor="White" />
<HeaderStyle BackColor="#990000" Font-Bold="True" ForeColor="White" />
<PagerStyle BackColor="#FFCC66" ForeColor="#333333" HorizontalAlign="Center" />
<RowStyle BackColor="#FFFBD6" ForeColor="#333333" />
<SelectedRowStyle BackColor="#FFCC66" Font-Bold="True" ForeColor="Navy" />
<SortedAscendingCellStyle BackColor="#FDF5AC" />
<SortedAscendingHeaderStyle BackColor="#4D0000" />
<SortedDescendingCellStyle BackColor="#FCF6C0" />
<SortedDescendingHeaderStyle BackColor="#820000" />
</asp:GridView>
<p>
<center>
<asp:Button ID="Button1" runat="server" Text="Delete Selected Rows"
ToolTip="Check desired rows and click this button for deletion" />
</p>
<p>
</p>
</center>
</asp:Content>
I think I posted everything that I need to, if not I can post more. I did not post the code-behind (visual basic.net) or the 2 stored procedures I have so far. I am very new to vb.net/asp.net/stored procedures.
Here is my information for finding the checked checkboxes.
Protected Sub Button1_Click1(ByVal sender As Object, ByVal e As EventArgs) Handles btnDelete.Click
'Create String Collection to store
'IDs of records to be deleted
Dim idCollection As New StringCollection()
Dim strID As String = String.Empty
'Loop through GridView rows to find checked rows
For d = 0 To GridView1.Rows.Count - 1
If GridView1.Rows(d).Cells.FromKey("DeleteRecord").Value = True Then
strID = GridView1.Rows(d).Cells.FromKey("EmpID").Value
idCollection.Add(strID)
End If
Next
'Call the method to Delete records
DeleteMultipleRecords(idCollection)
QueryEmployees("")
End Sub
Here is the code for the sub that calls my stored procedure.
Private Sub DeleteMultipleRecords(ByVal idCollection As StringCollection)
Dim SqlConn4 As SqlConnection = New SqlConnection("Password=;Persist Security Info=True;User ID=;Initial Catalog=;Data Source=.\SQLEXPRESS")
Dim cmd4 As New SqlCommand("Password=;Persist Security Info=True;User ID=;Initial Catalog=;Data Source=.\SQLEXPRESS")
Dim IDs As String = ""
For Each id As String In idCollection
IDs += id.ToString() & ","
Next
Try
'Dim strIDs As String = IDs.Substring(0, IDs.LastIndexOf(","))
'Dim strSql As String = "Delete from Details WHERE ID in (" & strIDs & ")"
For i = 0 To idCollection.Count - 1
cmd4.CommandType = CommandType.StoredProcedure
cmd4.Parameters.AddWithValue("@IDs", CInt(idCollection(i)))
cmd4.CommandText = "dbo.SPDeleteEmp"
cmd4.Connection = SqlConn4
SqlConn4.Open()
cmd4.ExecuteNonQuery()
Next
Catch ex As SqlException
Dim errorMsg As String = "Error in Deletion"
errorMsg += ex.Message
Throw New Exception(errorMsg)
Finally
SqlConn4.Close()
End Try
End Sub
While this works, at the moment this only deletes one checked box at a time, but does the bulk of what I needed to know. Also, thank you Carter for helping answer this question.