PROWAREtech
ASP.NET: Updateable DataGrid
An updateable Web Form DataGrid example (.NET Framework).
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 v1.0 and has since been replaced by the GridView in .NET v2.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>
Comment
