Save All GridView PageIndex Rows showing Duplicate rows in Database

Question

Please help. How to Browse all 3 GridView Pages above and INSERT all rows into DB on Save Button click. Using below code is showing Duplicate row on last gridview page which is having only one record in its page 3. Thanks

    public void SaveButton(int @classid, int @examid, int @subjid) { 


        

         int a = GridView1.PageIndex; 

         int parent; 

         int i; 

         int j; 



        //Loop through All Pages 

        for (parent = 0; parent < GridView1.PageCount; parent++) 

        { 

            //Set Page Index 

            GridView1.SetPageIndex(parent); 



            //After Setting Page Index Loop through its Rows 



            foreach (GridViewRow gvrow in GridView1.Rows) 

            { 

                using (SqlConnection con = new SqlConnection(SqlconString)) 

                { 



                    using (SqlCommand cmd = new SqlCommand("sp_insertEntry", con)) 

                    { 

                        con.Open(); 

                        cmd.CommandType = CommandType.StoredProcedure; 

                        for (i = 0; i <= GridView1.Rows.Count; i++) 

                        { 

                            for (j = 1; j <= GridView1.Rows.Count; j++) 

                            { 

                                //checkbox in 2nd Column Index 

                                var checkbox = GridView1.Rows[i].Cells[2].FindControl("CheckBox1") as CheckBox; 

                                var textboxOfGDV = GridView1.Rows[i].Cells[3].FindControl("txtMarks") as TextBox; 

                                int studentID = Convert.ToInt32(GridView1.Rows[i].Cells[0].Text); 



                                if (string.IsNullOrWhiteSpace(textboxOfGDV.Text)) 

                                { 

                                    textboxOfGDV.Text = "0"; 

                                } 



                                if (checkbox.Checked) 

                                { 

                                    //if checked add PRESENT to MSSQL ATTENDANCE Column 

                                    Session["status"] = "Present"; 

                                } 

                                else 

                                { 

                                    //if checked add PRESENT to MSSQL ATTENDANCE Column 

                                    Session["status"] = "Absent"; 

                                } 

                                cmd.Parameters.AddWithValue("@cid", @classid); 

                                cmd.Parameters.AddWithValue("@eid", @examid); 

                                cmd.Parameters.AddWithValue("@subjid", @subjid); 

                                cmd.Parameters.AddWithValue("@studid", studentID.ToString()); 

                                cmd.Parameters.AddWithValue("@AttendanceCheckBox", Session["status"].ToString()); 

                                cmd.Parameters.AddWithValue("@mark", Convert.ToInt32(textboxOfGDV.Text)); 



                                int result = cmd.ExecuteNonQuery(); 

                                if (j == GridView1.Rows.Count) 

                                { 

                                    parent++; 

                            a=a+1;

                        GridView1.SetPageIndex(a);  

                                } 

                                cmd.Parameters.Clear(); 

                                i++; 

                            } 


                            

                        } 



                    } 



                } 

            } 


             

        } 




         



    }

Answer 1

Keep in mind that if you use a data pager, then only rows in the current page exist in the GridView.

This suggests (means) that if you move to another page, then any values entered into controls on a given row are lost. Hence, for this to work, then when you move to a different page, you AT THAT point have to save the data, and THEN allow the paging to occur.

So, you can achieve the goal of having one final save button, and one cancel button that dumps all edits. However, this will require you to "persist" the GridView data source in memory. So, on any page change? You send the textbox(s) to the data table.

On save, then again, you send textbox(s) to data table, and then execute ONE save operation that will send any edits, deletes, or inserts to the database.

Here is a working proof of concept:

So, we have some hotels, and one text box of comments (that can be changed).

        <asp:GridView ID="GridView1" runat="server"
            AutoGenerateColumns="False" DataKeyNames="ID"
            CssClass="table table-hover" Width="50%"
            AllowPaging="true" PageSize="4"
            OnPageIndexChanging="GridView1_PageIndexChanging">
            <Columns>
                <asp:BoundField DataField="FirstName" HeaderText="FirstName" />
                <asp:BoundField DataField="LastName" HeaderText="LastName" />
                <asp:BoundField DataField="City" HeaderText="City" />
                <asp:BoundField DataField="HotelName" HeaderText="HotelName" />
                <asp:TemplateField HeaderText="Description">
                    <ItemTemplate>
                        <asp:TextBox ID="txtDescription"
                            runat="server" TextMode="MultiLine"
                            Text='<%# Eval("Description") %>'
                            Height="100px" Width="330px">
                        </asp:TextBox>
                    </ItemTemplate>
                </asp:TemplateField>
            </Columns>
            <PagerStyle CssClass="GridPager" />
        </asp:GridView>
        <br />
        <asp:Button ID="cmdSave" runat="server" Text="Save/Done"
            CssClass="btn btn-dark"
            OnClick="cmdSave_Click" />

And now our code behind:

    Dim rstHotels As New DataTable
    Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
        If Not IsPostBack Then
            LoadData()
            Session("rstHotels") = rstHotels
        Else
            rstHotels = Session("rstHotels")
        End If
    End Sub
    Sub LoadData()
        Dim strSQL =
            "SELECT * FROM tblHotelsA
            ORDER BY HotelName"
        rstHotels = MyRst(strSQL)
        GridView1.DataSource = rstHotels
        GridView1.DataBind()
    End Sub
    Sub GridToTable()
        ' send grid rows back to persisted table
        For Each gRow As GridViewRow In GridView1.Rows
            If gRow.RowType = DataControlRowType.DataRow Then
                Dim txtDescript As TextBox = gRow.FindControl("txtDescription")
                Dim OneRow As DataRow = rstHotels.Rows(gRow.DataItemIndex)
                OneRow("Description") = txtDescript.Text
            End If
        Next
    End Sub
    Protected Sub GridView1_PageIndexChanging(sender As Object, e As GridViewPageEventArgs)
        GridToTable()     ' send any changes to persisted table
        GridView1.PageIndex = e.NewPageIndex
        GridView1.DataSource = rstHotels
        GridView1.DataBind()
    End Sub
    Protected Sub cmdSave_Click(sender As Object, e As EventArgs)
        GridToTable()
        Using conn As New SqlConnection(My.Settings.TEST4)
            Using cmdSQL As New SqlCommand("SELECT * FROM tblHotelsA", conn)
                Dim da As New SqlDataAdapter(cmdSQL)
                Dim daU As New SqlCommandBuilder(da)
                conn.Open()
                da.Update(rstHotels)
            End Using
        End Using
        ' at this point all changes saved to database
        ' we no doubt at this point will navagate to another page.
        'Response.Redirect("Some other page.aspx")
    End Sub

Note close in above - we declare the table that the class level - so all routines can freely use that table. And we persist the table data into session.

So, anytime you change the data page, we can send the GV rows back to that table.

And then note close the save button code. We send all edits in one operation back to the database.

So, if you edit, change pages - data is always sent to the persisted data table. But, we still have the option to "cancel" (not save), or we can save.

So, when using a data pager, keep in mind that ONLY the rows that are currently displayed are available. The pages that you are not viewing don't display, and are NOT available for use from code. However, we solve this by persisting the data table into session. While ONLY rows of the GridView in display are available, we have the "magic" .DataItemIndex. So, we do NOT use .DataRow, since that is the displayed row. However, .DataItemIndex is the correct row pointer into the persisted data table, and that's how/why this works.

Hence, result looks like this:

Note how I am able to edit, move to next page, and move back - the values persist, but we not (yet) send the changes back to the database until such time the save/done button is used.