Delete any existing code in the .aspx page, and then add
the following code to the Web form:
<%@ 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=(local);database=pubs;UID=USER;PWD=PASSWORD");
if (!IsPostBack)
BindGrid();
}
public void MyDataGrid_Edit(Object sender, DataGridCommandEventArgs e)
{
MyDataGrid.EditItemIndex = (int)e.Item.ItemIndex;
BindGrid();
for (int i = 0; i < e.Item.Cells.Count; i++)
{
e.Item.Cells[i].Text = Server.HtmlDecode(e.Item.Cells[i].Text);
Trace.Write(e.Item.Cells[i].Text);
}
}
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", 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 column
{
String colvalue =((TextBox)e.Item.Cells[i].Controls[0]).Text;
// Look for null values in required fields.
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 = Server.HtmlEncode(colvalue);
}
// Append last row, converting true/false values 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();
MyDataGrid.EditItemIndex = -1;
}
catch (SqlException exc)
{
if (exc.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.Connection.Close();
BindGrid();
}
private void MyDataGrid_ItemDataBound(object sender, System.Web.UI.WebControls.DataGridItemEventArgs e)
{
if (e.Item.ItemType == ListItemType.EditItem)
{
for (int i = 0; i < e.Item.Controls.Count; i++)
{
if (e.Item.Controls[i].Controls[0].GetType().ToString() == "System.Web.UI.WebControls.TextBox")
{
TextBox tb = (TextBox)e.Item.Controls[i].Controls[0];
tb.Text = Server.HtmlDecode(tb.Text);
}
}
}
}
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: 10pt verdana">
<form runat="server" ID="Form1">
<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" OnItemDataBound="MyDataGrid_ItemDataBound">
<Columns>
<asp:EditCommandColumn EditText="Edit" CancelText="Cancel" UpdateText="Update" ItemStyle-Wrap="false" />
</Columns>
</ASP:DataGrid>
</p>
</span>
</form>
</body>
</html>
|