Add (INSERT) a New Row Using the ASP.Net Datagrid Control...
By: John Kilgo
Date: February 18, 2003
Download the code.
Printer Friendly Version
I have to admit that I struggled a bit for a title for this article. The title is a misnomer.
The ASP.Net datagrid control does not support adding (INSERTing) a new row into a table. You can
update rows as well as delete them using inbuilt features of the control, but you cannot do
Inserts without writing a fair amount of your own code. Even then, there are several different
approaches you can take. None of the approaches are without some problems so you end up dealing
with the lesser of several evils. One of the approaches, which I have taken for purpose of this
example, is to handle the Insert outside of the grid entirely rather than doing it strictly
within the grid. I think you will find that this approach is a little more straight forward than
doing the insert within the grid, but is still pleasing to the eye.
In this article we will display both the grid and INSERT facility inside an html table. This will
tie the datagrid and the INSERT facility together to make a fairly pleasing presentation. The
best way to see how this works is to get right to the code. As with most dotnetjohn articles we
will use a .aspx page for presentation and a .vb code-behind page to accomplish all the work. First
the .aspx file.
The first third or so of the markup sets properties of the datagrid and creates a HeaderStyle
as well as an ItemStyle for the grid. The middle third of the table is where we define textboxes
to contain the data (LastName and FirstName) that we will add to the database table. The
html table is formatted so that our two textboxes line up with the columns of the datagrid they
will go into. We have also included RequiredFieldValidators for the two textboxes to ensure data
integrity for the INSERT. The bottom third of the markup is where we create an Insert button.
We use its OnClick property to set up an OnClick event which is where the actual database INSERT
will occur in our code-behind page.
<%@ Page Language="vb" Src="DataGridAdd.aspx.vb" Inherits="DotNetJohn.DataGridAdd" %>
<html>
<head>
<title>DataGrid - Insert</title>
</head>
<body>
<form id="Form1" method="post" runat="server">
<table width="600" cellSpacing="0" cellPadding="0" border="0">
<tr>
<td colspan="3">
<asp:DataGrid id="dtgEmp" runat="server"
Width="600"
BorderColor="#006699"
BorderWidth="1"
BackColor="White"
CellPadding="2">
<ItemStyle ForeColor="#31319C"
BackColor="White" />
<HeaderStyle Font-Bold="True"
ForeColor="White"
BackColor="#006699" />
</asp:DataGrid>
</td>
</tr>
<tr>
<td width="37%" valign="top" align="center">
<font color="#006699">
New Row:</font>
</td>
<td width="31%" valign="top">
<asp:TextBox id="txtLastName" runat="server" />
<asp:RequiredFieldValidator id="rfvLastName" runat="server"
ErrorMessage="*Required*"
ControlToValidate="txtLastName" />
</td>
<td width="32%" valign="top">
<asp:TextBox id="txtFirstName" runat="server" />
<asp:RequiredFieldValidator id="rfvFirstName" runat="server"
ErrorMessage="*Required*"
ControlToValidate="txtFirstName" />
</td>
</tr>
<tr>
<td colSpan="3" align="center">
<asp:Button id="btnInsert"
runat="server"
OnClick="btnInsert_Click"
BackColor="#006699"
ForeColor="White"
Font-Bold="True"
Text="Insert" />
</td>
</tr>
</table>
</form>
</body>
</html>
Now for our code-behind page where all the work gets done. The first approximately two-thirds of
the code below is the usual code for doing the SELECT of the data (from the Employees table of the
Northwind database) and binding it to the grid. The last third, the btnInsert_Click event, is where
we perform the INSERT of the new data from the two textboxes into the database table. We then
immediately refresh the datagrid so that the new data is displayed.
Imports System
Imports System.Data
Imports System.Data.SqlClient
Imports System.Configuration
Namespace DotNetJohn
Public Class DataGridAdd : Inherits System.Web.UI.Page
Protected dtgEmp As System.Web.UI.WebControls.DataGrid
Protected txtLastName As System.Web.UI.WebControls.TextBox
Protected txtFirstName As System.Web.UI.WebControls.TextBox
Dim objConn As SqlConnection
Dim dataAdapter As SqlDataAdapter
Dim objCmd As SqlCommand
Dim ds As DataSet
Dim strSql As String
Sub Page_Load(Sender As Object, e As EventArgs)
objConn = New SqlConnection(ConfigurationSettings.AppSettings("connectionString"))
strSQL = "SELECT EmployeeID, LastName, FirstName FROM Employees"
dataAdapter = New SqlDataAdapter(strSQL, objConn)
If Not Page.IsPostBack Then
FillTheGrid()
End If
End Sub
Sub FillTheGrid()
ds = New DataSet()
dataAdapter.Fill(ds)
dtgEmp.DataSource = ds.Tables(0).DefaultView
dtgEmp.DataBind()
End Sub
Sub btnInsert_Click(Sender As Object, e As EventArgs)
If Page.IsValid Then
Dim strLastName As String
Dim strFirstName As String
objConn = New SqlConnection(ConfigurationSettings.AppSettings("connectionString"))
objConn.Open()
strLastName = txtLastName.Text.Trim()
strFirstName = txtFirstName.Text.Trim()
strSql = "INSERT INTO Employees (LastName, FirstName) " _
& "VALUES ('" & strLastName & "', '" & strFirstName & "')"
objCmd = New SqlCommand(strSql, objConn)
objCmd.ExecuteNonQuery()
objConn.Close()
FillTheGrid()
End If
End Sub
End Class
End Namespace
Although this method of adding data using the datagrid may not be as "sexy" as doing it within
the grid itself, it gets the job done with a minimum of fuss and, in my opionion, looks like it
belongs to the grid.
You may run the program here.
You may download the code here.
|