Gridview RowUpdating event using Stored Procedures not updating actual database?

Question

I have a Gridview where I am trying to edit my data by clicking on an edit button in the Gridview and raising the RowUpdating event handler.

When I click update in Gridview row, I receive no errors but the data in the Gridview remains the same as before I clicked edit/update as well as the data in the database

I have checked to see if my Stored Procedure actually works.
I have used breakpoints and all the data is correct.
I have done the same methods/procedures for deleting the row from the gridview and database and they have worked.
I have checked if (!IsPostback)

Here's my Stored Procedure:

CREATE PROCEDURE [dbo].UpdateReview
    @theID int,
    @theDate datetime,
    @theReview varchar(max),
    @theFood int, 
    @theService int, 
    @theAtmos int, 
    @thePrice int
AS
    SET @theDate = GETDATE();

BEGIN
    UPDATE Reviews 
    SET 
        ReviewDate = @theDate,
        ReviewText = @theReview, 
        FoodQuality = @theFood, 
        ServiceRating = @theService, 
        AtmosphereRating = @theAtmos, 
        PriceRating = @thePrice
    WHERE ReviewID = @theID
END

Here's my Method for executing stored procedure:

public void UpdateReview(int id, string review, int food, int service, int atmos, int price)
        {
            DBConnect objDB = new DBConnect();
            objCmd.Parameters.Clear();
            objCmd.CommandType = CommandType.StoredProcedure;
            objCmd.CommandText = "UpdateReview";

            objCmd.Parameters.AddWithValue("@theID", id);
            objCmd.Parameters.AddWithValue("@theReview", review);
            objCmd.Parameters.AddWithValue("@theFood", food);
            objCmd.Parameters.AddWithValue("@theService", service);
            objCmd.Parameters.AddWithValue("@theAtmos", atmos);
            objCmd.Parameters.AddWithValue("@thePrice", price);

            objDB.GetConnection();
            objDB.DoUpdateUsingCmdObj(objCmd);
            objDB.CloseConnection();
        }

I am using my own Connection class: GitHub

Here's my gridview:

<asp:GridView ID="gvMyReviews" runat="server" AutoGenerateColumns="false" OnRowEditing="gvMyReviews_RowEditing" OnRowUpdating="gvMyReviews_RowUpdating" OnRowCancelingEdit="gvMyReviews_RowCancelingEdit" OnRowDeleting="gvMyReviews_RowDeleting" >
            <Columns>
                <asp:BoundField DataField="ReviewID" HeaderText="ID" ReadOnly="true" />
                <asp:BoundField DataField="UserID" HeaderText="User ID" ReadOnly="true" />
                <asp:BoundField DataField="RestName" HeaderText="Restaurant" ReadOnly="true" />
                <asp:BoundField DataField="ReviewDate" HeaderText="Date of Review" DataFormatString="{0:d}" ReadOnly="true" />
                <asp:BoundField DataField="FoodQuality" HeaderText="Food Quality" />
                <asp:BoundField DataField="ServiceRating" HeaderText="Service" />
                <asp:BoundField DataField="AtmosphereRating" HeaderText="Atmosphere" />
                <asp:BoundField DataField="PriceRating" HeaderText="Price" />
                <asp:BoundField DataField="ReviewText" HeaderText="Review" />
                <asp:CommandField HeaderText="Modify" ShowEditButton="true" ControlStyle-CssClass="button2" />
                <asp:CommandField HeaderText="Remove" ShowDeleteButton="true" ControlStyle-CssClass="button2" />
            </Columns>
        </asp:GridView>

Here's my actual code:

protected void gvMyReviews_RowEditing(object sender, GridViewEditEventArgs e)
        {
            gvMyReviews.EditIndex = e.NewEditIndex;
            DataSet ds = p.GetReviewsByUserID(200);
            gvMyReviews.DataSource = ds;
            gvMyReviews.DataBind();
        }

        protected void gvMyReviews_RowUpdating(object sender, GridViewUpdateEventArgs e)
        {
            int index = e.RowIndex;
            int reviewID = int.Parse(gvMyReviews.Rows[index].Cells[0].Text);

            TextBox txtFoodRating = (TextBox)gvMyReviews.Rows[index].Cells[4].Controls[0];
            int foodRating = int.Parse(txtFoodRating.Text);

            TextBox txtServiceRating = (TextBox)gvMyReviews.Rows[index].Cells[5].Controls[0];
            int serviceRating = int.Parse(txtServiceRating.Text);

            TextBox txtAtmosphereRating = (TextBox)gvMyReviews.Rows[index].Cells[6].Controls[0];
            int atmosphereRating = int.Parse(txtAtmosphereRating.Text);

            TextBox txtPriceRating = (TextBox)gvMyReviews.Rows[index].Cells[7].Controls[0];
            int priceRating = int.Parse(txtPriceRating.Text);

            TextBox txtReview = (TextBox)gvMyReviews.Rows[index].Cells[8].Controls[0];
            string strReview = txtReview.Text;

            p.UpdateReview(reviewID, strReview, foodRating, serviceRating, atmosphereRating, priceRating);

            gvMyReviews.EditIndex = -1;

            DataSet ds = p.GetReviewsByUserID(200);
            gvMyReviews.DataSource = ds;
            gvMyReviews.DataBind();
        }

        protected void gvMyReviews_RowCancelingEdit(object sender, GridViewCancelEditEventArgs e)
        {
            gvMyReviews.EditIndex = -1;
            DataSet ds = p.GetReviewsByUserID(200);
            gvMyReviews.DataSource = ds;
            gvMyReviews.DataBind();
        }

check postback control when first load grid data.. probably pageload — levent, Mar 20 '19 at 06:38
`DbConnect.DoUpdateUsingCmdObj` ignores the error and turns -1. you should check DoUpdateUsingCmdObj result. — levent, Mar 20 '19 at 07:19
You don't mention running a SQL Profiler trace. Turn one on and see what SQL Command is being sent by your app (and where it's being sent to) when you do the update. — Tab Alleman, Mar 20 '19 at 13:46
@TabAlleman I get a return -1 which means it isnt working but i cant find the reason why. — pyuntae, Mar 21 '19 at 00:58
@levent I get a return -1 which means it isnt working but i cant find the reason why. — pyuntae, Mar 21 '19 at 00:58
change `return -1;` to `throw ex;` in `DbConnect.DoUpdateUsingCmdObj` catch block. — levent, Mar 21 '19 at 12:46
You still haven't run a profiler trace. Are you unwilling or unable to do so for some reason? Without doing that, I can't offer any suggestions. — Tab Alleman, Mar 21 '19 at 13:06

score 0 · Answer 1 · answered Mar 21 '19 at 13:00

even if you are setting the @theDate value in sp, you need to add @theDate parameter via command. otherwise you should change sp to like below..

CREATE PROCEDURE [dbo].UpdateReview
    @theID int,
    @theReview varchar(max),
    @theFood int, 
    @theService int, 
    @theAtmos int, 
    @thePrice int
AS
BEGIN
    UPDATE Reviews 
    SET 
        ReviewDate = GETDATE(),
        ReviewText = @theReview, 
        FoodQuality = @theFood, 
        ServiceRating = @theService, 
        AtmosphereRating = @theAtmos, 
        PriceRating = @thePrice
    WHERE ReviewID = @theID
END

Gridview RowUpdating event using Stored Procedures not updating actual database?

1 Answers1