Hello everyone,
What I have currently done is to create a datatable containing rows and store it into session, load it again at a "Add" button click event and store user input in it, and then bind it to a gridview.
When I click the "Add" button, I insert it into database immediately, and capture the ID which is in this case FoodID by using executescalar(), store it in a "visible = false" label and use it as part of the datatable.
The Gridview contains a "Select" and "Delete" link buttons, where "Select" highlights the option and "Delete" deletes the record in the database using the FoodID as the identifier. My gridview retains its data upon page refresh.
I can successfully delete the record in the database, but have problems deleting the row itself on the application side. Below are my codes. I hope someone can help, and thanks in advance.
protected void Page_Load(object sender, EventArgs e)
{
DataTable dtToGrid = new DataTable();
dtToGrid.Columns.Add("Date", typeof(string));
dtToGrid.Columns.Add("MealType", typeof(string));
dtToGrid.Columns.Add("Food&Drinks", typeof(string));
dtToGrid.Columns.Add("WhereDidYouGetYourMeal?", typeof(string));
dtToGrid.Columns.Add("Address", typeof(string));
dtToGrid.Columns.Add("FoodID", typeof(string));
Session["dtToGrid"] = dtToGrid;
}
protected void btnAdd2_Click(object sender, EventArgs e)
{
FoodExposure f1 = new FoodExposure();
f1.Day = Select1.Value.ToString();
f1.MealType = ddlMealtype1.Value.ToString();
f1.FoodAndDrink = tbxFood1.Value.ToString();
f1.MealPreparedFrom = ddlWhere1.Value.ToString();
f1.LocationOfMeal = inputTextAddress.Value.ToString();
int FoodID = DBManager.InsertFoodExposure(f1);
lblID.Text = FoodID.ToString();
lblID.Visible = false;
DataTable dtToGrid = (DataTable)Session["dtToGrid"];
DataRow drToGrid = dtToGrid.NewRow();
drToGrid["Date"] = Select1.Value.ToString();
drToGrid["MealType"] = ddlMealtype1.Value.ToString();
drToGrid["Food&Drinks"] = tbxFood1.Value.ToString();
drToGrid["WhereDidYouGetYourMeal?"] = ddlWhere1.Value.ToString();
drToGrid["Address"] = inputTextAddress.Value.ToString();
drToGrid["FoodID"] = lblID.Text.ToString();
dtToGrid.Rows.Add(drToGrid);
GridView1.Visible = true;
GridView1.DataSource = dtToGrid;
GridView1.DataBind();
if (ViewState["CurrentData"] != null)
{
DataTable dt = (DataTable)ViewState["CurrentData"];
int count = dt.Rows.Count;
BindGrid(count);
}
else
{
BindGrid(1);
}
Select1.Value = string.Empty;
ddlMealtype1.Value = string.Empty;
tbxFood1.Value = string.Empty;
ddlWhere1.Value = string.Empty;
inputTextAddress.Value = string.Empty;
lblID.Text = string.Empty;
Select1.Focus();
ddlMealtype1.Focus();
tbxFood1.Focus();
ddlWhere1.Focus();
inputTextAddress.Focus();
lblID.Focus();
}
private void BindGrid(int rowcount)
{
DataTable dt = new DataTable();
DataRow dr;
dt.Columns.Add(new System.Data.DataColumn("Date", typeof(String)));
dt.Columns.Add(new System.Data.DataColumn("MealType", typeof(String)));
dt.Columns.Add(new System.Data.DataColumn("Food&Drinks", typeof(String)));
dt.Columns.Add(new System.Data.DataColumn("WhereDidYouGetYourMeal?", typeof(String)));
dt.Columns.Add(new System.Data.DataColumn("Address", typeof(String)));
dt.Columns.Add(new System.Data.DataColumn("FoodID", typeof(String)));
if (ViewState["CurrentData"] != null)
{
for (int i = 0; i < rowcount + 1; i++)
{
dt = (DataTable)ViewState["CurrentData"];
if (dt.Rows.Count > 0)
{
dr = dt.NewRow();
dr[0] = dt.Rows[0][0].ToString();
}
}
dr = dt.NewRow();
dr[0] = Select1.Value.ToString();
dr[1] = ddlMealtype1.Value.ToString();
dr[2] = tbxFood1.Value.ToString();
dr[3] = ddlWhere1.Value.ToString();
dr[4] = inputTextAddress.Value.ToString();
dr[5] = lblID.Text.ToString();
dt.Rows.Add(dr);
}
else
{
dr = dt.NewRow();
dr[0] = Select1.Value.ToString();
dr[1] = ddlMealtype1.Value.ToString();
dr[2] = tbxFood1.Value.ToString();
dr[3] = ddlWhere1.Value.ToString();
dr[4] = inputTextAddress.Value.ToString();
dr[5] = lblID.Text.ToString();
dt.Rows.Add(dr);
}
if (ViewState["CurrentData"] != null)
{
GridView1.DataSource = (DataTable)ViewState["CurrentData"];
GridView1.DataBind();
}
else
{
GridView1.DataSource = dt;
GridView1.DataBind();
}
ViewState["CurrentData"] = dt;
}
protected void GridView1_RowDeleting(object sender, GridViewDeleteEventArgs e)
{
int rowsdeleted = 0;
SqlConnection conn = null;
try
{
conn = new SqlConnection();
conn.ConnectionString = ConfigurationManager.ConnectionStrings["NDBConnectionString"].ConnectionString;
conn.Open();
SqlCommand comm = new SqlCommand();
comm.Connection = conn;
comm.CommandText = "Delete from FoodExposure where FoodID=@FoodID";
comm.Parameters.AddWithValue("@FoodID", Convert.ToInt32(GridView1.SelectedRow.Cells[7].Text));
rowsdeleted = comm.ExecuteNonQuery();
conn.Close();
}
catch (SqlException es)
{
throw es;
}
}