locked
Insert data from Gridview on click of a button RRS feed

  • Question

  • User1234927972 posted

    I am trying to insert every row from the gridview into a database through the click of a button. The code works with no errors, but nothing is actually being inserted into the database, can anyone spot what I've done wrong? Here is the c# code from my button event. 

    <pre lang="c#">
    protected void btnSubmit_Click(object sender, EventArgs e)
      {
         int index = 0;
         foreach (GridViewRow row in gvStockTake.Rows)
         {
          if (row.RowType == DataControlRowType.DataRow)
            {
             Label ID = gvStockTake.Rows[index].FindControl("itemId")as Label;
             TextBox BAR = gvStockTake.Rows[index].FindControl("txtBar") as TextBox;
             TextBox STORAGE = gvStockTake.Rows[index].FindControl("txtStorage") as TextBox;
    
                       SqlConnection con = new SqlConnection(connectionString);
                       SqlCommand cmd = new SqlCommand();
                       cmd.CommandType = CommandType.Text;
                       cmd.CommandText =
                            "insert into Stock_Take_Item(ItemID, BarQuantity, StorageQuantity) values(@ID, @BAR, @STORAGE) ";
                                
    
                       cmd.Parameters.Add("@ID", SqlDbType.VarChar).Value = ID.Text;
                       cmd.Parameters.Add("@BAR", SqlDbType.Int).Value = BAR.Text;
                       cmd.Parameters.Add("@STORAGE", SqlDbType.Int).Value = STORAGE.Text;
    
                            
                        }
    
                        index++;
                    }
    
                    Response.Write("Successfully inserted stock take items.");
                }</pre> 

    Here is the code for populating my Gridview:

      {
                DataTable dtbl = new DataTable();
                using (SqlConnection sqlCon = new SqlConnection(connectionString))
                {
                    sqlCon.Open();
                    SqlDataAdapter sqlDa = new SqlDataAdapter("SELECT Item.ItemID, Item.ItemDesc, Stock_Take_Item.BarQuantity, Stock_Take_Item.StorageQuantity FROM Item LEFT JOIN Stock_Take_Item ON Item.ItemID = Stock_Take_Item.ItemID", sqlCon);
                    sqlDa.Fill(dtbl);
                }
                gvStockTake.DataSource = dtbl;
                gvStockTake.DataBind();
            }

    Here is the code for my girdview (I have deleted all styling properties for shortening purposes)

    <pre><asp:GridView ID="gvStockTake" runat="server" AutoGenerateColumns="false" ShowHeaderWhenEmpty="true"
    
    
    
                    <Columns>
                    <asp:TemplateField HeaderText="Item ID">
                        <ItemTemplate>
                            <asp:Label id="itemId" Text='<%# Eval("ItemID")%>' runat="server" />
                    </ItemTemplate>
                    </asp:TemplateField>
                    <asp:TemplateField HeaderText="Item Description">
                        <ItemTemplate>
                            <asp:Label Text='<%# Eval("ItemDesc")%>' runat="server" />
                        </ItemTemplate>
                    </asp:TemplateField>
                     
                    <asp:TemplateField HeaderText="Bar Quantity">
                        <ItemTemplate>
                            <asp:TextBox ID="txtBar" runat="server"></asp:TextBox>
                        </ItemTemplate>
                    </asp:TemplateField>
                     
                    <asp:TemplateField HeaderText="Storage Quantity">
                        <ItemTemplate>
                            <asp:TextBox ID="txtStorage" runat="server"></asp:TextBox>
                        </ItemTemplate>
                    </asp:TemplateField>
    
                    </Columns>
                </asp:GridView>

    I'm still learning as i'm going so any reccomendations will be appreciated!

    Tuesday, April 10, 2018 9:13 PM

Answers

  • User-1716253493 posted
        protected void btnSubmit_Click(object sender, EventArgs e)
        {
            string query = "insert into Stock_Take_Item(ItemID, BarQuantity, StorageQuantity) values(@ID, @BAR, @STORAGE) ";
            SqlConnection con = new SqlConnection(connectionString);
            SqlCommand cmd = new SqlCommand(query, con);
            con.Open();
            foreach (GridViewRow row in gvStockTake.Rows)
            {
                Label ID = row.FindControl("itemId") as Label;
                TextBox BAR = row.FindControl("txtBar") as TextBox;
                TextBox STORAGE = row.FindControl("txtStorage") as TextBox;
                cmd.Parameters.Clear();
                cmd.Parameters.Add("@ID", SqlDbType.VarChar).Value = ID.Text;
                cmd.Parameters.Add("@BAR", SqlDbType.Int).Value = BAR.Text;
                cmd.Parameters.Add("@STORAGE", SqlDbType.Int).Value = STORAGE.Text;
                cmd.ExecuteNonQuery(); //query execution
            }
            con.Close();
            Response.Write("Successfully inserted stock take items.");
        } 

    Seem you have some problems in the codes.

    • you need to open/close the connection
    • you need to execute the sqlcommand
    • simply iterate gv rows, get the controls from the row

    I have modify your code, replace it

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, April 11, 2018 1:08 AM

All replies

  • User-1716253493 posted
        protected void btnSubmit_Click(object sender, EventArgs e)
        {
            string query = "insert into Stock_Take_Item(ItemID, BarQuantity, StorageQuantity) values(@ID, @BAR, @STORAGE) ";
            SqlConnection con = new SqlConnection(connectionString);
            SqlCommand cmd = new SqlCommand(query, con);
            con.Open();
            foreach (GridViewRow row in gvStockTake.Rows)
            {
                Label ID = row.FindControl("itemId") as Label;
                TextBox BAR = row.FindControl("txtBar") as TextBox;
                TextBox STORAGE = row.FindControl("txtStorage") as TextBox;
                cmd.Parameters.Clear();
                cmd.Parameters.Add("@ID", SqlDbType.VarChar).Value = ID.Text;
                cmd.Parameters.Add("@BAR", SqlDbType.Int).Value = BAR.Text;
                cmd.Parameters.Add("@STORAGE", SqlDbType.Int).Value = STORAGE.Text;
                cmd.ExecuteNonQuery(); //query execution
            }
            con.Close();
            Response.Write("Successfully inserted stock take items.");
        } 

    Seem you have some problems in the codes.

    • you need to open/close the connection
    • you need to execute the sqlcommand
    • simply iterate gv rows, get the controls from the row

    I have modify your code, replace it

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, April 11, 2018 1:08 AM
  • User-1838255255 posted

    Hi fredbrass,

    According to your description and needs, i know you want to insert the gridview data to the database when click the button, i think you could check this tutorial: 

    Insert multiple rows from GridView to Database in ASP.Net: 

    https://www.aspforums.net/Threads/400418/Insert-multiple-rows-from-GridView-to-Database-in-ASPNet/  

    Best Regards,

    Eric Du 

    Wednesday, April 11, 2018 3:04 AM