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
- <%@ Page Title="Room" Language="C#" MasterPageFile="~/VPAA.master" AutoEventWireup="true" CodeFile="VPAA_Room.aspx.cs" Inherits="Pages_VPAA_Room" %>
-
- ADD NEW ROOM
- HeaderText="Please check the following error(s):" />
-
- Room:
- ForeColor="Red" ControlToValidate="TextBoxroom" Display="Dynamic" SetFocusOnError="true">*
-
- Description:
- Height="24px" Width="60px" CssClass="Add" PostBackUrl="~/Pages/VPAA_Room_Description.aspx" />
- ControlToValidate="DropDownListdescription" Display="Dynamic"
- ErrorMessage="Room description is required." ForeColor="Red" InitialValue="0">*
-
- Capacity:
- Placeholder="Enter number only">
- ForeColor="Red" ControlToValidate="TextBoxcapacity" Display="Dynamic" SetFocusOnError="true">*
- ErrorMessage="Room capacity must be atleast 35 and not greater than to 40." ForeColor="Red" Display="Dynamic"
- MaximumValue="40" MinimumValue="35" Type="Integer" SetFocusOnError="true">*
-
-
- Width="75px" Onclick="ButtonSave_Click" ToolTip="Save Data" />
- Onclick="ButtonCancel_Click" ToolTip="Clear" CssClass="Cancel" />
-
-
- CausesValidation="False" Height="28px" Width="87px" CssClass="Archive"
- OnClientClick="return confirm('The selected item(s) will be deleted.')"
- OnClick="ButtonArchive_Click" />
- CausesValidation="False" Height="28px" Width="107px" CssClass="ClearFilter"
- Onclick="ButtonClearFilter_Click" />
- Room Description:
- Height="23px" AutoPostBack="True" OnSelectedIndexchanged="DropDownListdescription2_SelectedIndexChanged" >
- CausesValidation="False" Height="25px" OnClick="ButtonSearch_Click" />
- CssClass="Search">
-
- DataKeyNames="RoomID" CellPadding="4" GridLines="Vertical" BorderColor="#CCCCCC"
- BorderStyle="Solid" ForeColor="#333333" BorderWidth="1px" EmptyDataText="No records found."
- OnRowDeleting="GVRoom_RowDeleting" OnRowDataBound="RowDataBound" OnRowCancelingEdit="GVRoom_RowCancelingEdit"
- OnPageIndexChanging="GVRoom_PageIndexChanging" OnRowEditing="GVRoom_RowEditing" OnRowUpdating="GVRoom_RowUpdating" >
- CommandName="Update" Height="27px" Width="28px" ImageAlign="Middle" ImageUrl="~/Images/Update.png" />
- CommandName="Cancel" Height="27px" Width="28px" ImageAlign="Middle" ImageUrl="~/Images/Cancel Edit.png" />
- CommandName="Edit" Height="28px" Width="30px" ImageAlign="Middle" ImageUrl="~/Images/Edit.png" />
- ToolTip="Archive Item" CssClass="ArchiveRow" CommandName="Delete" Height="28px" Width="30px" ImageAlign="Middle" ImageUrl="~/Images/Archive Item.png" />
- <%# Container.DataItemIndex + 1 %>
- Width="100px" DataSourceID="SqlDataSourceRoom_Description2"
- DataTextField="Description" DataValueField="Description">
- ConnectionString="<%$ ConnectionStrings:ScheduleConnectionString %>"
- SelectCommand="SELECT [Description] FROM [Room_Description]">
- using System;
- using System.Collections.Generic;
- using System.Linq;
- using System.Web;
- using System.Web.UI;
- using System.Web.UI.HtmlControls;
- using System.Web.UI.WebControls;
- using System.Web.UI.WebControls.WebParts;
- using System.Configuration;
- using System.Data.SqlClient;
- using System.Data;
- using System.Collections.Specialized;
- public partial class Pages_VPAA_Room : System.Web.UI.Page
- {
- // Declaring variable for SQL functions and configuring the ConnectionString needed
- SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["ScheduleConnectionString"].ConnectionString);
- SqlCommand cmd;
- SqlDataAdapter adp;
- string strCon = ConfigurationManager.ConnectionStrings["ScheduleConnectionString"].ConnectionString;
- protected void Page_Load(object sender, EventArgs e)
- {
- if (!Page.IsPostBack)
- {
- RoomData(this.TextBoxSearch.Text);
- // DataSource of the first DropDownList
- using (con = new SqlConnection(strCon))
- {
- using (cmd = new SqlCommand("SELECT * from Room_Description"))
- {
- cmd.CommandType = CommandType.Text;
- cmd.Connection = con;
- con.Open();
- DropDownListdescription.DataSource = cmd.ExecuteReader();
- DropDownListdescription.DataTextField = "Description";
- DropDownListdescription.DataValueField = "Description";
- DropDownListdescription.DataBind();
- con.Close();
- }
- }
- // Setting the Initial Value of the first DropDownList
- DropDownListdescription.Items.Insert(0, new ListItem("-- Select Description --", "0"));
- // DataSource of the second DropDownList
- using (con = new SqlConnection(strCon))
- {
- using (cmd = new SqlCommand("SELECT DISTINCT Description from Room"))
- {
- cmd.CommandType = CommandType.Text;
- cmd.Connection = con;
- con.Open();
- DropDownListdescription2.DataSource = cmd.ExecuteReader();
- DropDownListdescription2.DataTextField = "Description";
- DropDownListdescription2.DataValueField = "Description";
- DropDownListdescription2.DataBind();
- con.Close();
- }
- }
- // Setting the Initial Value of the second DropDownList
- DropDownListdescription2.Items.Insert(0, new ListItem("-- Select Description --", "0"));
- }
- LabelMsg.Visible = false;
- }
- // Binding data to GridView using SELECT parameters and setting the TextBoxSearch for room searching
- protected void RoomData(string room)
- {
- string select = "SELECT * from Room where (Room LIKE '%" + TextBoxSearch.Text + "%')";
- cmd = new SqlCommand(select);
- GridViewRoom.DataSource = GetData(cmd);
- GridViewRoom.DataBind();
- }
- private DataTable GetData(SqlCommand cmd)
- {
- using (con = new SqlConnection(strCon))
- {
- using (adp = new SqlDataAdapter())
- {
- cmd.Connection = con;
- adp.SelectCommand = cmd;
- using (DataTable dt = new DataTable())
- {
- adp.Fill(dt);
- return dt;
- }
- }
- }
- }
- protected void ButtonSave_Click(object sender, EventArgs e)
- {
- try
- {
- con.Open();
- // Checking if the data inserting is already exist in the current records to prevent data redundancy
- string CheckRoom = "SELECT count(*) from Room where Room='" + TextBoxroom.Text + "'";
- cmd = new SqlCommand(CheckRoom, con);
- int temp = Convert.ToInt32(cmd.ExecuteScalar().ToString());
- con.Close();
- // If the data already exist, it displays an error message
- if (temp == 1)
- {
- LabelMsg.Visible = true;
- LabelMsg.Text = "Room name already exist.";
- }
- // If not, then it proceed to data insertion
- // Trim() is used for removing blank spaces in the TextBox during insertion
- else
- {
- string strDate = DateTime.Now.ToShortDateString();
- con.Open();
- string insert = "INSERT into Room (Room, Description, Capacity, DateCreated) values (@Room, @Description, @Capacity, @DateCreated)";
- cmd = new SqlCommand(insert, con);
- cmd.Parameters.AddWithValue("@Room", TextBoxroom.Text.Trim());
- cmd.Parameters.AddWithValue("@Description", DropDownListdescription.SelectedValue);
- cmd.Parameters.AddWithValue("@Capacity", TextBoxcapacity.Text.Trim());
- cmd.Parameters.AddWithValue("@DateCreated", strDate);
- cmd.ExecuteNonQuery();
- Response.Redirect("VPAA_Room.aspx");
- con.Close();
- }
- }
- catch (Exception ex)
- {
- LabelMsg.Text = ex.Message.ToString();
- LabelMsg.Visible = true;
- }
- }
- protected void ButtonCancel_Click(object sender, EventArgs e)
- {
- TextBoxroom.Text = "";
- TextBoxcapacity.Text = "";
- DropDownListdescription.ClearSelection();
- }
- // Preparing the function for deleting data in GridView
- protected void DeleteRecord(int roomID)
- {
- con = new SqlConnection(strCon);
- cmd = new SqlCommand("DELETE from Room where RoomID = @RoomID", con);
- cmd.Parameters.AddWithValue("@RoomID", roomID);
- con.Open();
- cmd.ExecuteNonQuery();
- con.Close();
- }
- protected void ButtonArchive_Click(object sender, EventArgs e)
- {
- int temp = 0;
- // Read each row of GridView
- foreach (GridViewRow row in GridViewRoom.Rows)
- {
- // Identify whether the CheckBox in each row is checked or not
- CheckBox check = (CheckBox)row.FindControl("CheckBoxSelect");
- Label ID = (Label)row.FindControl("LabelRoomID");
- if (check.Checked)
- {
- string strDate = DateTime.Now.ToShortDateString();
- int roomID = Convert.ToInt32(ID.Text);
- string room = (row.FindControl("LabelRoom") as Label).Text;
- string des = (row.FindControl("LabelDescription") as Label).Text;
- string cap = (row.FindControl("LabelCapacity") as Label).Text;
- // Calling the prepared delete function before the inserting(transferring) of selected data to another table
- DeleteRecord(roomID);
- cmd = new SqlCommand("INSERT into Archive_Room (ARoom, ADescription, ACapacity, DateArchived) values ('" + room + "','" + des + "','" + cap + "', @DateArchived)", con);
- cmd.Parameters.AddWithValue("@DateArchived", strDate);
- con.Open();
- cmd.ExecuteNonQuery();
- con.Close();
- LabelMsg.Visible = true;
- LabelMsg.Text = "Data deleted.";
- temp++;
- }
- if (temp == 0)
- {
- LabelMsg.Visible = true;
- LabelMsg.Text = "Please select data.";
- }
- }
- RoomData(this.TextBoxSearch.Text);
- }
- // Displays the GridView to its original state
- protected void ButtonClearFilter_Click(object sender, EventArgs e)
- {
- Response.Redirect("VPAA_Room.aspx");
- }
- // Used for filtering the GridView using the DropDownList or TextBox
- protected void DropDownListdescription2_SelectedIndexChanged(object sender, EventArgs e)
- {
- string search = "SELECT * from Room where Description LIKE '%' + @Description + '%'";
- cmd = new SqlCommand(search, con);
- cmd.Parameters.AddWithValue("@Description", DropDownListdescription2.SelectedValue);
- int selected = Convert.ToInt32(DropDownListdescription2.SelectedIndex);
- if (DropDownListdescription2.SelectedIndex == 0)
- {
- RoomData(this.TextBoxSearch.Text);
- }
- else
- {
- DataTable dt = SearchData(cmd);
- RoomData(this.TextBoxSearch.Text);
- GridViewRoom.DataSource = dt;
- GridViewRoom.DataBind();
- }
- }
- protected void ButtonSearch_Click(object sender, EventArgs e)
- {
- string search = "SELECT * from Room where Room LIKE '%' + @Room + '%' ";
- cmd = new SqlCommand(search, con);
- cmd.Parameters.AddWithValue("@Room", TextBoxSearch.Text.Trim());
- DataTable dt = SearchData(cmd);
- RoomData(this.TextBoxSearch.Text);
- GridViewRoom.DataSource = dt;
- GridViewRoom.DataBind();
- }
- private DataTable SearchData(SqlCommand cmd)
- {
- DataTable dt = new DataTable();
- String strCon = System.Configuration.ConfigurationManager.ConnectionStrings["ScheduleConnectionString"].ConnectionString;
- SqlConnection con = new SqlConnection(strCon);
- SqlDataAdapter sda = new SqlDataAdapter();
- cmd.CommandType = CommandType.Text;
- cmd.Connection = con;
- try
- {
- con.Open();
- sda.SelectCommand = cmd;
- sda.Fill(dt);
- return dt;
- }
- catch (Exception ex)
- {
- LabelMsg.Text = ex.Message.ToString();
- LabelMsg.Visible = true;
- return null;
- }
- finally
- {
- con.Close();
- sda.Dispose();
- con.Dispose();
- }
- }
- // The following codes is used to perform the GridView functions programatically such PAGING, EDITING, CANCEL EDITING and UPDATING
- protected void RowDataBound(object sender, GridViewRowEventArgs e)
- {
- if (e.Row.RowType == DataControlRowType.DataRow)
- {
- // Marks the selected rows with black bar
- e.Row.Attributes.Add("onmouseover", "MouseEvents(this, event)");
- e.Row.Attributes.Add("onmouseout", "MouseEvents(this, event)");
- }
- }
- protected void GVRoom_PageIndexChanging(object sender, GridViewPageEventArgs e)
- {
- GridViewRoom.PageIndex = e.NewPageIndex;
- RoomData(this.TextBoxSearch.Text);
- }
- protected void GVRoom_RowEditing(object sender, GridViewEditEventArgs e)
- {
- GridViewRoom.EditIndex = e.NewEditIndex;
- this.RoomData(this.TextBoxSearch.Text);
- }
- // Preparing the function for deleting data in GridView
- protected void Delete(string roomID)
- {
- cmd = new SqlCommand("DELETE from Room where RoomID=" + roomID, con);
- con.Open();
- cmd.ExecuteNonQuery();
- con.Close();
- RoomData(this.TextBoxSearch.Text);
- }
- protected void GVRoom_RowDeleting(object sender, GridViewDeleteEventArgs e)
- {
- string strDate = DateTime.Now.ToShortDateString();
- string desID = GridViewRoom.DataKeys[e.RowIndex].Value.ToString();
- string room = (GridViewRoom.Rows[e.RowIndex].FindControl("LabelRoom") as Label).Text;
- string des = (GridViewRoom.Rows[e.RowIndex].FindControl("LabelDescription") as Label).Text;
- string cap = (GridViewRoom.Rows[e.RowIndex].FindControl("LabelCapacity") as Label).Text;
- // Calling the prepared delete function before the inserting(transferring) of selected data to another table
- Delete(desID);
- using (SqlConnection con = new SqlConnection(strCon))
- {
- string insert = "INSERT into Archive_Room (ARoom, ADescription, ACapacity, DateArchived) values ('" + room + "','" + des + "','" + cap + "', @DateArchived)";
- using (SqlCommand cmd = new SqlCommand(insert))
- {
- cmd.Connection = con;
- cmd.Parameters.AddWithValue("@ARoom", room);
- cmd.Parameters.AddWithValue("@ADescription", des);
- cmd.Parameters.AddWithValue("@DateArchived", strDate);
- con.Open();
- cmd.ExecuteNonQuery();
- con.Close();
- RoomData(this.TextBoxSearch.Text);
- }
- }
- }
- protected void GVRoom_RowUpdating(object sender, GridViewUpdateEventArgs e)
- {
- string roomID = GridViewRoom.DataKeys[e.RowIndex].Value.ToString();
- string room = (GridViewRoom.Rows[e.RowIndex].FindControl("TextBoxRoom") as TextBox).Text;
- string cap = (GridViewRoom.Rows[e.RowIndex].FindControl("TextBoxCapacity") as TextBox).Text;
- string des = (GridViewRoom.Rows[e.RowIndex].FindControl("DropDownListdescription3") as DropDownList).Text;
- int capacity = Convert.ToInt32(cap);
- // Displays an error message if the required fields is blank before the updating of data
- if (room == "")
- {
- LabelMsg.Visible = true;
- LabelMsg.Text = "Room name cannot be blank.";
- }
- else if (cap == "")
- {
- LabelMsg.Visible = true;
- LabelMsg.Text = "Room capacity cannot be blank.";
- }
- else
- {
- if (capacity < 35 || capacity > 45)
- {
- LabelMsg.Visible = true;
- LabelMsg.Text = "Room capacity must be atleast 35 and not greater than to 40.";
- }
- else
- {
- using (SqlConnection conn = new SqlConnection(strCon))
- {
- string update = "UPDATE Room set Room=@Room, Description=@Description, Capacity=@Capacity where RoomID=@RoomID";
- using (SqlCommand cmd = new SqlCommand(update))
- {
- cmd.Connection = con;
- cmd.Parameters.AddWithValue("@Room", room.Trim());
- cmd.Parameters.AddWithValue("@Description", des);
- cmd.Parameters.AddWithValue("@Capacity", cap.Trim());
- cmd.Parameters.AddWithValue("@RoomID", roomID);
- con.Open();
- cmd.ExecuteNonQuery();
- con.Close();
- GridViewRoom.EditIndex = -1;
- Response.Redirect("VPAA_Room.aspx");
- }
- }
- }
- }
- }
- protected void GVRoom_RowCancelingEdit(object sender, GridViewCancelEditEventArgs e)
- {
- GridViewRoom.EditIndex = -1;
- RoomData(this.TextBoxSearch.Text);
- }
- }
ali tuncerPosted Mar 3, 2016, 5:37 AM
if (temp == 1) //data might exist more than one time, change to if (temp > 0)
{
}
C# CornerPosted Mar 3, 2016, 9:11 AM
C# CornerPosted Mar 3, 2016, 8:26 AM
Shubham KumarPosted Mar 3, 2016, 3:59 AM