ASP.NET Updateable DataGrid Example

Use the DataGrid control to update data in a database. This is not a very elegant way to do so, but wanting to know if it can be done then, yes, it can. The DataGrid was introduced in .NET v.1.0 and has since been replaced by the GridView in .NET v.2.0. The GridView allows creating a record-editable table of data without writing a single line of code.

<%@ Page Language="VB" %>
<%@ Import Namespace="System" %>	
<%@ Import Namespace="System.Configuration" %>	
<%@ Import Namespace="System.Collections" %>	
<%@ Import Namespace="System.Web" %>	
<%@ Import Namespace="System.Web.UI" %>	
<%@ Import Namespace="System.Web.UI.WebControls" %>	
<%@ Import Namespace="System.Web.UI.WebControls.WebParts" %>	
<%@ Import Namespace="System.Web.UI.HtmlControls" %>	
<%@ Import Namespace="System.Data" %>	
<%@ Import Namespace="System.Data.SqlClient" %>	

<script runat="server">

	Dim cmd As New SqlCommand()
	Dim da As SqlDataAdapter
	Dim ds As New DataSet()

	Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs)
		If Page.IsPostBack = False Then
			BindDataGrid1()
		End If
	End Sub

	Sub BindDataGrid1()
		'Connect to the MS SQL Server pubs Database
		cmd.CommandText = "SELECT emp_id, fname, lname FROM employee"
		cmd.Connection = New SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings("ConnectPubsSql").ToString())
		da = New SqlDataAdapter(cmd)
		da.Fill(ds)
		cmd.Connection.Open()
		cmd.ExecuteNonQuery()
		DataGrid1.DataSource = ds
		DataGrid1.DataBind()
		cmd.Connection.Close()
	End Sub

	Sub DataGrid1Update(ByVal sender As Object, ByVal e As DataGridCommandEventArgs)
		'Connect to the MS SQL Server pubs Database
		cmd.Parameters.Add("@emp_id", SqlDbType.Char).Value = CType(e.Item.Cells(0), TableCell).Text
		cmd.Parameters.Add("@fname", SqlDbType.Char).Value = CType(e.Item.Cells(1).Controls(0), TextBox).Text
		cmd.Parameters.Add("@lname", SqlDbType.Char).Value = CType(e.Item.Cells(2).Controls(0), TextBox).Text
		cmd.CommandText = "UPDATE employee SET fname=@fname, lname=@lname WHERE emp_id=@emp_id"
		cmd.Connection = New SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings("ConnectPubsSql").ToString())
		cmd.Connection.Open()
		cmd.ExecuteNonQuery()
		cmd.Connection.Close()
		DataGrid1.EditItemIndex = -1
		BindDataGrid1()
	End Sub

	Sub DataGrid1IndexChange(ByVal sender As Object, ByVal e As DataGridPageChangedEventArgs)
		DataGrid1.EditItemIndex = -1
		DataGrid1.CurrentPageIndex = e.NewPageIndex
		BindDataGrid1()
	End Sub

	Sub DataGrid1Edit(ByVal sender As Object, ByVal e As DataGridCommandEventArgs)
		DataGrid1.EditItemIndex = e.Item.ItemIndex
		BindDataGrid1()
	End Sub

	Sub DataGrid1Cancel(ByVal sender As Object, ByVal e As DataGridCommandEventArgs)
		DataGrid1.EditItemIndex = -1
		BindDataGrid1()
	End Sub

</script>

<html>
<head>
	<title>DataGrid Example</title>
</head>
<body>
	<form id="form1" runat="server">
	<div>
	<asp:DataGrid ID="DataGrid1" runat="server" PageSize="7" AllowPaging="True" DataKeyField="emp_id"	
	AutoGenerateColumns="False" OnPageIndexChanged="DataGrid1IndexChange" OnCancelCommand="DataGrid1Cancel"	
	OnEditCommand="DataGrid1Edit" OnUpdateCommand="DataGrid1Update" BackColor="White" BorderColor="#999999"
	BorderStyle="None" BorderWidth="1px" CellPadding="3" GridLines="Vertical">	
	<Columns>	
		<asp:BoundColumn HeaderText="Employee ID" ReadOnly="True" DataField="emp_id"></asp:BoundColumn>	
		<asp:BoundColumn HeaderText="First Name" DataField="fname"></asp:BoundColumn>	
		<asp:BoundColumn HeaderText="Last Name" DataField="lname"></asp:BoundColumn>	
		<asp:EditCommandColumn EditText="Edit" CancelText="Cancel" UpdateText="Update" HeaderText="Edit">	
		</asp:EditCommandColumn>
	</Columns>
		<FooterStyle BackColor="#CCCCCC" ForeColor="Black" />
		<SelectedItemStyle BackColor="#008A8C" Font-Bold="True" ForeColor="White" />
		<PagerStyle BackColor="#999999" ForeColor="Black" HorizontalAlign="Center" Mode="NumericPages" />
		<AlternatingItemStyle BackColor="#DCDCDC" />
		<ItemStyle BackColor="#EEEEEE" ForeColor="Black" />
		<HeaderStyle BackColor="#000084" Font-Bold="True" ForeColor="White" />
	</asp:DataGrid>	
	</div>
	</form>
