How to insert more records in database if gridview have more data asp.net c#

Question

User696604810 posted

Problem

I cannot insert data to database from Grid View if Grid View Have more Records .

 <div>
    <asp:GridView ID="GridItems" runat="server" AutoGenerateColumns="false" OnRowDataBound="GridItems_RowDataBound" OnRowEditing="OnRowEditing" OnRowCancelingEdit="OnRowCancelingEdit" >
        <Columns>
            <asp:BoundField DataField="RowNumber" HeaderText="Row Number" />
            <asp:TemplateField HeaderText="ItemCode">
                <ItemTemplate>
                    <asp:TextBox ID="txtItemCode" runat="server"></asp:TextBox>
                </ItemTemplate>
            </asp:TemplateField>
            <asp:TemplateField HeaderText="Quantity">
                <ItemTemplate>
                    <asp:TextBox ID="TxtQuantity" runat="server"></asp:TextBox>
                </ItemTemplate>
            </asp:TemplateField>
            <asp:TemplateField HeaderText="Price">
                <ItemTemplate>
                    <asp:TextBox ID="TxtPrice" runat="server" Text="0"   OnTextChanged="TxtPrice_TextChanged" AutoPostBack="true" ></asp:TextBox>
                </ItemTemplate>
                  <FooterTemplate>
                            <asp:TextBox ID="TxtPrice" AutoPostBack="true" OnTextChanged="TxtPrice_TextChanged" runat="server"></asp:TextBox>
                        </FooterTemplate>
            </asp:TemplateField>
            <asp:TemplateField HeaderText="Amount" >
                <ItemTemplate>
                    <asp:TextBox ID="TxtAmount" runat="server">
                    </asp:TextBox>
                </ItemTemplate>
               
            </asp:TemplateField>
        </Columns>
    </asp:GridView>
    <br />
    <div style="display: none;">
        <asp:Button ID="ButtonAdd" runat="server" OnClick="ButtonAdd_Click" Text="Add New Row" />
    </div>

private void SetInitialRow()

        {

            DataTable dt = new DataTable();

            DataRow dr = null;

            dt.Columns.Add(new DataColumn("RowNumber", typeof(string)));

            dt.Columns.Add(new DataColumn("ItemCode", typeof(string)));

            dt.Columns.Add(new DataColumn("Quantity", typeof(string)));

            dt.Columns.Add(new DataColumn("Price", typeof(string)));
            dt.Columns.Add(new DataColumn("Amount", typeof(string)));

            dr = dt.NewRow();

            dr["RowNumber"] = 1;

            dr["ItemCode"] = string.Empty;

            dr["Quantity"] = string.Empty;

            dr["Price"] = string.Empty;
            dr["Amount"] = string.Empty;

            dt.Rows.Add(dr);

            //dr = dt.NewRow();



            //Store the DataTable in ViewState

            ViewState["CurrentTable"] = dt;



            GridItems.DataSource = dt;

            GridItems.DataBind();

        }

How to make insert to data in grid view to database  ?

Answer 1

User-893317190 posted

Hi  ahmedbarbary,

You could use footerTemplate and add a button to insert data.

Below is my code.

 <form id="form1" runat="server">
        <div>
            <asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" DataKeyNames="SID" DataSourceID="SqlDataSource1"  ShowFooter="true"  >
                <Columns>
                    <asp:BoundField DataField="SID" HeaderText="SID" InsertVisible="False" ReadOnly="True" SortExpression="SID" />
                   
                    <asp:TemplateField HeaderText="Name">
                        <ItemTemplate>
                            <asp:Label ID="Label1" runat="server" Text='<%# Eval("Name") %>'></asp:Label>
                        </ItemTemplate>
                        <EditItemTemplate>
                            <asp:TextBox ID="TextBox1" runat="server" Text='<%# Bind("Name") %>' ></asp:TextBox>
                        </EditItemTemplate>
                        <FooterTemplate>
                            <asp:TextBox ID="TextBox1" runat="server" ></asp:TextBox>
                        </FooterTemplate>
                    </asp:TemplateField>
               
                    <asp:TemplateField HeaderText="City">
                        <ItemTemplate>
                             <asp:Label ID="Label2" runat="server" Text='<%# Eval("City") %>'></asp:Label>
                        </ItemTemplate>
                        <EditItemTemplate>
                              <asp:TextBox ID="TextBox2" runat="server" Text='<%# Bind("city") %>' ></asp:TextBox>
                        </EditItemTemplate>
                        <FooterTemplate>
                            <asp:TextBox ID="TextBox2" runat="server" ></asp:TextBox>
                        </FooterTemplate>
                    </asp:TemplateField>
                    <asp:TemplateField>
                       <ItemTemplate>
                           <asp:Button ID="Button1" runat="server" CommandName="Edit" Text="edit" />
                           <asp:Button ID="Button2" runat="server" CommandName="Delete" Text="delete" />
                       </ItemTemplate>
                        <EditItemTemplate>
                             <asp:Button ID="Button1" runat="server" CommandName="update" Text="update" />
                           <asp:Button ID="Button2" runat="server" CommandName="cancel" Text="cancel" />
                        </EditItemTemplate>
                        <FooterTemplate>
                             <asp:Button ID="Button1" runat="server"  Text="insert" OnClick="Button1_Click" />
                        </FooterTemplate>
                    </asp:TemplateField>
                </Columns>


            </asp:GridView>
            <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:WebFormCases.Models.supplyModelConnectionString %>" SelectCommand="SELECT [SID], [Name], [City] FROM [Suppliers]" DeleteCommand="DELETE FROM [Suppliers] WHERE [SID] = @SID" InsertCommand="INSERT INTO [Suppliers] ([Name], [City]) VALUES (@Name, @City)" UpdateCommand="UPDATE [Suppliers] SET [Name] = @Name, [City] = @City WHERE [SID] = @SID">
                <DeleteParameters>
                    <asp:Parameter Name="SID" Type="Int32" />
                </DeleteParameters>
                <InsertParameters>
                    <asp:Parameter Name="Name" Type="String" />
                    <asp:Parameter Name="City" Type="String" />
                </InsertParameters>
                <UpdateParameters>
                    <asp:Parameter Name="Name" Type="String" />
                    <asp:Parameter Name="City" Type="String" />
                    <asp:Parameter Name="SID" Type="Int32" />
                </UpdateParameters>
            </asp:SqlDataSource>
        </div>
    </form>

Code behind.

    private static string constr = ConfigurationManager.ConnectionStrings["supplyModel"].ConnectionString;
        protected void Button1_Click(object sender, EventArgs e)
        {
            string sql = "insert into suppliers values(@name,@city)";
            Button addButton = (Button)sender;
         TextBox box1=   addButton.NamingContainer.FindControl("TextBox1") as TextBox;
          TextBox box2=  addButton.NamingContainer.FindControl("TextBox2") as TextBox;
            using (SqlConnection con = new SqlConnection(constr))
            {
                using (SqlCommand com = new SqlCommand(sql, con))
                {
                    com.Parameters.AddWithValue("name",box1.Text);
                    com.Parameters.AddWithValue("city", box2.Text);
                    con.Open();
                   com.ExecuteNonQuery();
                }
            }
            GridView1.DataBind();

        }

The result.

Best regards,

Ackerly Xu

Answer 2

User-1716253493 posted

Seem like you want to insert data by looping gridview rows.

You cant insert same parameters twice, clear the parameters first before adding parameters

cmd.Parameters.Clear();
cmd.Parameters.Add(...);
cmd.Parameters.Add(...);