Updating databases may be tricky in Web applications. In this case, the DataGrid Control provides some built-in support to make updates easier. To allow row editing, the DataGrid supports the integer edititemindex attribute, which indicates which row of the grid should be editable. After this attribute is set, the DataGrid displays the rows as text input boxes based on this index, instead of simple labels. Value-1 (default) indicates that no row is editable. Page can contain the DataGrid in the form on the server side, and obtain access to the edited data through the object model of the DataGrid.
To determine which row should be editable, you need to accept the user's input about which row they want to edit. The DataGrid can contain an editcommandcolumn to present links that inspire three special events: editcommand, updatecommand, and cancelcommand. The editcommandcolumn is added to the columns set of the DataGrid as declared, as shown in the following example.
<Asp: DataGrid id = "mydatagrid" runat = "server"
...
Oneditcommand = "mydatagrid_edit"
Oncancelcommand = "mydatagrid_cancel"
Onupdatecommand = "mydatagrid_update"
Datakeyfield = "au_id"
>
<Columns>
<Asp: editcommandcolumn edittext = "edit" canceltext = "cancel"
Updatetext = "Update"/>
</Columns>
</ASP: DataGrid>
On the DataGrid tag itself, connect the event handler to each
Command. The datagridcommandeventargs parameters of these handlers allow you to directly access
The index selected by the client of edititemindex of the DataGrid. Note: You need to re-bind the DataGrid
Make the change take effect, as shown in the following example.
Public void mydatagrid_edit (Object sender, datagridcommandeventargs e ){
Mydatagrid. edititemindex = (INT) E. Item. itemindex;
Bindgrid ();
}
Public sub mydatagrid_edit (sender as object, e as datagridcommandeventargs)
Mydatagrid. edititemindex = E. Item. itemindex
Bindgrid ()
End sub
Public Function mydatagrid_edit (Sender: object, E: datagridcommandeventargs)
: Void {
Mydatagrid. edititemindex = int (E. Item. itemindex );
Bindgrid ();
}
When editing a row of the DataGrid, The editcommandcolumn displays the update and cancel links. If the client selects cancel, you only need to set edititemindex back to-1. However, if the client chooses update, You need to execute the update command on the database. To execute an update query, you must know the primary keys in the database of the row to be updated. To support this requirement, the DataGrid discloses a datakeyfield attribute that can be set as the primary key field name. In the event handler connected to updatecommand, you can retrieve the key name from the datakeys set of the DataGrid. Use the itemindex of the event to index the collection, as shown in the following example.
Mycommand. Parameters ["@ ID"]. value =
Mydatagrid. datakeys [(INT) E. Item. itemindex];
Mycommand. parameters ("@ ID"). value =
Mydatagrid. datakeys (ctype (E. Item. itemindex, integer ))
Mycommand. Parameters ["@ ID"]. value =
Mydatagrid. datakeys [int (E. Item. itemindex)];
At the end of the update event handler, set edititemindex back to-1. The following example shows how to run the code.
<% @ Import namespace = "system. Data" %>
<% @ Import namespace = "system. Data. sqlclient" %>
<HTML>
<Script language = "C #" runat = "server">
Sqlconnection myconnection;
Protected void page_load (Object SRC, eventargs E)
{
Myconnection = new
Sqlconnection ("Server = Jeff; uid = sa; Password =; database = pubs ");
If (! Ispostback)
Bindgrid ();
}
Public void mydatagrid_edit (Object sender, datagridcommandeventargs E)
{
Mydatagrid. edititemindex = (INT) E. Item. itemindex;
Bindgrid ();
}
Public void mydatagrid_cancel (Object sender, datagridcommandeventargs E)
{
Mydatagrid. edititemindex =-1;
Bindgrid ();
}
Public void mydatagrid_update (Object sender, datagridcommandeventargs E)
{
String updatecmd = "Update authors set au_lname = @ lname, au_fname
= @ Fname, phone = @ phone ,"
+ "Address = @ address, city = @ city, state = @ State, zip =
@ Zip, contract = @ Contract Where au_id = @ ID ";
Sqlcommand mycommand = new sqlcommand (updatecmd, myconnection );
Mycommand. Parameters. Add (New sqlparameter ("@ ID", sqldbtype. nvarchar, 11 ));
Mycommand. Parameters. Add (New sqlparameter ("@ lname", sqldbtype. nvarchar, 40 ));
Mycommand. Parameters. Add (New sqlparameter ("@ fname", sqldbtype. nvarchar, 20 ));
Mycommand. Parameters. Add (New sqlparameter ("@ phone", sqldbtype. nchar, 12 ));
Mycommand. Parameters. Add (New sqlparameter ("@ address", sqldbtype. nvarchar, 40 ));
Mycommand. Parameters. Add (New sqlparameter ("@ City", sqldbtype. nvarchar, 20 ));
Mycommand. Parameters. Add (New sqlparameter ("@ State", sqldbtype. nchar, 2 ));
Mycommand. Parameters. Add (New sqlparameter ("@ zip", sqldbtype. nchar, 5 ));
Mycommand. Parameters. Add (New sqlparameter ("@ contract", sqldbtype. nvarchar, 1 ));
Mycommand. Parameters ["@ ID"]. value = mydatagrid. datakeys [(INT) E. Item. itemindex];
String [] Cols = {"@ ID", "@ lname", "@ fname", "@ phone", "@ address", "@ City ", "@ State", "@ zip", "@ contract "};
Int numcols = E. Item. cells. count;
For (INT I = 2; I <numCols-1; I ++) // skip the first, second, and last columns
{
String colvalue = (textbox) E. Item. cells [I]. controls [0]). text;
// Check for null values in the required fields
If (I <6 & colvalue = "")
{
Message. innerhtml = "error:" Author ID "," name ", or" phone number "does not allow null values ";
Message. Style ["color"] = "red ";
Return;
}
Mycommand. Parameters [Cols [I-1]. value = colvalue;
}
// Append the last row and convert the value true/false to 0/1.
If
(String. Compare (textbox) E. Item. cells [numCols-1]. controls [0]). Text, "true", true) = 0)
Mycommand. Parameters ["@ contract"]. value = "1 ";
Else
Mycommand. Parameters ["@ contract"]. value = "0 ";
Mycommand. Connection. open ();
Try
{
Mycommand. executenonquery ();
Message. innerhtml = "<B> updated records </B> <br>" + updatecmd;
Mydatagrid. edititemindex =-1;
}
Catch (sqlexception exc)
{
If (EXC. Number = 2627)
Message. innerhtml = "error: a record with the same primary key already exists ";
Else
Message. innerhtml = "error: failed to update record. please ensure that the field is correct
";
Message. Style ["color"] = "red ";
}
Mycommand. Connection. Close ();
Bindgrid ();
}
Public void bindgrid ()
{
Sqldataadapter mycommand = new sqldataadapter ("select * from authors", myconnection );
Dataset DS = new dataset ();
Mycommand. Fill (DS, "Authors ");
Mydatagrid. datasource = Ds. Tables ["Authors"]. defaultview;
Mydatagrid. databind ();
}
</SCRIPT>
<Body style = "Font: 10.5pt ">
<Form runat = "server" id = "form1">
<H3> <font face = ""> Update data rows </font> <Span id = "message" enableviewstate = "false" style = "Font: Arial 11pt;" runat = "server"/> <p>
<Asp: dataGrid id = "mydatagrid" runat = "server" width = "800" backcolor = "# ccccff" bordercolor = "black" showfooter = "false" cellpadding = "3" cellspacing =" 0 "font-name =" verdana "font-size =" 8pt "headerstyle-backcolor =" # aaaadd "oneditcommand =" mydatagrid_edit "oncancelcommand =" mydatagrid_cancel "onupdatecommand =" mydatagrid_update "datakeyfield = "au_id">
<Columns>
<Asp: editcommandcolumn edittext = "edit" canceltext = "cancel" updatetext = "Update
"Itemstyle-wrap =" false "/>
</Columns>
</ASP: DataGrid>
</Form>
</Body>
</Html>
Start building with 50+ products and up to 12 months usage for Elastic Compute Service
1 on 1 presale consultation
24/7 Technical Support 6 Free Tickets per Quarter Faster Response
Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.
A comprehensive suite of global cloud computing services to power your business
Payment Methods We Support
