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
- 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
- {
-
- 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);
-
- 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();
- }
- }
-
- DropDownListdescription.Items.Insert(0, new ListItem("-- Select Description --", "0"));
-
-
- 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();
- }
- }
-
- DropDownListdescription2.Items.Insert(0, new ListItem("-- Select Description --", "0"));
- }
- LabelMsg.Visible = false;
- }
-
- 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();
-
- 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 (temp == 1)
- {
- LabelMsg.Visible = true;
- LabelMsg.Text = "Room name already exist.";
- }
-
-
- 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();
- }
-
- 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;
-
- foreach (GridViewRow row in GridViewRoom.Rows)
- {
-
- 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;
-
- 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);
- }
-
- protected void ButtonClearFilter_Click(object sender, EventArgs e)
- {
- Response.Redirect("VPAA_Room.aspx");
- }
-
- 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();
- }
- }
-
- protected void RowDataBound(object sender, GridViewRowEventArgs e)
- {
- if (e.Row.RowType == DataControlRowType.DataRow)
- {
-
- 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);
- }
-
- 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;
-
-
- 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);
-
- 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);
- }
- }