DataGrid Learning Five

The DataGrid update database can often be tricky in Web applications. In this case, the DataGrid control provides some built-in support to make the update easier. To allow editing of rows, the DataGrid supports the integer EditItemIndex property, which indicates which row of the grid should be editable. When this property is set, the DataGrid renders the row as a text input box, rather than a simple label, by that index. A value of 1 (the default value) indicates that no rows are editable. A page can include a DataGrid in a server-side form and gain access to edited data through the DataGrid's object model.



To determine which row should be editable, you need a way to accept the user's input about which line they want to edit. The DataGrid can contain a editcommandcolumn to render the links that inspire three special events: EditCommand, UpdateCommand, and CancelCommand. EditCommandColumn is added declaratively to the Columns collection of the DataGrid, 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 fired from the EditCommandColumn. The DataGridCommandEventArgs parameters of these handlers allow you to directly access the index selected by the client that is used to set the DataGrid's EditItemIndex. Note that the DataGrid needs to be rebind for the changes to 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 you edit a row of DataGrid, EditCommandColumn renders the Update and Cancel links. If the client chooses Cancel, simply set the EditItemIndex back to-1. However, if the client chooses update, you need to perform an update command on the database. Perform an update query that requires a primary key in the database of the rows that you want to update. To support this requirement, the DataGrid exposes a DataKeyField property that can be set as the primary key field name. In an event handler connected to UpdateCommand, you can retrieve the key name from the DataKeys collection in the DataGrid. Use the itemindex of the event to index in this 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 the EditItemIndex back to-1. The following example shows the operation of this code.



<%@ Import namespace= "System.Data"%>
<%@ Import namespace= "System.Data.SqlClient"%>
<script language= "C #" runat= "Server" >



SqlConnection myconnection;



protected void Page_Load (Object Src, EventArgs E)
{
Myconnection=new SqlConnection ("server=jeff;uid=sa;password=;d atabase=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 First, second, and last columns
{
String Colvalue = ((TextBox) e.item.cells[i]. Controls[0]). Text;



Check to see if there is a null value in the desired field
if (i<6 && colvalue = "")
{
message.innerhtml = "Error: Author ID", "name" or "phone" do not allow null value ";
message.style["COLOR"] = "red";
Return
}



Mycommand.parameters[cols[i-1]]. Value = Colvalue;
}



Append the last row and convert the True/false value 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 record </b><br>" + updatecmd;
Mydatagrid.edititemindex =-1;
}
catch (SqlException exc)
{
if (exc. Number = 2627)
message.innerhtml = "Error: Record with same primary key already exists";
Else
message.innerhtml = "Error: Failed to update record, make sure fields are filled in correctly";
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 song body" >
<form runat= "Server" id= "Form1" >
<span id= "message" enableviewstate= "false" style= "font:arial 11pt;" runat= "Server"/><p>
<asp:datagrid id= "Mydatagrid" runat= "Server" width= "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>