Dear Raif, if you write your code in separate functions(better Encapsulation),you can surely decrease such runtime errors specially for working on databases in ASP.NET pages.
Hope you accept this advice and Have a look at this example.
---------
//C#
<%@ Import namespace="System.Data" %>
<%@ Import namespace="System.Data.SqlClient" %>
<%@ Import namespace="System.Drawing" %>
<html>
<head>
<title>Updating Beverages</title>
</head>
<body>
<form method="post" runat="server">
<asp:Label id="lblError" runat="server" Width="164" /><br/><br/>
<asp:DataGrid id="dgProducts" runat="server"
CellPadding="5" AutoGenerateColumns="False"
OnEditCommand="EditRecord"
OnCancelCommand="CancelEdit"
OnUpdateCommand="UpdateRecord"
OnDeleteCommand="DeleteRecord">
<Columns>
<asp:BoundColumn DataField="ProductID" ReadOnly="True"
Visible="False" />
<asp:BoundColumn DataField="ProductName" ReadOnly="True"
HeaderText="Name" />
<asp:BoundColumn DataField="UnitPrice" HeaderText="Price" />
<asp:EditCommandColumn ButtonType="LinkButton"
UpdateText="Save" CancelText="Cancel"
EditText="Edit" />
<asp:ButtonColumn Text="Delete" CommandName="Delete" />
</Columns>
</asp:DataGrid>
</form>
</body>
</html>
<script language="c#" runat="server">
private String strConnection = "server=(local);database=Northwind;integrated security=true;";
private String strSQLSelect = "SELECT ProductID, ProductName, UnitPrice FROM Products WHERE CategoryID = 1";
private String ProductTableName = "ProductTable";
private SqlConnection objConnection;
private void Page_Load(object sender, System.EventArgs e)
{
if (!IsPostBack)
// {
// String strSQL = "INSERT INTO Products (" + "ProductName, CategoryID, UnitPrice) " + "VALUES ('RolaBolaCola', 1, 15)";
// Connect();
// SqlCommand dbComm = new SqlCommand(strSQL, objConnection);
// dbComm.ExecuteNonQuery();
// Disconnect();
LoadGrid();
// }
}
private void LoadGrid()
{
Connect();
SqlDataAdapter adapter = new SqlDataAdapter(strSQLSelect, objConnection);
DataSet ds = new DataSet();
adapter.Fill(ds, ProductTableName);
Disconnect();
dgProducts.DataSource = ds.Tables[ProductTableName];
dgProducts.DataBind();
}
private void Connect()
{
if (objConnection == null)
objConnection = new SqlConnection(strConnection);
if (objConnection.State == ConnectionState.Closed)
objConnection.Open();
}
private void Disconnect()
{
objConnection.Close();
}
public void EditRecord(object sender, DataGridCommandEventArgs e)
{
dgProducts.EditItemIndex = e.Item.ItemIndex;
LoadGrid();
}
public void CancelEdit(object sender, DataGridCommandEventArgs e)
{
dgProducts.EditItemIndex = -1;
LoadGrid();
}
public void UpdateRecord(object sender, DataGridCommandEventArgs e)
{
// Retrieve the field values in the edited row
int ProductID = Convert.ToInt32(e.Item.Cells[0].Text);
TextBox PriceTextBox = (TextBox)e.Item.Cells[2].Controls[0];
decimal Price = Convert.ToDecimal(PriceTextBox.Text);
if (Price > 0)
{
dgProducts.EditItemIndex = -1;
lblError.Visible = false;
UpdateProduct(ProductID, Price);
}
else
{
LoadGrid();
lblError.Text = "Product price must be greater than 0!";
lblError.Visible = true;
}
// DataSet ds = new DataSet();
// dgProducts.DataSource = ds.Tables[ProductTableName];
// dgProducts.DataBind();
}
private void UpdateProduct(int ProductID, decimal Price)
{
// Create and load a DataSet with records from Northwind.Products table
Connect();
SqlDataAdapter adapter = new SqlDataAdapter(strSQLSelect, objConnection);
DataSet ds = new DataSet();
adapter.Fill(ds, ProductTableName);
Disconnect();
// Modify the in-memory records in the DataSet
DataTable tbl = ds.Tables[ProductTableName];
tbl.PrimaryKey = new DataColumn[] {tbl.Columns["ProductID"]};
DataRow row = tbl.Rows.Find(ProductID);
row["UnitPrice"] = Price;
// Reconnect the DataSet and update the database
SqlCommandBuilder cb = new SqlCommandBuilder(adapter);
Connect();
adapter.Update(ds, ProductTableName);
Disconnect();
}
public void DeleteRecord(object sender, DataGridCommandEventArgs e)
{
// Retrieve the ID of the product to be deleted
int ProductID = Convert.ToInt32(e.Item.Cells[0].Text);
dgProducts.EditItemIndex = -1;
DeleteProduct(ProductID);
// Display the remaining items in the DataGrid
DataSet ds = new DataSet();
dgProducts.DataSource = ds.Tables[ProductTableName];
dgProducts.DataBind();
}
private void DeleteProduct(int ProductID)
{
// Create and load a DataSet with records from Northwind's Products table
Connect();
SqlDataAdapter adapter = new SqlDataAdapter(strSQLSelect, objConnection);
DataSet ds = new DataSet();
adapter.Fill(ds, ProductTableName);
Disconnect();
// Mark the product as Deleted in the DataSet
DataTable tbl = ds.Tables[ProductTableName];
tbl.PrimaryKey = new DataColumn[] {tbl.Columns["ProductID"]};
DataRow row = tbl.Rows.Find(ProductID);
row.Delete();
// Reconnect the DataSet and delete the record from the database
SqlCommandBuilder cb = new SqlCommandBuilder(adapter);
Connect();
adapter.Update(ds, ProductTableName);
Disconnect();
}
</script>
--------------------
--------------------------------------------
Mehdi.
help with submit button problem
