Save All GridView PageIndex Rows showing Duplicate rows in Database

Imoleayo Emmanuel 20 Reputation points
2024-06-18T15:39:35.17+00:00

20240618_160733

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++; 

                            } 


                            

                        } 



                    } 



                } 

            } 


             

        } 




         



    }
ASP.NET
ASP.NET
A set of technologies in the .NET Framework for building web applications and XML web services.
3,405 questions
C#
C#
An object-oriented and type-safe programming language that has its roots in the C family of languages and includes support for component-oriented programming.
10,616 questions
{count} votes

1 answer

Sort by: Most helpful
  1. Albert Kallal 5,231 Reputation points
    2024-06-18T18:59:30.0266667+00:00

    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:

    gvtextbox

    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.