Post
Ask Question
C# Corner

C# Corner

  • Tech Writer
  • 40
  • 6.4k

Prevent duplicate data in GRIDVIEW update

Mar 3 2016 2:45 AM
Hi Guys,
We currently developing a scheduling system for our thesis. In summary, my problem is when i update the data in gridview, it update, it works fine but i want to formulate a condition which prevents the user to update the data if the data he/she enter is already exist.
For example, we have a GridViewRoom (name of the Gridview) that have columns RoomID, RoomName, RoomDescription, and RoomCapacity. The Room ID is my primary key and it is auto increment and works fine. Only the Room Name is not allowed to have duplicate even though it have different RoomID.
Im using ASP.NET C# and works with Microsoft Visual Studio 2010.
Here's our aspx
  1. <%@ Page Title="Room" Language="C#" MasterPageFile="~/VPAA.master" AutoEventWireup="true" CodeFile="VPAA_Room.aspx.cs" Inherits="Pages_VPAA_Room" %>  
  2.   
  3. <asp:Content ID="Content1" ContentPlaceHolderID="head" Runat="Server">  
  4. </asp:Content>  
  5. <asp:Content ID="Content2" ContentPlaceHolderID="ContentPlaceHolder1" Runat="Server">  
  6. <script src="../JScripts/VPAA_JScripts.js" type="text/javascript"></script>  
  7. <table class="tableroom1">  
  8.     <tr>  
  9.         <td class="room1" colspan="3">  
  10.             ADD NEW ROOM  
  11.         </td>  
  12.         <td rowspan="6" class="room15">  
  13.         </td>  
  14.         <td rowspan="6" valign="top" class="room16">  
  15.             <asp:ValidationSummary ID="ValidationSummary" runat="server" ForeColor="Red"   
  16.                 HeaderText="Please check the following error(s):" />  
  17.         </td>  
  18.     </tr>  
  19.     <tr>  
  20.         <td class="room2">  
  21.             Room:  
  22.         </td>  
  23.         <td class="room3">  
  24.             <asp:TextBox ID="TextBoxroom" runat="server" Width="250px"></asp:TextBox>  
  25.         </td>  
  26.         <td class="room4">  
  27.             <asp:RequiredFieldValidator ID="RequiredFieldValidatorRoom" runat="server" ErrorMessage="Room name is required."  
  28.              ForeColor="Red" ControlToValidate="TextBoxroom" Display="Dynamic" SetFocusOnError="true">*</asp:RequiredFieldValidator>  
  29.         </td>  
  30.     </tr>  
  31.     <tr>  
  32.         <td class="room5">  
  33.             Description:  
  34.         </td>  
  35.         <td class="room6">  
  36.             <asp:DropDownList ID="DropDownListdescription" runat="server" Width="255px" Height="23px" >  
  37.             </asp:DropDownList>  
  38.         </td>  
  39.         <td class="room7">  
  40.             <asp:Button ID="ButtonAdd" runat="server" Text="ADD" ToolTip="Add Description" CausesValidation="False"  
  41.                 Height="24px" Width="60px" CssClass="Add" PostBackUrl="~/Pages/VPAA_Room_Description.aspx" />  
  42.             <asp:RequiredFieldValidator ID="RequiredFieldValidator1" runat="server"   
  43.                 ControlToValidate="DropDownListdescription" Display="Dynamic"   
  44.                 ErrorMessage="Room description is required." ForeColor="Red" InitialValue="0">*</asp:RequiredFieldValidator>  
  45.         </td>  
  46.     </tr>  
  47.     <tr>  
  48.         <td class="room8">  
  49.             Capacity:  
  50.         </td>  
  51.         <td class="room9">  
  52.             <asp:TextBox ID="TextBoxcapacity" runat="server" Width="250px" OnKeyPress="return NumberOnly(event)"  
  53.                 Placeholder="Enter number only"></asp:TextBox>  
  54.         </td>  
  55.         <td class="room10">  
  56.             <asp:RequiredFieldValidator ID="RequiredFieldValidatorCapacity" runat="server" ErrorMessage="Room capacity is required."  
  57.              ForeColor="Red" ControlToValidate="TextBoxcapacity" Display="Dynamic" SetFocusOnError="true">*</asp:RequiredFieldValidator>  
  58.             <asp:RangeValidator ID="Capacity" runat="server" ControlToValidate="TextBoxcapacity"  
  59.                 ErrorMessage="Room capacity must be atleast 35 and  not greater than to 40." ForeColor="Red" Display="Dynamic"   
  60.                 MaximumValue="40" MinimumValue="35" Type="Integer" SetFocusOnError="true">*</asp:RangeValidator>  
  61.         </td>  
  62.     </tr>  
  63.     <tr>  
  64.         <td colspan="3">  
  65.         </td>  
  66.     </tr>  
  67.     <tr>  
  68.         <td>  
  69.         </td>  
  70.         <td colspan="2">  
  71.            <asp:Button ID="ButtonSave" runat="server" Text="SAVE" Height="30px" CssClass="Save"  
  72.                 Width="75px" Onclick="ButtonSave_Click" ToolTip="Save Data" />  
  73.                
  74.            <asp:Button ID="ButtonCancel" runat="server" Text="CANCEL" Height="30px" Width="95px" CausesValidation="false"  
  75.                 Onclick="ButtonCancel_Click" ToolTip="Clear" CssClass="Cancel" />  
  76.         </td>  
  77.     </tr>  
  78. </table>  
  79. <table class="tableroom2">  
  80.         <tr>  
  81.             <td>  
  82.                 <asp:Label ID="LabelMsg" runat="server" ForeColor="Red"></asp:Label>  
  83.             </td>  
  84.         </tr>  
  85. </table>  
  86. <table class="tableroom3">  
  87.     <tr>  
  88.         <td class="room11">  
  89.             <asp:Button ID="ButtonArchive" runat="server" Text="Archive" ToolTip="Bulk Archive"  
  90.                 CausesValidation="False" Height="28px" Width="87px" CssClass="Archive"  
  91.                 OnClientClick="return confirm('The selected item(s) will be deleted.')"   
  92.                 OnClick="ButtonArchive_Click" />  
  93.                    
  94.             <asp:Button ID="ButtonClearFilter" runat="server" Text="Clear Filter" ToolTip="Clear Filter"  
  95.                 CausesValidation="False" Height="28px" Width="107px" CssClass="ClearFilter"  
  96.                 Onclick="ButtonClearFilter_Click" />  
  97.         </td>  
  98.         <td class="room12">  
  99.             Room Description:  
  100.             <asp:DropDownList ID="DropDownListdescription2" runat="server" Width="200px"   
  101.                 Height="23px" AutoPostBack="True" OnSelectedIndexchanged="DropDownListdescription2_SelectedIndexChanged" >  
  102.             </asp:DropDownList>  
  103.         </td>  
  104.         <td class="room13">  
  105.             <asp:Button ID="ButtonSearch" runat="server" Text="Search" ToolTip="Search Room"  
  106.                 CausesValidation="False" Height="25px" OnClick="ButtonSearch_Click" />  
  107.             <asp:TextBox ID="TextBoxSearch" runat="server" Placeholder=" Enter room" Height="20px" Width="170px"  
  108.                 CssClass="Search"></asp:TextBox>  
  109.         </td>  
  110.     </tr>  
  111. </table>  
  112. <table class="tableroom4">  
  113.     <tr>  
  114.         <td>  
  115.         </td>  
  116.         <td class="room14" align="center">  
  117.             <asp:GridView ID="GridViewRoom" runat="server" AutoGenerateColumns="False" AllowPaging="True"  
  118.                 DataKeyNames="RoomID" CellPadding="4" GridLines="Vertical" BorderColor="#CCCCCC"   
  119.                 BorderStyle="Solid" ForeColor="#333333" BorderWidth="1px" EmptyDataText="No records found."  
  120.                 OnRowDeleting="GVRoom_RowDeleting" OnRowDataBound="RowDataBound" OnRowCancelingEdit="GVRoom_RowCancelingEdit"  
  121.                 OnPageIndexChanging="GVRoom_PageIndexChanging" OnRowEditing="GVRoom_RowEditing" OnRowUpdating="GVRoom_RowUpdating" >  
  122.                 <AlternatingRowStyle BackColor="White" />  
  123.                 <Columns>  
  124.                     <asp:TemplateField>  
  125.                         <HeaderTemplate>  
  126.                             <asp:CheckBox ID="CheckBoxSelectAll" runat="server" onclick="checkAll(this);" />  
  127.                         </HeaderTemplate>  
  128.                         <ItemTemplate>  
  129.                             <asp:CheckBox ID="CheckBoxSelect" runat="server" onclick="Check_Click(this)" />  
  130.                         </ItemTemplate>  
  131.                     </asp:TemplateField>  
  132.                     <asp:TemplateField HeaderText="Action">  
  133.                         <EditItemTemplate>  
  134.                             <asp:ImageButton ID="ButtonUpdate" runat="server" CausesValidation="False" ToolTip="Update" CssClass="Update"  
  135.                                 CommandName="Update" Height="27px" Width="28px" ImageAlign="Middle" ImageUrl="~/Images/Update.png" />  
  136.                                
  137.                             <asp:ImageButton ID="ButtonCancel" runat="server" CausesValidation="False" ToolTip="Cancel Edit" CssClass="CancelEdit"  
  138.                                 CommandName="Cancel" Height="27px" Width="28px" ImageAlign="Middle" ImageUrl="~/Images/Cancel Edit.png" />  
  139.                         </EditItemTemplate>  
  140.                         <ItemTemplate>  
  141.                             <asp:ImageButton ID="ButtonEdit" runat="server" CausesValidation="False" ToolTip="Edit" CssClass="Edit"  
  142.                                 CommandName="Edit" Height="28px" Width="30px" ImageAlign="Middle" ImageUrl="~/Images/Edit.png" />  
  143.                             <asp:ImageButton ID="ButtonArchive" runat="server" CausesValidation="False" OnClientClick="return confirm('Delete this item?')"   
  144.                                 ToolTip="Archive Item" CssClass="ArchiveRow" CommandName="Delete" Height="28px" Width="30px" ImageAlign="Middle" ImageUrl="~/Images/Archive Item.png" />  
  145.                         </ItemTemplate>  
  146.                         <HeaderStyle Width="70" />  
  147.                         <ItemStyle HorizontalAlign="Center" />  
  148.                     </asp:TemplateField>  
  149.                     <asp:TemplateField HeaderText="No." ItemStyle-HorizontalAlign="Center">  
  150.                         <ItemTemplate>  
  151.                             <%# Container.DataItemIndex + 1 %>  
  152.                         </ItemTemplate>  
  153.                     </asp:TemplateField>  
  154.                     <asp:TemplateField HeaderText="Room ID" InsertVisible="False" SortExpression="RoomID">  
  155.                         <EditItemTemplate>  
  156.                             <asp:Label ID="LabelRoomID" runat="server" Text='<%# Eval("RoomID") %>'></asp:Label>  
  157.                         </EditItemTemplate>  
  158.                         <ItemTemplate>  
  159.                             <asp:Label ID="LabelRoomID" runat="server" Text='<%# Bind("RoomID") %>'></asp:Label>  
  160.                         </ItemTemplate>  
  161.                     <HeaderStyle Width="120px" />  
  162.                     <ItemStyle HorizontalAlign="Center" />  
  163.                     </asp:TemplateField>  
  164.                     <asp:TemplateField HeaderText="Room" SortExpression="Room">  
  165.                         <EditItemTemplate>  
  166.                             <asp:TextBox ID="TextBoxRoom" runat="server" Text='<%# Bind("Room") %>'></asp:TextBox>  
  167.                         </EditItemTemplate>  
  168.                         <ItemTemplate>  
  169.                             <asp:Label ID="LabelRoom" runat="server" Text='<%# Bind("Room") %>'></asp:Label>  
  170.                         </ItemTemplate>  
  171.                     <ControlStyle Width="140px" />  
  172.                     <HeaderStyle Width="200px" />  
  173.                     <ItemStyle HorizontalAlign="Center" />  
  174.                     </asp:TemplateField>  
  175.                     <asp:TemplateField HeaderText="Description" SortExpression="Description">  
  176.                         <EditItemTemplate>  
  177.                             <asp:DropDownList ID="DropDownListdescription3" runat="server" Height="23px"   
  178.                                 Width="100px" DataSourceID="SqlDataSourceRoom_Description2"   
  179.                                 DataTextField="Description" DataValueField="Description"></asp:DropDownList>  
  180.                             <asp:SqlDataSource ID="SqlDataSourceRoom_Description2" runat="server"   
  181.                                 ConnectionString="<%$ ConnectionStrings:ScheduleConnectionString %>"   
  182.                                 SelectCommand="SELECT [Description] FROM [Room_Description]">  
  183.                             </asp:SqlDataSource>  
  184.                         </EditItemTemplate>  
  185.                         <ItemTemplate>  
  186.                             <asp:Label ID="LabelDescription" runat="server" Text='<%# Bind("Description") %>'></asp:Label>  
  187.                         </ItemTemplate>  
  188.                     <ControlStyle Width="140px" />  
  189.                     <HeaderStyle Width="200px" />  
  190.                     <ItemStyle HorizontalAlign="Center" />  
  191.                     </asp:TemplateField>  
  192.                     <asp:TemplateField HeaderText="Capacity" SortExpression="Capacity">  
  193.                         <EditItemTemplate>  
  194.                             <asp:TextBox ID="TextBoxCapacity" runat="server" Text='<%# Bind("Capacity") %>' OnKeyPress="return NumberOnly(event)"></asp:TextBox>  
  195.                         </EditItemTemplate>  
  196.                         <ItemTemplate>  
  197.                             <asp:Label ID="LabelCapacity" runat="server" Text='<%# Bind("Capacity") %>'></asp:Label>  
  198.                         </ItemTemplate>  
  199.                     <ControlStyle Width="120px" />  
  200.                     <HeaderStyle Width="140px" />  
  201.                     <ItemStyle HorizontalAlign="Center" />  
  202.                     </asp:TemplateField>  
  203.                     <asp:TemplateField HeaderText="Date Created" SortExpression="DateCreated">  
  204.                         <EditItemTemplate>  
  205.                                 <asp:Label ID="LabelDateCreated" runat="server" Text='<%# Bind("DateCreated") %>'></asp:Label>  
  206.                             </EditItemTemplate>  
  207.                         <ItemTemplate>  
  208.                             <asp:Label ID="LabelDateCreated" runat="server" Text='<%# Bind("DateCreated") %>'></asp:Label>  
  209.                         </ItemTemplate>  
  210.                     <HeaderStyle Width="150px" />  
  211.                     <ItemStyle HorizontalAlign="Center" />  
  212.                     </asp:TemplateField>  
  213.                 </Columns>  
  214.                 <FooterStyle BackColor="#507CD1" Font-Bold="True" ForeColor="White" />  
  215.                 <HeaderStyle BackColor="#507CD1" Font-Bold="True" ForeColor="White" />  
  216.                 <PagerStyle BackColor="#507CD1" ForeColor="White" HorizontalAlign="Center" />  
  217.                 <RowStyle BackColor="#EFF3FB" />  
  218.                 <SelectedRowStyle BackColor="#D1DDF1" Font-Bold="True" ForeColor="#333333" />  
  219.                 <SortedAscendingCellStyle BackColor="#F5F7FB" />  
  220.                 <SortedAscendingHeaderStyle BackColor="#6D95E1" />  
  221.                 <SortedDescendingCellStyle BackColor="#E9EBEF" />  
  222.                 <SortedDescendingHeaderStyle BackColor="#4870BE" />  
  223.             </asp:GridView>  
  224.         </td>  
  225.         <td>  
  226.         </td>  
  227.     </tr>  
  228. </table>  
  229. </asp:Content>  
