I have tried below mentioned sample code and its working fine.
My only concern is how to set visibility of EDIT or DELETE button dynamically depending on value from any other column.
Can anyone help me in this..?
private String strConnString = ConfigurationManager.ConnectionStrings["conString"].ConnectionString;
protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
BindData();
}
}
private void BindData()
{
string strQuery = "select CustomerID,ContactName,CompanyName" +
" from customers";
SqlCommand cmd = new SqlCommand(strQuery);
GridView1.DataSource = GetData(cmd);
GridView1.DataBind();
}
private DataTable GetData(SqlCommand cmd)
{
DataTable dt = new DataTable();
SqlConnection con = new SqlConnection(strConnString);
SqlDataAdapter sda = new SqlDataAdapter();
cmd.CommandType = CommandType.Text;
cmd.Connection = con;
con.Open();
sda.SelectCommand = cmd;
sda.Fill(dt);
return dt;
}
protected void OnPaging(object sender, GridViewPageEventArgs e)
{
BindData();
GridView1.PageIndex = e.NewPageIndex;
GridView1.DataBind();
}
protected void AddNewCustomer(object sender, EventArgs e)
{
string CustomerID = ((TextBox)GridView1.FooterRow.FindControl("txtCustomerID")).Text;
string Name = ((TextBox)GridView1.FooterRow.FindControl("txtContactName")).Text;
string Company = ((TextBox)GridView1.FooterRow.FindControl("txtCompany")).Text;
SqlConnection con = new SqlConnection(strConnString);
SqlCommand cmd = new SqlCommand();
cmd.CommandType = CommandType.Text;
cmd.CommandText = "insert into customers(CustomerID, ContactName, CompanyName) " +
"values(@CustomerID, @ContactName, @CompanyName);" +
"select CustomerID,ContactName,CompanyName from customers";
cmd.Parameters.Add("@CustomerID", SqlDbType.VarChar).Value = CustomerID;
cmd.Parameters.Add("@ContactName", SqlDbType.VarChar).Value = Name;
cmd.Parameters.Add("@CompanyName", SqlDbType.VarChar).Value = Company;
GridView1.DataSource = GetData(cmd);
GridView1.DataBind();
}
protected void DeleteCustomer(object sender, EventArgs e)
{
LinkButton lnkRemove = (LinkButton)sender;
SqlConnection con = new SqlConnection(strConnString);
SqlCommand cmd = new SqlCommand();
cmd.CommandType = CommandType.Text;
cmd.CommandText = "delete from customers where " +
"CustomerID=@CustomerID;" +
"select CustomerID,ContactName,CompanyName from customers";
cmd.Parameters.Add("@CustomerID", SqlDbType.VarChar).Value = lnkRemove.CommandArgument;
GridView1.DataSource = GetData(cmd);
GridView1.DataBind();
}
protected void EditCustomer(object sender, GridViewEditEventArgs e)
{
GridView1.EditIndex = e.NewEditIndex;
BindData();
}
protected void CancelEdit(object sender, GridViewCancelEditEventArgs e)
{
GridView1.EditIndex = -1;
BindData();
}
protected void UpdateCustomer(object sender, GridViewUpdateEventArgs e)
{
string CustomerID = ((Label)GridView1.Rows[e.RowIndex].FindControl("lblCustomerID")).Text;
string Name = ((TextBox)GridView1.Rows[e.RowIndex].FindControl("txtContactName")).Text;
string Company = ((TextBox)GridView1.Rows[e.RowIndex].FindControl("txtCompany")).Text;
SqlConnection con = new SqlConnection(strConnString);
SqlCommand cmd = new SqlCommand();
cmd.CommandType = CommandType.Text;
cmd.CommandText = "update customers set ContactName=@ContactName,CompanyName=@CompanyName " +
"where CustomerID=@CustomerID;" +
"select CustomerID,ContactName,CompanyName from customers";
cmd.Parameters.Add("@CustomerID", SqlDbType.VarChar).Value = CustomerID;
cmd.Parameters.Add("@ContactName", SqlDbType.VarChar).Value = Name;
cmd.Parameters.Add("@CompanyName", SqlDbType.VarChar).Value = Company;
GridView1.EditIndex = -1;
GridView1.DataSource = GetData(cmd);
GridView1.DataBind();
}
<asp:GridView ID="GridView1" runat="server" Width = "550px"
AutoGenerateColumns = "false" Font-Names = "Arial"
Font-Size = "11pt" AlternatingRowStyle-BackColor = "#C2D69B"
HeaderStyle-BackColor = "green" AllowPaging ="true" ShowFooter = "true"
OnPageIndexChanging = "OnPaging" onrowediting="EditCustomer"
onrowupdating="UpdateCustomer" onrowcancelingedit="CancelEdit"
PageSize = "10" >
<Columns>
<asp:TemplateField ItemStyle-Width = "30px" HeaderText = "CustomerID">
<ItemTemplate>
<asp:Label ID="lblCustomerID" runat="server" Text='<%# Eval("CustomerID")%>'></asp:Label>
</ItemTemplate>
<FooterTemplate>
<asp:TextBox ID="txtCustomerID" Width = "40px" MaxLength = "5" runat="server"></asp:TextBox>
</FooterTemplate>
</asp:TemplateField>
<asp:TemplateField ItemStyle-Width = "100px" HeaderText = "Name">
<ItemTemplate>
<asp:Label ID="lblContactName" runat="server" Text='<%# Eval("ContactName")%>'></asp:Label>
</ItemTemplate>
<EditItemTemplate>
<asp:TextBox ID="txtContactName" runat="server" Text='<%# Eval("ContactName")%>'></asp:TextBox>
</EditItemTemplate>
<FooterTemplate>
<asp:TextBox ID="txtContactName" runat="server"></asp:TextBox>
</FooterTemplate>
</asp:TemplateField>
<asp:TemplateField ItemStyle-Width = "150px" HeaderText = "Company">
<ItemTemplate>
<asp:Label ID="lblCompany" runat="server" Text='<%# Eval("CompanyName")%>'></asp:Label>
</ItemTemplate>
<EditItemTemplate>
<asp:TextBox ID="txtCompany" runat="server" Text='<%# Eval("CompanyName")%>'></asp:TextBox>
</EditItemTemplate>
<FooterTemplate>
<asp:TextBox ID="txtCompany" runat="server"></asp:TextBox>
</FooterTemplate>
</asp:TemplateField>
<asp:TemplateField>
<ItemTemplate>
<asp:LinkButton ID="lnkRemove" runat="server" CommandArgument = '<%# Eval("CustomerID")%>'
OnClientClick = "return confirm('Do you want to delete?')"
Text = "Delete" OnClick = "DeleteCustomer"></asp:LinkButton>
</ItemTemplate>
<FooterTemplate>
<asp:Button ID="btnAdd" runat="server" Text="Add" OnClick = "AddNewCustomer" />
</FooterTemplate>
</asp:TemplateField>
<asp:CommandField ShowEditButton="True" />
</Columns>
<AlternatingRowStyle BackColor="#C2D69B" />
</asp:GridView>