Often it will be desirable to update data fields in an existing database. This section shows how to do this.
In this example, when the page is presented, there is an additional column on the left side of the DataGrid display. It has an "edit" link that, when clicked, causes all the fields in that row to become text boxes showing the existing data. These fields can now be edited to contain the new information. When ready, the user can click the "update" link and the data will be modified in the database.
To update existing data
<%@ Import Namespace="System.Data" %> <%@ Import Namespace="System.Data.SQL" %> <%@ Import Namespace="System.Text"%>
Within the <script language="C#" runat="server"> tag, this example implements five functions: Page_Load, MyDataGridEdit, MyDataGridCancel, MyDataGridUpdate, and BindGrid. These functions are described in more detail in the steps that follow.
<script language="C#" runat="server"> SQLConnection myConnection;
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_id = @Id, 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", SQLDataType.VarChar, 11)); myCommand.Parameters.Add(new SQLParameter("@LName", SQLDataType.VarChar, 40)); myCommand.Parameters.Add(new SQLParameter("@FName", SQLDataType.VarChar, 20)); myCommand.Parameters.Add(new SQLParameter("@Phone", SQLDataType.Char, 12)); myCommand.Parameters.Add(new SQLParameter("@Address", SQLDataType.VarChar, 40)); myCommand.Parameters.Add(new SQLParameter("@City", SQLDataType.VarChar, 20)); myCommand.Parameters.Add(new SQLParameter("@State", SQLDataType.Char, 2)); myCommand.Parameters.Add(new SQLParameter("@Zip", SQLDataType.Char, 5)); myCommand.Parameters.Add(new SQLParameter("@Contract", SQLDataType.VarChar,1)); String[] cols = {"@Id","@LName","@FName","@Phone","@Address", "@City","@State","@Zip","@Contract"};
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++) { String colvalue = ((TextBox)E.Item.Cells[i].Controls[0]).Text; if (i<6 && colvalue == "") { Message.InnerHtml = "ERROR: Null values not allowed for Author ID, Name or Phone"; Message.Style["color"] = "red"; return; } myCommand.Parameters[cols[i-1]].Value = colvalue; }
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.ActiveConnection.Open();
try { myCommand.Execute(ref rowsAffected); Message.InnerHtml = "<b>Record Updated</b><br>" + updateCmd.ToString(); MyDataGrid.EditItemIndex = -1; } catch (SQLException e) { if (e.Number == 2627) Message.InnerHtml = "ERROR: A record already exists with the same primary key"; else Message.InnerHtml = "ERROR: Could not update record, please ensure the fields are correctly filled out"; Message.Style["color"] = "red"; }
myCommand.ActiveConnection.Close();
BindGrid(); }
<body style="font: 10pt verdana"> <form runat="server"> <h3><font face="Verdana">Updating a Row of Data</font></h3> <span id="Message" MaintainState="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" > <property name="Columns"> <asp:EditCommandColumn EditText="Edit" CancelText="Cancel" UpdateText="Update" ItemStyle-Wrap="false"/> </property> </ASP:DataGrid> </form> </body>
To see this example run, go to the ASP+ Quick Start and run sample DataGrid6.aspx.