Here's our .cs
 
  1. using System;  
  2. using System.Collections.Generic;  
  3. using System.Linq;  
  4. using System.Web;  
  5. using System.Web.UI;  
  6. using System.Web.UI.HtmlControls;  
  7. using System.Web.UI.WebControls;  
  8. using System.Web.UI.WebControls.WebParts;  
  9. using System.Configuration;  
  10. using System.Data.SqlClient;  
  11. using System.Data;  
  12. using System.Collections.Specialized;  
  13.   
  14. public partial class Pages_VPAA_Room : System.Web.UI.Page  
  15. {  
  16.     // Declaring variable for SQL functions and configuring the ConnectionString needed  
  17.     SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["ScheduleConnectionString"].ConnectionString);  
  18.     SqlCommand cmd;  
  19.     SqlDataAdapter adp;  
  20.     string strCon = ConfigurationManager.ConnectionStrings["ScheduleConnectionString"].ConnectionString;  
  21.   
  22.     protected void Page_Load(object sender, EventArgs e)  
  23.     {  
  24.         if (!Page.IsPostBack)  
  25.         {  
  26.             RoomData(this.TextBoxSearch.Text);  
  27.             // DataSource of the first DropDownList  
  28.             using (con = new SqlConnection(strCon))  
  29.             {  
  30.                 using (cmd = new SqlCommand("SELECT * from Room_Description"))  
  31.                 {  
  32.                     cmd.CommandType = CommandType.Text;  
  33.                     cmd.Connection = con;  
  34.                     con.Open();  
  35.                     DropDownListdescription.DataSource = cmd.ExecuteReader();  
  36.                     DropDownListdescription.DataTextField = "Description";  
  37.                     DropDownListdescription.DataValueField = "Description";  
  38.                     DropDownListdescription.DataBind();  
  39.                     con.Close();  
  40.                 }  
  41.             }  
  42.             // Setting the Initial Value of the first DropDownList  
  43.             DropDownListdescription.Items.Insert(0, new ListItem("-- Select Description --", "0"));  
  44.   
  45.             // DataSource of the second DropDownList  
  46.             using (con = new SqlConnection(strCon))  
  47.             {  
  48.                 using (cmd = new SqlCommand("SELECT DISTINCT Description from Room"))  
  49.                 {  
  50.                     cmd.CommandType = CommandType.Text;  
  51.                     cmd.Connection = con;  
  52.                     con.Open();  
  53.                     DropDownListdescription2.DataSource = cmd.ExecuteReader();  
  54.                     DropDownListdescription2.DataTextField = "Description";  
  55.                     DropDownListdescription2.DataValueField = "Description";  
  56.                     DropDownListdescription2.DataBind();  
  57.                     con.Close();  
  58.                 }  
  59.             }  
  60.             // Setting the Initial Value of the second DropDownList  
  61.             DropDownListdescription2.Items.Insert(0, new ListItem("-- Select Description --", "0"));  
  62.         }  
  63.         LabelMsg.Visible = false;  
  64.     }  
  65.     // Binding data to GridView using SELECT parameters and setting the TextBoxSearch for room searching  
  66.     protected void RoomData(string room)  
  67.     {  
  68.         string select = "SELECT * from Room where (Room LIKE '%" + TextBoxSearch.Text + "%')";  
  69.         cmd = new SqlCommand(select);  
  70.         GridViewRoom.DataSource = GetData(cmd);  
  71.         GridViewRoom.DataBind();  
  72.     }  
  73.     private DataTable GetData(SqlCommand cmd)  
  74.     {  
  75.         using (con = new SqlConnection(strCon))  
  76.         {  
  77.             using (adp = new SqlDataAdapter())  
  78.             {  
  79.                 cmd.Connection = con;  
  80.                 adp.SelectCommand = cmd;  
  81.                 using (DataTable dt = new DataTable())  
  82.                 {  
  83.                     adp.Fill(dt);  
  84.                     return dt;  
  85.                 }  
  86.             }  
  87.         }  
  88.     }  
  89.     protected void ButtonSave_Click(object sender, EventArgs e)  
  90.     {  
  91.         try  
  92.         {  
  93.             con.Open();  
  94.             // Checking if the data inserting is already exist in the current records to prevent data redundancy  
  95.             string CheckRoom = "SELECT count(*) from Room where Room='" + TextBoxroom.Text + "'";  
  96.             cmd = new SqlCommand(CheckRoom, con);  
  97.             int temp = Convert.ToInt32(cmd.ExecuteScalar().ToString());  
  98.             con.Close();  
  99.             // If the data already exist, it displays an error message  
  100.             if (temp == 1)  
  101.             {  
  102.                 LabelMsg.Visible = true;  
  103.                 LabelMsg.Text = "Room name already exist.";  
  104.             }  
  105.             // If not, then it proceed to data insertion  
  106.             // Trim() is used for removing blank spaces in the TextBox during insertion  
  107.             else  
  108.             {  
  109.                 string strDate = DateTime.Now.ToShortDateString();  
  110.                 con.Open();  
  111.                 string insert = "INSERT into Room (Room, Description, Capacity, DateCreated) values (@Room, @Description, @Capacity, @DateCreated)";  
  112.                 cmd = new SqlCommand(insert, con);  
  113.                 cmd.Parameters.AddWithValue("@Room", TextBoxroom.Text.Trim());  
  114.                 cmd.Parameters.AddWithValue("@Description", DropDownListdescription.SelectedValue);  
  115.                 cmd.Parameters.AddWithValue("@Capacity", TextBoxcapacity.Text.Trim());  
  116.                 cmd.Parameters.AddWithValue("@DateCreated", strDate);  
  117.                 cmd.ExecuteNonQuery();  
  118.                 Response.Redirect("VPAA_Room.aspx");  
  119.                 con.Close();  
  120.             }     
  121.         }  
  122.         catch (Exception ex)  
  123.         {  
  124.             LabelMsg.Text = ex.Message.ToString();  
  125.             LabelMsg.Visible = true;  
  126.         }  
  127.     }  
  128.     protected void ButtonCancel_Click(object sender, EventArgs e)  
  129.     {  
  130.         TextBoxroom.Text = "";  
  131.         TextBoxcapacity.Text = "";  
  132.         DropDownListdescription.ClearSelection();  
  133.     }  
  134.     // Preparing the function for deleting data in GridView  
  135.     protected void DeleteRecord(int roomID)  
  136.     {  
  137.         con = new SqlConnection(strCon);  
  138.         cmd = new SqlCommand("DELETE from Room where RoomID = @RoomID", con);  
  139.         cmd.Parameters.AddWithValue("@RoomID", roomID);  
  140.         con.Open();  
  141.         cmd.ExecuteNonQuery();  
  142.         con.Close();  
  143.     }  
  144.     protected void ButtonArchive_Click(object sender, EventArgs e)  
  145.     {  
  146.         int temp = 0;  
  147.         // Read each row of GridView  
  148.         foreach (GridViewRow row in GridViewRoom.Rows)  
  149.         {  
  150.             // Identify whether the CheckBox in each row is checked or not  
  151.             CheckBox check = (CheckBox)row.FindControl("CheckBoxSelect");  
  152.             Label ID = (Label)row.FindControl("LabelRoomID");  
  153.   
  154.             if (check.Checked)  
  155.             {  
  156.                 string strDate = DateTime.Now.ToShortDateString();  
  157.                 int roomID = Convert.ToInt32(ID.Text);  
  158.                 string room = (row.FindControl("LabelRoom") as Label).Text;  
  159.                 string des = (row.FindControl("LabelDescription") as Label).Text;  
  160.                 string cap = (row.FindControl("LabelCapacity") as Label).Text;  
  161.                 // Calling the prepared delete function before the inserting(transferring) of selected data to another table  
  162.                 DeleteRecord(roomID);  
  163.                 cmd = new SqlCommand("INSERT into Archive_Room (ARoom, ADescription, ACapacity, DateArchived) values ('" + room + "','" + des + "','" + cap + "', @DateArchived)", con);  
  164.                 cmd.Parameters.AddWithValue("@DateArchived", strDate);  
  165.                 con.Open();  
  166.                 cmd.ExecuteNonQuery();  
  167.                 con.Close();  
  168.                 LabelMsg.Visible = true;  
  169.                 LabelMsg.Text = "Data deleted.";  
  170.                 temp++;  
  171.             }  
  172.             if (temp == 0)  
  173.             {  
  174.                 LabelMsg.Visible = true;  
  175.                 LabelMsg.Text = "Please select data.";  
  176.             }  
  177.         }  
  178.         RoomData(this.TextBoxSearch.Text);  
  179.     }  
  180.     // Displays the GridView to its original state  
  181.     protected void ButtonClearFilter_Click(object sender, EventArgs e)  
  182.     {  
  183.         Response.Redirect("VPAA_Room.aspx");  
  184.     }  
  185.     // Used for filtering the GridView using the DropDownList or TextBox  
  186.     protected void DropDownListdescription2_SelectedIndexChanged(object sender, EventArgs e)  
  187.     {  
  188.         string search = "SELECT * from Room where Description LIKE '%' +  @Description + '%'";  
  189.         cmd = new SqlCommand(search, con);  
  190.         cmd.Parameters.AddWithValue("@Description", DropDownListdescription2.SelectedValue);  
  191.         int selected = Convert.ToInt32(DropDownListdescription2.SelectedIndex);  
  192.         if (DropDownListdescription2.SelectedIndex == 0)  
  193.         {  
  194.             RoomData(this.TextBoxSearch.Text);  
  195.         }  
  196.         else  
  197.         {  
  198.             DataTable dt = SearchData(cmd);  
  199.             RoomData(this.TextBoxSearch.Text);  
  200.             GridViewRoom.DataSource = dt;  
  201.             GridViewRoom.DataBind();  
  202.         }  
  203.     }  
  204.     protected void ButtonSearch_Click(object sender, EventArgs e)  
  205.     {  
  206.         string search = "SELECT * from Room where Room LIKE '%' +  @Room + '%' ";  
  207.         cmd = new SqlCommand(search, con);  
  208.         cmd.Parameters.AddWithValue("@Room", TextBoxSearch.Text.Trim());  
  209.         DataTable dt = SearchData(cmd);  
  210.         RoomData(this.TextBoxSearch.Text);  
  211.         GridViewRoom.DataSource = dt;  
  212.         GridViewRoom.DataBind();  
  213.     }  
  214.     private DataTable SearchData(SqlCommand cmd)  
  215.     {  
  216.         DataTable dt = new DataTable();  
  217.         String strCon = System.Configuration.ConfigurationManager.ConnectionStrings["ScheduleConnectionString"].ConnectionString;  
  218.         SqlConnection con = new SqlConnection(strCon);  
  219.         SqlDataAdapter sda = new SqlDataAdapter();  
  220.         cmd.CommandType = CommandType.Text;  
  221.         cmd.Connection = con;  
  222.         try  
  223.         {  
  224.             con.Open();  
  225.             sda.SelectCommand = cmd;  
  226.             sda.Fill(dt);  
  227.             return dt;  
  228.         }  
  229.         catch (Exception ex)  
  230.         {  
  231.             LabelMsg.Text = ex.Message.ToString();  
  232.             LabelMsg.Visible = true;  
  233.             return null;  
  234.         }  
  235.         finally  
  236.         {  
  237.             con.Close();  
  238.             sda.Dispose();  
  239.             con.Dispose();  
  240.         }  
  241.     }  
  242.     // The following codes is used to perform the GridView functions programatically such PAGING, EDITING, CANCEL EDITING and UPDATING  
  243.     protected void RowDataBound(object sender, GridViewRowEventArgs e)  
  244.     {  
  245.         if (e.Row.RowType == DataControlRowType.DataRow)  
  246.         {  
  247.             // Marks the selected rows with black bar  
  248.             e.Row.Attributes.Add("onmouseover", "MouseEvents(this, event)");  
  249.             e.Row.Attributes.Add("onmouseout", "MouseEvents(this, event)");  
  250.         }  
  251.     }  
  252.     protected void GVRoom_PageIndexChanging(object sender, GridViewPageEventArgs e)  
  253.     {  
  254.         GridViewRoom.PageIndex = e.NewPageIndex;  
  255.         RoomData(this.TextBoxSearch.Text);  
  256.     }  
  257.     protected void GVRoom_RowEditing(object sender, GridViewEditEventArgs e)  
  258.     {  
  259.         GridViewRoom.EditIndex = e.NewEditIndex;  
  260.         this.RoomData(this.TextBoxSearch.Text);  
  261.     }  
  262.     // Preparing the function for deleting data in GridView  
  263.     protected void Delete(string roomID)  
  264.     {  
  265.         cmd = new SqlCommand("DELETE from Room where RoomID=" + roomID, con);  
  266.         con.Open();  
  267.         cmd.ExecuteNonQuery();  
  268.         con.Close();  
  269.   
  270.         RoomData(this.TextBoxSearch.Text);  
  271.     }  
  272.     protected void GVRoom_RowDeleting(object sender, GridViewDeleteEventArgs e)  
  273.     {  
  274.         string strDate = DateTime.Now.ToShortDateString();  
  275.         string desID = GridViewRoom.DataKeys[e.RowIndex].Value.ToString();  
  276.         string room = (GridViewRoom.Rows[e.RowIndex].FindControl("LabelRoom") as Label).Text;  
  277.         string des = (GridViewRoom.Rows[e.RowIndex].FindControl("LabelDescription") as Label).Text;  
  278.         string cap = (GridViewRoom.Rows[e.RowIndex].FindControl("LabelCapacity") as Label).Text;  
  279.   
  280.         // Calling the prepared delete function before the inserting(transferring) of selected data to another table  
  281.         Delete(desID);  
  282.         using (SqlConnection con = new SqlConnection(strCon))  
  283.         {  
  284.             string insert = "INSERT into Archive_Room (ARoom, ADescription, ACapacity, DateArchived) values ('" + room + "','" + des + "','" + cap + "', @DateArchived)";  
  285.             using (SqlCommand cmd = new SqlCommand(insert))  
  286.             {  
  287.                 cmd.Connection = con;  
  288.                 cmd.Parameters.AddWithValue("@ARoom", room);  
  289.                 cmd.Parameters.AddWithValue("@ADescription", des);  
  290.                 cmd.Parameters.AddWithValue("@DateArchived", strDate);  
  291.                 con.Open();  
  292.                 cmd.ExecuteNonQuery();  
  293.                 con.Close();  
  294.   
  295.                 RoomData(this.TextBoxSearch.Text);  
  296.             }  
  297.         }  
  298.     }  
  299.     protected void GVRoom_RowUpdating(object sender, GridViewUpdateEventArgs e)  
  300.     {  
  301.         string roomID = GridViewRoom.DataKeys[e.RowIndex].Value.ToString();  
  302.         string room = (GridViewRoom.Rows[e.RowIndex].FindControl("TextBoxRoom") as TextBox).Text;  
  303.         string cap = (GridViewRoom.Rows[e.RowIndex].FindControl("TextBoxCapacity") as TextBox).Text;  
  304.         string des = (GridViewRoom.Rows[e.RowIndex].FindControl("DropDownListdescription3") as DropDownList).Text;  
  305.         int capacity = Convert.ToInt32(cap);  
  306.         // Displays an error message if the required fields is blank before the updating of data  
  307.         if (room == "")  
  308.         {  
  309.             LabelMsg.Visible = true;  
  310.             LabelMsg.Text = "Room name cannot be blank.";  
  311.         }  
  312.         else if (cap == "")  
  313.         {  
  314.             LabelMsg.Visible = true;  
  315.             LabelMsg.Text = "Room capacity cannot be blank.";  
  316.         }  
  317.         else  
  318.         {  
  319.             if (capacity < 35 || capacity > 45)  
  320.             {  
  321.                 LabelMsg.Visible = true;  
  322.                 LabelMsg.Text = "Room capacity must be atleast 35 and  not greater than to 40.";  
  323.             }  
  324.             else  
  325.             {  
  326.                 using (SqlConnection conn = new SqlConnection(strCon))  
  327.                 {  
  328.                     string update = "UPDATE Room set Room=@Room, Description=@Description, Capacity=@Capacity where RoomID=@RoomID";  
  329.                     using (SqlCommand cmd = new SqlCommand(update))  
  330.                     {  
  331.                         cmd.Connection = con;  
  332.                         cmd.Parameters.AddWithValue("@Room", room.Trim());  
  333.                         cmd.Parameters.AddWithValue("@Description", des);  
  334.                         cmd.Parameters.AddWithValue("@Capacity", cap.Trim());  
  335.                         cmd.Parameters.AddWithValue("@RoomID", roomID);  
  336.                         con.Open();  
  337.                         cmd.ExecuteNonQuery();  
  338.                         con.Close();  
  339.                         GridViewRoom.EditIndex = -1;  
  340.                         Response.Redirect("VPAA_Room.aspx");  
  341.                     }  
  342.                 }  
  343.             }  
  344.         }  
  345.     }  
  346.     protected void GVRoom_RowCancelingEdit(object sender, GridViewCancelEditEventArgs e)  
  347.     {  
  348.         GridViewRoom.EditIndex = -1;  
  349.         RoomData(this.TextBoxSearch.Text);  
  350.     }  
  351. }  
 
 

Answers (4)