Hi Manoj,
I have tried your code its working but po no. showing in gridview but not inserting into gridview. one more thing after clicking add new row button new row will be adding to gridview in that row only the generated number(cbu001) should show, but whenever i press add new row button it is showing cbu001 only after entering all the data in row pressing insert button then it will show increamented numbers, i want number should increase and show whenever i press add new row button. please help me.
for your reference my .aspx page is
<%@ Page Language="C#" AutoEventWireup="true" CodeFile="addeditdelete.aspx.cs" Inherits="Default3" Debug="true"%>
<%@ Register assembly="AjaxControlToolkit" namespace="AjaxControlToolkit" tagprefix="asp" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<title></title>
<link href="GridViewCSSThemes/GlassBlackGridView.css" rel="stylesheet" type="text/css" />
</head>
<body>
<form id="form1" runat="server">
<div>
<%-- <div style="width: 575px; height: 500px; overflow: scroll">--%>
<%-- <asp:Panel ID="Panel1" runat="server" Height="500px"
Width="500px" ScrollBars="Vertical">--%>
<asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" CellPadding="4"
DataKeyNames="cat_id" ForeColor="#333333" GridLines="None" OnRowCancelingEdit="GridView1_RowCancelingEdit"
OnRowEditing="GridView1_RowEditing" OnRowUpdating="GridView1_RowUpdating" CssClass="GridViewStyle" OnRowDataBound="GridView1_RowDataBound">
<FooterStyle BackColor="#1C5E55" Font-Bold="True" ForeColor="White" />
<Columns>
<asp:TemplateField>
<ItemTemplate>
<asp:CheckBox ID="CheckBox1" runat="server" />
</ItemTemplate>
</asp:TemplateField>
<asp:CommandField HeaderText="Edit-Update" ShowEditButton="True" />
<asp:BoundField DataField="cat_id" HeaderText="Category ID" ReadOnly="True" />
<asp:BoundField DataField="cat_name" HeaderText="Category Name" />
<asp:BoundField DataField="Location" HeaderText="Location" />
<%--<asp:BoundField DataField="Date" HeaderText="Date" />--%>
<asp:TemplateField>
<ItemTemplate>
<asp:TextBox ID="TextBox1" runat="server" Text='<%# Eval("Date") %>'></asp:TextBox>
<asp:CalendarExtender ID="TextBox1_CalendarExtender" runat="server"
Enabled="True" TargetControlID="TextBox1">
</asp:CalendarExtender>
</ItemTemplate>
</asp:TemplateField>
</Columns>
<RowStyle CssClass="RowStyle" />
<EmptyDataRowStyle CssClass="EmptyRowStyle" />
<PagerStyle CssClass="PagerStyle" />
<SelectedRowStyle CssClass="SelectedRowStyle" />
<HeaderStyle CssClass="HeaderStyle" />
<AlternatingRowStyle CssClass="AltRowStyle" />
</asp:GridView>
<br />
<asp:ScriptManager ID="ScriptManager1" runat="server">
</asp:ScriptManager>
<br />
<br />
</div>
<br />
<asp:Button ID="Button1" runat="server" Text="Add New Row"
onclick="Button1_Click" />
<asp:Button ID="btnDelete" runat="server" OnClick="btnDelete_Click" Text="Delete Selected Records" />
</form>
</body>
</html>
my .aspx.cs code is
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data;
using System.Data.SqlClient;
using System.Collections.Specialized;
using System.Text;
using System.Collections;
public partial class Default3 : System.Web.UI.Page
{
SqlConnection con = new SqlConnection();
SqlCommand cmd = new SqlCommand();
protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
BindData();
btnDelete.Attributes.Add("onclick",
"return confirm('Are you sure you want to delete selected item(s) ?');");
}
}
protected void GridView1_RowDataBound(object sender, GridViewRowEventArgs e)
{
if (e.Row.RowType == DataControlRowType.DataRow)
e.Row.Cells[3].Text = "cbu" + Convert.ToString((e.Row.RowIndex + 1) + (GridView1.PageIndex) * (GridView1.PageSize));
}
private void BindData()
{
SqlConnection con = new SqlConnection("Data source=hobvision07; initial catalog=master; user id=sa; password=hobvision");
SqlDataAdapter da = new SqlDataAdapter("SELECT cat_id, cat_name,location,date FROM quest_categories", con);
DataTable dt = new DataTable();
da.Fill(dt);
GridView1.DataSource = dt;
GridView1.DataBind();
}
protected void Button1_Click(object sender, EventArgs e)
{
SqlConnection con = new SqlConnection("Data source=hobvision07; initial catalog=master; user id=sa; password=hobvision");
SqlDataAdapter da = new SqlDataAdapter("SELECT cat_id, cat_name,location,date FROM quest_categories", con);
DataTable dt = new DataTable();
da.Fill(dt);
// Here we'll add a blank row to the returned DataTable
DataRow dr = dt.NewRow();
dt.Rows.InsertAt(dr, 0);
//Creating the first row of GridView to be Editable
GridView1.EditIndex = 0;
GridView1.DataSource = dt;
GridView1.DataBind();
//Changing the Text for Inserting a New Record
((LinkButton)GridView1.Rows[0].Cells[1].Controls[0]).Text = "Insert";
}
protected void GridView1_RowEditing(object sender, GridViewEditEventArgs e)
{
GridView1.EditIndex = e.NewEditIndex;
BindData();
}
protected void GridView1_RowCancelingEdit(object sender, GridViewCancelEditEventArgs e)
{
GridView1.EditIndex = -1;
BindData();
}
protected void GridView1_RowUpdating(object sender, GridViewUpdateEventArgs e)
{
if (((LinkButton)GridView1.Rows[0].Cells[1].Controls[0]).Text == "Insert")
{
SqlConnection con = new SqlConnection("Data source=hobvision07; initial catalog=master; user id=sa; password=hobvision");
SqlCommand cmd = new SqlCommand();
cmd.CommandText = "INSERT INTO quest_categories(cat_name,location,date) VALUES(@cat_name,@location,@date)"; cmd.Parameters.Add("@cat_name", SqlDbType.VarChar).Value = ((TextBox)GridView1.Rows[0].Cells[3].Controls[0]).Text;
cmd.Parameters.Add("@location", SqlDbType.VarChar).Value = ((TextBox)GridView1.Rows[0].Cells[4].Controls[0]).Text;
//cmd.Parameters.Add("@date", SqlDbType.VarChar).Value = ((TextBox)GridView1.Rows[0].Cells[5].Controls[0]).Text;
cmd.Parameters.Add("@date", SqlDbType.VarChar).Value = ((TextBox)GridView1.Rows[0].FindControl("TextBox1")).Text;
cmd.Connection = con;
con.Open();
cmd.ExecuteNonQuery();
con.Close();
}
else
{
SqlConnection con = new SqlConnection("Data source=hobvision07; initial catalog=master; user id=sa; password=hobvision");
SqlCommand cmd = new SqlCommand();
cmd.CommandText = "UPDATE quest_categories SET cat_name=@cat_name,location=@location,date=@date WHERE cat_id=@cat_id";
cmd.Parameters.Add("@cat_name", SqlDbType.VarChar).Value = ((TextBox)GridView1.Rows[e.RowIndex].Cells[3].Controls[0]).Text;
cmd.Parameters.Add("@location", SqlDbType.VarChar).Value = ((TextBox)GridView1.Rows[e.RowIndex].Cells[4].Controls[0]).Text;
cmd.Parameters.Add("@date", SqlDbType.VarChar).Value = ((TextBox)GridView1.Rows[0].FindControl("TextBox1")).Text;
cmd.Parameters.Add("@cat_id", SqlDbType.Int).Value = Convert.ToInt32(GridView1.Rows[e.RowIndex].Cells[2].Text);
cmd.Connection = con;
con.Open();
cmd.ExecuteNonQuery();
con.Close();
}
GridView1.EditIndex = -1;
BindData();
}
protected void btnDelete_Click(object sender, EventArgs e)
{
StringCollection idCollection = new StringCollection();
string strID = string.Empty;
//Loop through GridView rows to find checked rows
for (int i = 0; i < GridView1.Rows.Count; i++)
{
CheckBox chkDelete = (CheckBox)
GridView1.Rows[i].Cells[0].FindControl("CheckBox1");
if (chkDelete != null)
{
if (chkDelete.Checked)
{
strID = GridView1.Rows[i].Cells[2].Text;
idCollection.Add(strID);
}
}
}
//Call the method to Delete records
DeleteMultipleRecords(idCollection);
// rebind the GridView
GridView1.DataBind();
BindData();
}
private void DeleteMultipleRecords(StringCollection idCollection)
{
//Create sql Connection and Sql Command
SqlConnection con = new SqlConnection("data source=hobvision07; initial catalog=master; user id=sa; password=hobvision");
SqlCommand cmd = new SqlCommand();
string IDs = "";
foreach (string id in idCollection)
{
IDs += id.ToString() + ",";
}
try
{
string strIDs =
IDs.Substring(0, IDs.LastIndexOf(","));
string strSql = "Delete from quest_categories WHERE cat_id in (" + strIDs + ")";
cmd.CommandType = CommandType.Text;
cmd.CommandText = strSql;
cmd.Connection = con;
con.Open();
cmd.ExecuteNonQuery();
}
catch (SqlException ex)
{
string errorMsg = "Error in Deletion";
errorMsg += ex.Message;
throw new Exception(errorMsg);
}
finally
{
con.Close();
}
}
}