</body>
</html>

And now in C#:


<%@ Page Language="C#" %>
<%@ Import Namespace="System" %>	
<%@ Import Namespace="System.Configuration" %>	
<%@ Import Namespace="System.Collections" %>	
<%@ Import Namespace="System.Web" %>	
<%@ Import Namespace="System.Web.UI" %>	
<%@ Import Namespace="System.Web.UI.WebControls" %>	
<%@ Import Namespace="System.Web.UI.WebControls.WebParts" %>	
<%@ Import Namespace="System.Web.UI.HtmlControls" %>	
<%@ Import Namespace="System.Data" %>	
<%@ Import Namespace="System.Data.SqlClient" %>	

<script runat="server">

	SqlCommand cmd = new SqlCommand();
	SqlDataAdapter da;
	DataSet ds = new DataSet();

	void Page_Load(object sender, System.EventArgs e)
	{
		if(Page.IsPostBack == false)
			BindDataGrid1();
	}

	void BindDataGrid1()
	{
		//Connect to the MS SQL Server pubs Database
		cmd.CommandText = "SELECT emp_id, fname, lname FROM employee";
		cmd.Connection = new SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["ConnectPubsSql"].ToString());
		da = new SqlDataAdapter(cmd);
		da.Fill(ds);
		cmd.Connection.Open();
		cmd.ExecuteNonQuery();
		DataGrid1.DataSource = ds;
		DataGrid1.DataBind();
		cmd.Connection.Close();
	}

	void DataGrid1Update(object sender, DataGridCommandEventArgs e)
	{
		//Connect to the MS SQL Server pubs Database
		cmd.Parameters.Add("@emp_id", SqlDbType.Char).Value = ((TableCell)e.Item.Cells[0]).Text;
		cmd.Parameters.Add("@fname", SqlDbType.Char).Value = ((TextBox)e.Item.Cells[1].Controls[0]).Text;
		cmd.Parameters.Add("@lname", SqlDbType.Char).Value = ((TextBox)e.Item.Cells[2].Controls[0]).Text;
		cmd.CommandText = "UPDATE employee SET fname=@fname, lname=@lname WHERE emp_id=@emp_id";
		cmd.Connection = new SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["ConnectPubsSql"].ToString());
		cmd.Connection.Open();
		cmd.ExecuteNonQuery();
		cmd.Connection.Close();
		DataGrid1.EditItemIndex = -1;
		BindDataGrid1();
	}

	void DataGrid1IndexChange(object sender, DataGridPageChangedEventArgs e)
	{
		DataGrid1.EditItemIndex = -1;
		DataGrid1.CurrentPageIndex = e.NewPageIndex;
		BindDataGrid1();
	}

	void DataGrid1Edit(object sender, DataGridCommandEventArgs e)
	{
		DataGrid1.EditItemIndex = e.Item.ItemIndex;
		BindDataGrid1();
	}

	void DataGrid1Cancel(object sender, DataGridCommandEventArgs e)
	{
		DataGrid1.EditItemIndex = -1;
		BindDataGrid1();
	}

</script>

<html>
<head>
	<title>DataGrid Example</title>
</head>
<body>
	<form id="form1" runat="server">
	<div>
	<asp:DataGrid ID="DataGrid1" runat="server" PageSize="7" AllowPaging="True" DataKeyField="emp_id"	
	AutoGenerateColumns="False" OnPageIndexChanged="DataGrid1IndexChange" OnCancelCommand="DataGrid1Cancel"	
	OnEditCommand="DataGrid1Edit" OnUpdateCommand="DataGrid1Update" BackColor="White" BorderColor="#999999"
	BorderStyle="None" BorderWidth="1px" CellPadding="3" GridLines="Vertical">	
	<Columns>	
		<asp:BoundColumn HeaderText="Employee ID" ReadOnly="True" DataField="emp_id"></asp:BoundColumn>	
		<asp:BoundColumn HeaderText="First Name" DataField="fname"></asp:BoundColumn>	
		<asp:BoundColumn HeaderText="Last Name" DataField="lname"></asp:BoundColumn>	
		<asp:EditCommandColumn EditText="Edit" CancelText="Cancel" UpdateText="Update" HeaderText="Edit">	
		</asp:EditCommandColumn>
	</Columns>
		<FooterStyle BackColor="#CCCCCC" ForeColor="Black" />
		<SelectedItemStyle BackColor="#008A8C" Font-Bold="True" ForeColor="White" />
		<PagerStyle BackColor="#999999" ForeColor="Black" HorizontalAlign="Center" Mode="NumericPages" />
		<AlternatingItemStyle BackColor="#DCDCDC" />
		<ItemStyle BackColor="#EEEEEE" ForeColor="Black" />
		<HeaderStyle BackColor="#000084" Font-Bold="True" ForeColor="White" />
	</asp:DataGrid>	
	</div>
	</form>
</body>
</html>
DataGrid1
DataGrid2
DataGrid3