The Question:
I have bound data to a DataGrid, which has ID, name, address columns. But
I want the key field (for example ID) as a href so that by clicking on the ID,
I can take the user to another page where I can let him edit that record.
The Answer:
This is a great question. The best part about it is that the ASP.NET DataGrid
server control enables editing of records in a much easier fashion. In classic
ASP you would typically provide an edit link for the user and redirect them
to a page with the ID value in the QueryString. The new page would grab the
ID value, use it to get the record to edit, and then you would build all
kinds of complicated logic to display the RecordSet values in an editable
fashion. With the ASP.NET DataGrid, record editing has been made much easier.
In this tutorial I will show you how to edit records in the DataGrid without
having to redirect to another page.
[ Run Sample ]
The Set-Up
The first this you will need is a database table for the personal information.
I am going to use SQL Server 7.0, but you can use any database you would like.
If you are not using SQL Server 7.0 or better, replace all of the SQL Managed
Provider objects in this tutorial with OleDb Managed Provider objects (i.e.
SqlCommand = OleDbCommand).
Name the table "Person" and create a Primary Key/Identity column
named "ID". Add a VarChar (100) column named "Name" and
a VarChar (200) column named "Address". Once you have set up the
table, add some people to the table so you have some data to work with. The
table should look like this:
Next you need the ASP.NET page to display the output. Add a basic DataGrid
and code to retrieve all of the fields and values from the Person
table. The code should look like this:
<%@ Page Language="VB" %>
<%@ Import Namespace="System.Data" %>
<%@ Import Namespace="System.Data.SqlClient" %>
<html>
<head>
<script runat="server">
Public Sub Page_Load(Source As Object, E As EventArgs)
If Not Page.IsPostBack Then
BindData()
End If
End Sub
Public Sub BindData()
Dim myDataSet As New DataSet
Dim mySqlDataAdapter As SqlDataAdapter
mySqlDataAdapter = New SqlDataAdapter( _
"SELECT * FROM Person", _
"server=localhost;database=dotnetjunkies_samples;uid=codeju;pwd=junkiema;")
mySqlDataAdapter.Fill(myDataSet, "Person")
personInfo.DataSource = myDataSet.Tables("Person")
personInfo.DataBind()
End Sub
</script>
</head>
<body>
<form runat="server" method="post">
<H3>Editing Records in the DataGrid</H3>
<asp:DataGrid
id="personInfo" runat="server" />
</form>
</body>
</html> |
The resulting page will look like this:
Since this is a tutorial on editing in the DataGrid I'm not going to dress
it up any. I'll leave the visual esthetics to you.
Get It On
To make the DataGrid editable you need to build a series of BoundColumns
in the the DataGrid's Columns property. You
need to add an EditCommandColumn, and a BoundColumn
for each field you want to display.
Here is the new DataGrid. Replace the existing DataGrid with this code:
<asp:DataGrid id="personInfo"
runat="server"
AutoGenerateColumns="False">
<Columns>
<asp:EditCommandColumn
ButtonType="LinkButton"
CancelText="Cancel"
EditText="Edit"
UpdateText="Update" />
<asp:BoundColumn
DataField="ID"
HeaderText="ID"
ReadOnly="True" />
<asp:BoundColumn
DataField="Name"
HeaderText="Name" />
<asp:BoundColumn
DataField="Address"
HeaderText="Address" />
</Columns>
</asp:DataGrid> |
In the new DataGrid you added an EditCommandColumn.
This column type adds a column to the DataGrid the displays the EditText
value for each record when the form is loaded. Currently the Edit link will
not do anything. You have to manually write code for the OnEditCommand
event handler and specify the method in the OnEditCommand
property of the DataGrid. You set the ID
BoundColumn to ReadOnly="True".
This is because you do not want the user to edit the ID value for the record.
Remember, in the database the ID column is
the Primary Key and uses an Identity seed to auto increment. You wouldn't
want a user messing with your Primary Keys. None of the other fields have
a ReadOnly property set. The default value
is "False", thus enabling editing on these fields.
To create the editing ability on the DataGrid first you need to write the event
handler. The OnEditCommand event handler
will capture the index value of the item being edited, then call the BindData()
method. When the DataGrid is rebound, the item called will be converted to
an editable row using the DataGrid.EditItemIndex.
Public Sub DataGrid_Edit(Source As Object,
_
E
As DataGridCommandEventArgs)
personInfo.EditItemIndex = E.Item.ItemIndex
BindData()
End Sub |
Next you need to set the OnEditCommand property
of the DataGrid to call the event handler.
<asp:DataGrid id="personInfo" runat="server"
AutoGenerateColumns="False"
OnEditCommand="DataGrid_Edit"> |
Now when you click on "Edit" for a row, the DataGrid is rebound with
TextBoxes in the Name and Address
columns, and the appropriate values in them. You will notice that links for
"Update" and "Cancel" replaced the "Edit" link
for the row you are editing
Right now the "Update" and "Cancel" links will not do anything.
As with the "Edit" link, you need to write event handlers for these
links, and set the DataGrid's OnUpdateCommand
and OnCancelCommand properties. I'm going
to do the OnCancelCommand event handler first
because it is the easiest. You simply need to reset the EditItemIndex
to -1. This denotes that no records are being edited. After setting the EditItemIndex
to -1, you need to call BindData() again
to rebind the DataGrid.
Public Sub DataGrid_Cancel(Source As
Object, _
E
As DataGridCommandEventArgs)
personInfo.EditItemIndex = -1
BindData()
End Sub |
Of course you need to set the OnCancelCommand
property of the DataGrid.
<Asp:DataGrid id="personInfo"
runat="server"
AutoGenerateColumns="False"
OnEditCommand="DataGrid_Edit"
OnCancelCommand="DataGrid_Cancel" > |
Now when you click on "Cancel" the DataGrid is rebound and no rows
are set to be edited.
Break It Down
OK, now for the fun part. The OnUpdateCommand
event handler. In this event handler you will create all of the update functionality.
Basically you will create a SqlCommand to update the record in the database,
set the DataGrid's EditItemIndex to -1, then
call the BindData() method to refresh the form.
The DataGrid creates TextBox controls in each edit cell of the rendered table.
The values you want to send to the database are the values of the Text
property of each of these TextBoxes. The only exception to that is the ID
cell. Since you set it to ReadOnly="True"
a TextBox is not created for the ID cell.
Rather, you can use the Text property of
the cell itself. To make the DataGrid_Update()
method you will declare variables for each dynamically created TextBox as
TextBox datatypes. You will need to instantiate them as the specific TextBox
you need. This is accomplished using the DataGridCommandEventArgs
similar to how you captured the Item.ItemIndex
property. To grab the TextBox you need to specify the cell index in the Item.Cells
collection and the control index in the cell's Controls
collection. The dynamically created TextBox is always uses the 0 index value
in a BoundColumn. The Item.Cells
collection is 0-based. The EditCommandColumn
is index=0, the ID column is index=1, the
Name column is index=2, and the Address
column is index=3.
Public Sub DataGrid_Update(Source As Object,
_
E As DataGridCommandEventArgs)
Dim myConnection As SqlConnection
Dim myCommand As SqlCommand
Dim txtName As TextBox = E.Item.Cells(2).Controls(0)
Dim txtAddress As TextBox = E.Item.Cells(3).Controls(0)
Dim strUpdateStmt As String
strUpdateStmt = "UPDATE Person SET " & _
"Name = '" & txtName.Text & "',
" & _
"Address = '" & txtAddress.Text
& "' " & _
"WHERE ID = " & E.Item.Cells(1).Text
myConnection = New SqlConnection( _
"server=localhost;database=myDB;uid=sa;pwd=;")
myCommand = New SqlCommand(strUpdateStmt, myConnection)
myConnection.Open()
myCommand.ExecuteNonQuery()
personInfo.EditItemIndex = -1
BindData()
End Sub |
In the preceding code you build the SQL UPDATE
statement dynamically using the values in the DataGridCommandEventArgs.
You create a SqlConnection to your database and a SqlCommand, passing it the
UPDATE statement and the SqlConnection object.
You open the connection and execute the command. Lastly you set the DataGrid's
EditItemCommand to -1 (to set no item as editable)
and call BindData().
The last thing to do is wire-up the DataGrid's OnUpdateCommand
property to the DataGrid_Update() event handler.
<Asp:DataGrid id="personInfo"
runat="server"
AutoGenerateColumns="False"
OnEditCommand="DataGrid_Edit"
OnCancelCommand="DataGrid_Cancel"
OnUpdateCommand="DataGrid_Update"> |
[ Run Sample ]
Summary
That's it! No extra page to call, no QueryString to deal with. It really is
as simple as that. If you are feeling creative, you can use a TemplateColumn
and create a more visually appealing layout. The TemplateColumn
has templates for ItemTemplate and EditItemTemplate.
Here is an example of a page I built using the TemplateColumn
in the DataGrid:
