hi This Gupta,
below is my code, i am getting error it is Bold and RED marked in row commandevnt,here role coming based on function
/
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Drawing;
using System.IO;
using System.Text;
using System.Web.UI.HtmlControls;
public partial class Weightage_ : System.Web.UI.Page
{
SqlConnection cnn;
SqlCommand _cmd;
SqlDataReader dr;
protected void Page_Load(object sender, EventArgs e)
{
string ConnectionString = ConfigurationManager.ConnectionStrings["Database"].ConnectionString;
cnn = new SqlConnection(ConnectionString);
btn_Save.Attributes.Add("onclick", "return AddNewControlValidation()");
if (!IsPostBack)
{
btn_Upadate.Visible = false;
btn_Export.Visible = false;
// gvdatabind();
BindFyear();
BindQuartr();
BindFunction();
}
if (IsPostBack)
{
btn_Upadate.Attributes.Add("onclick", "return AddNewControlValidation()");
}
}
#region Events
protected void btn_Save_Click(object sender, EventArgs e)
{
_cmd = new SqlCommand("insert into WEIGHTAGE values('" + FYear_DrpDown.SelectedItem.Text + "','" + Quarter_DrpDown.SelectedItem.Text + "','" + Role_DrpDown.SelectedItem.Text + "','" + SelfWeitage_textBox.Text + "','" + TeamachWeitage_textBox.Text + "','" + OrgWeitage_textBox.Text + "','" + Function_DrpDown.SelectedItem.Text + "')", cnn);
cnn.Open();
_cmd.ExecuteNonQuery();
cnn.Close();
lbl_msg.Text = "Data inserted successfully";
lbl_msg.ForeColor = Color.Blue;
// gvdatabind();
Clearcontrols();
}
protected void btn_Upadate_Click(object sender, EventArgs e)
{
btn_Save.Visible = false;
btn_Upadate.Visible = true;
string id = Session["wid"].ToString();
_cmd = new SqlCommand("update WEIGHTAGE set FIN_YEAR='" + FYear_DrpDown.SelectedItem.Text + "', QUATER='" + Quarter_DrpDown.SelectedItem.Text + "',ROLE='" + Role_DrpDown.SelectedItem.Text + "',SELF_ACH='" + SelfWeitage_textBox.Text + "',TEAM_ACH='" + TeamachWeitage_textBox.Text + "',ORG_ACH='" + OrgWeitage_textBox.Text + "',[FUNCTION]='" + Function_DrpDown.SelectedItem.Text + "' where W_ID='" + id + "'", cnn);
cnn.Open();
_cmd.ExecuteNonQuery();
cnn.Close();
lbl_msg.Text = "Data Updated successfully";
lbl_msg.ForeColor = Color.Blue;
// gvdatabind();
Clearcontrols();
btn_Save.Visible = true;
btn_Upadate.Visible = false;
}
protected void ShowDetails_Grd_RowCommand(object sender, GridViewCommandEventArgs e)
{
btn_Save.Visible = false;
btn_Upadate.Visible = true;
Session["wid"] = e.CommandArgument.ToString();
string id = Session["wid"].ToString();
if (e.CommandName == "Edit")
{
_cmd = new SqlCommand("select W_ID,FIN_YEAR,QUATER,[FUNCTION],ROLE,SELF_ACH,TEAM_ACH,ORG_ACH from WEIGHTAGE where W_ID='" + id + "'", cnn);
cnn.Open();
_cmd.ExecuteNonQuery();
SqlDataAdapter da = new SqlDataAdapter(_cmd);
DataSet ds = new DataSet();
da.Fill(ds);
FYear_DrpDown.SelectedItem.Text = ds.Tables[0].Rows[0]["FIN_YEAR"].ToString();
Quarter_DrpDown.SelectedItem.Text = ds.Tables[0].Rows[0]["QUATER"].ToString();
Function_DrpDown.SelectedItem.Text = ds.Tables[0].Rows[0]["FUNCTION"].ToString();
// Role_DrpDown.SelectedItem.Text = ds.Tables[0].Rows[0]["ROLE"].ToString();
SelfWeitage_textBox.Text = ds.Tables[0].Rows[0]["SELF_ACH"].ToString();
TeamachWeitage_textBox.Text = ds.Tables[0].Rows[0]["TEAM_ACH"].ToString();
OrgWeitage_textBox.Text = ds.Tables[0].Rows[0]["ORG_ACH"].ToString();
cnn.Close();
}
else if (e.CommandName == "Delete")
{
cnn.Open();
_cmd = new SqlCommand("DELETE FROM WEIGHTAGE WHERE W_ID='" + id + "'", cnn);
_cmd.ExecuteScalar();
cnn.Close();
//gvdatabind();
searchdata();
lbl_msg.Text = "Data Deleted successfully";
}
}
protected void Function_DrpDown_SelectedIndexChanged(object sender, EventArgs e)
{
ShowDetails_Grd.EditIndex = -1;
// gvdatabind();
Bindrole();
}
protected void ShowDetails_Grd_RowEditing(object sender, GridViewEditEventArgs e)
{
//// ShowDetails_Grd.EditIndex = -1;
ShowDetails_Grd.EditIndex = e.NewEditIndex;
//// searchdata();
// gvdatabind();
}
protected void ShowDetails_Grd_RowDeleting(object sender, GridViewDeleteEventArgs e)
{
ShowDetails_Grd.EditIndex = -1;
searchdata();
// gvdatabind();
}
protected void btn_Cancel_Click(object sender, EventArgs e)
{
Clearcontrols();
// ShowDetails_Grd.EditIndex = -1;
btn_Save.Visible = true;
btn_Upadate.Visible = false;
}
protected void ShowDetails_Grd_RowCancelingEdit(object sender, GridViewCancelEditEventArgs e)
{
ShowDetails_Grd.EditIndex = -1;
searchdata();
// gvdatabind();
}
protected void ShowDetails_Grd_PageIndexChanging(object sender, GridViewPageEventArgs e)
{
ShowDetails_Grd.PageIndex = e.NewPageIndex;
searchdata();
// gvdatabind();
}
protected void btn_Export_Click(object sender, EventArgs e)
{
HideControls();
string attachment = "attachment; filename=Weightage.xls";
Response.ClearContent();
Response.AddHeader("content-disposition", attachment);
Response.ContentType = "application/ms-excel";
StringWriter sw = new StringWriter();
HtmlTextWriter htw = new HtmlTextWriter(sw);
// Create a form to contain the grid
HtmlForm frm = new HtmlForm();
ShowDetails_Grd.Parent.Controls.Add(frm);
frm.Attributes["runat"] = "server";
frm.Controls.Add(ShowDetails_Grd);
frm.RenderControl(htw);
//GridView1.RenderControl(htw);
Response.Write(sw.ToString());
Response.End();
}
public override void VerifyRenderingInServerForm(Control control)
{
/* Confirms that an HtmlForm control is rendered for the specified ASP.NET
server control at run time. */
}
private void HideControls()
{
try
{
ShowDetails_Grd.Columns[5].Visible = false;
foreach (GridViewRow row in ShowDetails_Grd.Rows)
{
LinkButton lnk = (LinkButton)row.FindControl("LinkButton1");
if (lnk != null)
{
lnk.Visible = false;
}
LinkButton lnk1 = (LinkButton)row.FindControl("LinkButton2");
if (lnk1 != null)
{
lnk1.Visible = false;
}
}
}
catch (Exception ex)
{
Response.Write("<script>alert('" + ex.Message + "')</script>");
}
}
private void PrepareGridViewForExport(System.Web.UI.Control gv)
{
LinkButton lb = new LinkButton();
Literal l = new Literal();
string name = String.Empty;
for (int i = 0; i < gv.Controls.Count; i++)
{
// PrepareGridViewForExport(ShowDetails_Grd.Controls);
}
}
int ID = 1;
protected void btn_Srch_Click(object sender, EventArgs e)
{
ShowDetails_Grd.Visible = true;
searchdata();
}
#endregion
#region Methods
public void searchdata()
{
string l_query = "select W_ID,FIN_YEAR,QUATER,[ROLE],SELF_ACH,TEAM_ACH,ORG_ACH,[FUNCTION] from WEIGHTAGE where (SELF_ACH like '%" + SelfWeitage_textBox.Text + "%' or SELF_ACH is NULL) and (TEAM_ACH like '%" + TeamachWeitage_textBox.Text + "%' or TEAM_ACH is NULL) and (ORG_ACH like'%" + OrgWeitage_textBox.Text + "%' or ORG_ACH is NULL) and ";
StringBuilder l_queryBuilder = new StringBuilder();
l_queryBuilder.Append(l_query);
if (FYear_DrpDown.SelectedItem.Text != "--Select--")
{
l_queryBuilder.Append("FIN_YEAR like '%" + FYear_DrpDown.SelectedItem.Text + "%'");
}
if (Quarter_DrpDown.SelectedItem.Text != "--Select--" && FYear_DrpDown.SelectedItem.Text != "--Select--")
{
l_queryBuilder.Append("and QUATER like '%" + Quarter_DrpDown.SelectedItem.Text + "%'");
}
else if (Quarter_DrpDown.SelectedItem.Text != "--Select--")
{
l_queryBuilder.Append("QUATER like '%" + Quarter_DrpDown.SelectedItem.Text + "%'");
}
if (Quarter_DrpDown.SelectedItem.Text != "--Select--" && FYear_DrpDown.SelectedItem.Text != "--Select--" && Function_DrpDown.SelectedItem.Text != "--Select--")
{
l_queryBuilder.Append("and [FUNCTION] like '%" + Function_DrpDown.SelectedItem.Text + "%'");
}
else if (Function_DrpDown.SelectedItem.Text != "--Select--")
{
l_queryBuilder.Append("[FUNCTION] like '%" + Function_DrpDown.SelectedItem.Text + "%'");
}
//if (Quarter_DrpDown.SelectedItem.Text != "--Select--" && FYear_DrpDown.SelectedItem.Text != "--Select--" && Function_DrpDown.SelectedItem.Text != "--Select--" && Role_DrpDown.SelectedItem.Text != "--Select--")
//{
// l_queryBuilder.Append("and [ROLE] like '%" + Role_DrpDown.SelectedItem.Text + "%'");
//}
//else if (Role_DrpDown.SelectedItem.Text != "--Select--")
//{
// l_queryBuilder.Append("[ROLE] like '%" + Role_DrpDown.SelectedItem.Text + "%'");
//}
////Ending of below condition add (&& Role_DrpDown.SelectedItem.Text == "--Select--") this one
if (Quarter_DrpDown.SelectedItem.Text == "--Select--" && FYear_DrpDown.SelectedItem.Text == "--Select--" && Function_DrpDown.SelectedItem.Text == "--Select--")
{
string srchqry = "select W_ID,FIN_YEAR,QUATER,[ROLE],SELF_ACH,TEAM_ACH,ORG_ACH,[FUNCTION] from WEIGHTAGE where (SELF_ACH like '%" + SelfWeitage_textBox.Text + "%' or SELF_ACH is NULL) and (TEAM_ACH like '%" + TeamachWeitage_textBox.Text + "%' or TEAM_ACH is NULL) and (ORG_ACH like'%" + OrgWeitage_textBox.Text + "%' or ORG_ACH is NULL)";
_cmd = new SqlCommand(srchqry.ToString(), cnn);
}
else
{
_cmd = new SqlCommand(l_queryBuilder.ToString(), cnn);
}
cnn.Open();
dr = _cmd.ExecuteReader();
DataTable dt = new DataTable();
dt.Load(dr);
cnn.Close();
if (dt.Rows.Count > 0)
{
lbl_msg.Text = "";
ShowDetails_Grd.DataSource = dt;
ShowDetails_Grd.DataBind();
lbl_msg.Text = "";
btn_Export.Visible = true;
//Btn_ExportRole_Mapping.Visible = false;
}
else
{
ShowDetails_Grd.DataSource = dt;
ShowDetails_Grd.DataBind();
lbl_msg.Visible = true;
lbl_msg.Text = "No records found";
lbl_msg.ForeColor = Color.Red;
btn_Export.Visible = false;
}
}
public void gvdatabind()
{
cnn.Open();
_cmd = new SqlCommand("select * from WEIGHTAGE", cnn);
dr = _cmd.ExecuteReader();
DataTable dt = new DataTable();
dt.Load(dr);
ShowDetails_Grd.DataSource = dt;
ShowDetails_Grd.DataBind();
cnn.Close();
}
public void Clearcontrols()
{
Bindrole();
BindFyear();
BindQuartr();
BindFunction();
SelfWeitage_textBox.Text = string.Empty;
TeamachWeitage_textBox.Text = string.Empty;
OrgWeitage_textBox.Text = string.Empty;
ShowDetails_Grd.EditIndex = -1;
// gvdatabind();
}
public void Bindrole()
{
//Role_DrpDown.Items.Clear();
if (!string.IsNullOrEmpty(Function_DrpDown.SelectedItem.Text))
{
_cmd = new SqlCommand("select ID_FUNC, ROLES from Role_master where ID_FUNC='" + Function_DrpDown.SelectedItem.Value + "'", cnn);
}
cnn.Open();
SqlDataReader dr1 = _cmd.ExecuteReader();
DataTable dt1 = new DataTable();
dt1.Load(dr1);
Role_DrpDown.DataSource = dt1;
Role_DrpDown.DataValueField = "ID_FUNC";
Role_DrpDown.DataTextField = "ROLES";
Role_DrpDown.DataBind();
Role_DrpDown.Items.Insert(0, new ListItem("--Select--", ""));
cnn.Close();
}
public void BindFyear()
{
_cmd = new SqlCommand("select ID_FIN, FIN_YEAR from Financial_year_master", cnn);
cnn.Open();
SqlDataReader dr1 = _cmd.ExecuteReader();
DataTable dt1 = new DataTable();
dt1.Load(dr1);
FYear_DrpDown.DataSource = dt1;
FYear_DrpDown.DataValueField = "ID_FIN";
FYear_DrpDown.DataTextField = "FIN_YEAR";
FYear_DrpDown.DataBind();
FYear_DrpDown.Items.Insert(0, new ListItem("--Select--", ""));
cnn.Close();
}
public void BindQuartr()
{
_cmd = new SqlCommand("select ID_QUARTER,QUARTER from Quarter_master", cnn);
cnn.Open();
SqlDataReader dr1 = _cmd.ExecuteReader();
DataTable dt1 = new DataTable();
dt1.Load(dr1);
Quarter_DrpDown.DataSource = dt1;
Quarter_DrpDown.DataValueField = "ID_QUARTER";
Quarter_DrpDown.DataTextField = "QUARTER";
Quarter_DrpDown.DataBind();
Quarter_DrpDown.Items.Insert(0, new ListItem("--Select--", ""));
cnn.Close();
}
public void BindFunction()
{
_cmd = new SqlCommand("select ID_FUNC,[FUNCTION] from Function_master", cnn);
cnn.Open();
SqlDataReader dr1 = _cmd.ExecuteReader();
DataTable dt1 = new DataTable();
dt1.Load(dr1);
Function_DrpDown.DataSource = dt1;
Function_DrpDown.DataValueField = "ID_FUNC";
Function_DrpDown.DataTextField = "FUNCTION";
Function_DrpDown.DataBind();
Function_DrpDown.Items.Insert(0, new ListItem("--Select--", ""));
cnn.Close();
}
#endregion
}