How to insert gridview rows one column and textbox data outside from gridview into table?

Question

User-367318540 posted

I am trying to insert textbox and gridview one column(prdno) data into table 

below is my html

Pack Transfer OF Small Bale</h1>
 </td>
 <td>&nbsp;</td>
 </tr>
 <tr>
 <td class="auto-style2">&nbsp;</td>
 <td class="auto-style3">
 <h4>&nbsp;&nbsp;Pack No : <asp:TextBox ID="txtPack" CssClass="form-control input-lg" Placeholder="Pack No" AutoPostBack="true" runat="server" Width="67px" style="height: 25px" OnTextChanged="txtPack_TextChanged" ></asp:TextBox>
 &nbsp;
  
 
  
 <asp:Label ID="lblIP" runat="server" Text="Label" Visible="false"></asp:Label>
  
 
  
 </h4>
 <p>
 <asp:TextBox ID="txtOrder" Visible="true" Enabled="false" runat="server" Height="16px" Width="121px"></asp:TextBox>
 &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Transfer Order Ref : <asp:TextBox ID="txtRefnotr" CssClass="form-control input-lg" Placeholder="Ref No" AutoPostBack="true" runat="server" OnTextChanged="txtRefnotr_TextChanged" Height="23px" Width="83px" ></asp:TextBox>
 
 
  
 &nbsp;
 <asp:TextBox ID="txtOrderto" Visible="true" Enabled="false" runat="server" Height="28px" Width="40px"></asp:TextBox>
 <asp:TextBox ID="TextBox1" AutoPostBack="true" runat="server" OnTextChanged="TextBox1_TextChanged"></asp:TextBox>
 <asp:Button ID="btnUpdate" runat="server" Text="Transfer" OnClick = "Update" />
 
 
  
 </p>
  
  
 </td>
 <td>&nbsp;</td>
 </tr>
 <tr>
 <td class="auto-style2">&nbsp;</td>
 <td class="auto-style3">
 <div class="auto-style4">
 
 
 <asp:GridView ID="GVallbb" runat="server" BackColor="White" BorderColor="#E7E7FF" BorderStyle="None" BorderWidth="1px" CellPadding="3" GridLines="Horizontal" AutoGenerateColumns = "false" Font-Names = "Arial" CssClass="HeaderFreez" ShowHeader = "true"
 
 Font-Size = "11pt" AlternatingRowStyle-BackColor = "#C2D69B" class="grid_scroll" PageSize="5" ShowHeaderWhenEmpty="True" DataKeyNames = "Prdno,PDID" ShowFooter="True" style="margin-right: 0px">
 <AlternatingRowStyle BackColor="#F7F7F7" />
 <FooterStyle BackColor="#B5C7DE" ForeColor="#4A3C8C" />
 <HeaderStyle BackColor="#4A3C8C" Font-Bold="True" ForeColor="#F7F7F7" />
 <PagerStyle BackColor="#E7E7FF" ForeColor="#4A3C8C" HorizontalAlign="Right" />
 <RowStyle BackColor="#E7E7FF" ForeColor="#4A3C8C" />
 <SelectedRowStyle BackColor="#738A9C" Font-Bold="True" ForeColor="#F7F7F7" />
 <SortedAscendingCellStyle BackColor="#F4F4FD" />
 <SortedAscendingHeaderStyle BackColor="#5A4C9D" />
 <SortedDescendingCellStyle BackColor="#D8D8F0" />
 <SortedDescendingHeaderStyle BackColor="#3E3277" />
 <Columns>
 
 <asp:TemplateField>
 
 <HeaderTemplate>
 
 <asp:CheckBox ID="checkAll" runat="server" onclick = "checkAll(this);true" />
 
 </HeaderTemplate>
 
 <ItemTemplate>
 
 <asp:CheckBox ID="CheckBox1" runat="server" onclick = "Check_Click(this)" />
 
 </ItemTemplate>
 
</asp:TemplateField>
 
 <asp:TemplateField HeaderText="ID" >
 <ItemTemplate>
 <asp:HiddenField ID="hfBID" runat="server" Value='<%#Eval("PDID")%>' />
 <asp:Label ID="PDID" runat="server" Visible="false" Text='<%#Bind("PDID")%>' ></asp:Label>
 </ItemTemplate>
 </asp:TemplateField>
 <asp:TemplateField HeaderText="Bale No">
 <ItemTemplate>
 <asp:HiddenField ID="hfBID1" runat="server" Value='<%#Eval("Prdno")%>' />
 
 <asp:Label ID="Prdno" runat="server" Text='<%#Bind("Prdno")%>' ></asp:Label>
 </ItemTemplate>
 </asp:TemplateField>
 <asp:TemplateField HeaderText="OrderNo">
 <ItemTemplate>
 <asp:label ID="OrderNo" runat="server" Text='<%#Bind("OrderNo")%>' ></asp:label>
 </ItemTemplate>
 </asp:TemplateField>
 <asp:TemplateField HeaderText="Item Name">
 <ItemTemplate>
 <asp:Label ID="itemName" runat="server" Text='<%#Bind("Descriptionitem")%>'></asp:Label>
 </ItemTemplate>
 <FooterTemplate><asp:Label ID="lblTotalText" Text="Total" runat="server"></asp:Label></FooterTemplate>
 </asp:TemplateField>
 <asp:TemplateField HeaderText="QTY">
 <ItemTemplate>
 <asp:Label ID="QTY1" runat="server" Text='<%#Bind("QTY1")%>'></asp:Label>
 </ItemTemplate>
 <FooterTemplate><asp:Label ID="lblTotal" runat="server"></asp:Label></FooterTemplate>
 <%--<FooterTemplate><asp:Label ID="lblTotalText" Text="Total" runat="server"></asp:Label></FooterTemplate>--%>
 </asp:TemplateField>
  
 
 
 </Columns>
 </asp:GridView>
 </div>

C# for inserting data into table

i have already data into prdno in gridview(Which is i am retrieving from database) and remaining textbox value i will input (i will not insert textbox value into gridview ) ,i want that each row(Prdno) in gridview save with textbox Data

string query = "Insert into SBtrns (Prdno,FOrderNo,TOrderNo,IPAddress,Date,Etime,Type) values  (@Prdno,@FOrderNo,@TOrderNo,@IPAddress,@Date,@Etime,@Type)";
              SqlCommand cmd = new SqlCommand(query);
              {
                  cmd.Connection = con;
                  cmd.Parameters.AddWithValue("@Prdno", prdno);
 
                  cmd.Parameters.AddWithValue("@FOrderNo", txtOrder.Text.Trim());
                  cmd.Parameters.AddWithValue("@TOrderNo", txtOrderto.Text.Trim());
 
                  cmd.Parameters.AddWithValue("@IPAddress", lblIP.Text);
                  cmd.Parameters.AddWithValue("@Date", DateTime.Now.ToString());
                  cmd.Parameters.AddWithValue("@Etime", DateTime.Now.ToString("hh:mm"));
                  cmd.Parameters.AddWithValue("@Type", "TSSBP");
                  con.Open();
                  cmd.ExecuteNonQuery();
                  con.Close();

Answer 1

User288213138 posted

Hi akhterr,

i have already data into prdno in gridview(Which is i am retrieving from database) and remaining textbox value i will input (i will not insert textbox value into gridview ) ,i want that each row(Prdno) in gridview save with textbox Data

You can get the Prdno column value by traversing the GridView.

protected void Button1_Click(object sender, EventArgs e)
        {
            foreach (GridViewRow row in GVallbb.Rows)
            {

                string prdno = ((Label)row.FindControl("Prdno")).Text;
                Session["Prdno"] += prdno;
            }

            string query = "Insert into SBtrns (Prdno,FOrderNo,TOrderNo,IPAddress,Date,Etime,Type) values  (@Prdno,@FOrderNo,@TOrderNo,@IPAddress,@Date,@Etime,@Type)";
            SqlCommand cmd = new SqlCommand(query);
            {
                cmd.Connection = con;
                cmd.Parameters.AddWithValue("@Prdno", Session["Prdno"].ToString());

                cmd.Parameters.AddWithValue("@FOrderNo", txtOrder.Text.Trim());
                cmd.Parameters.AddWithValue("@TOrderNo", txtOrderto.Text.Trim());

                cmd.Parameters.AddWithValue("@IPAddress", lblIP.Text);
                cmd.Parameters.AddWithValue("@Date", DateTime.Now.ToString());
                cmd.Parameters.AddWithValue("@Etime", DateTime.Now.ToString("hh:mm"));
                cmd.Parameters.AddWithValue("@Type", "TSSBP");
                con.Open();
                cmd.ExecuteNonQuery();
                con.Close();
            }
        }

Best regards,

Sam

Answer 2

User-367318540 posted

Hi samwu,

facing below error 

The conversion of the nvarchar value '100558291005548410055299100545811005542010055356100546161005540110054723' overflowed an int column.
The statement has been terminated.

Answer 3

User288213138 posted

Hi akhterr,

The conversion of the nvarchar value '100558291005548410055299100545811005542010055356100546161005540110054723' overflowed an int column.

That value is too large for an int, you can try to modify the data type to be nvarchar(MAX) for that value.

Best regards,

Sam

Answer 4

User-367318540 posted

i have modify your code please view and value is not too large ,but data is not getting insert into table and it's type(Prdno) in integer please view image 

https://ibb.co/RYLtKh9

 private void Insertintoleger()
        {
            foreach (GridViewRow row in GVallbb.Rows)
            {

                int prdno = int.Parse(((Label)row.FindControl("Prdno")).Text);
                Session["Prdno"] = prdno;
            }

            string query = "Insert into SBtrns (Prdno,FOrderNo,TOrderNo,IPAddress,Date,Etime,Type) values  (@Prdno,@FOrderNo,@TOrderNo,@IPAddress,@Date,@Etime,@Type)";
                    SqlCommand cmd = new SqlCommand(query);
                    {
                        cmd.Connection = con;
                        cmd.Parameters.AddWithValue("@Prdno", Session["Prdno"].ToString());

                        cmd.Parameters.AddWithValue("@FOrderNo", txtOrder.Text.Trim());
                        cmd.Parameters.AddWithValue("@TOrderNo", txtOrderto.Text.Trim());

                        cmd.Parameters.AddWithValue("@IPAddress", lblIP.Text);
                        cmd.Parameters.AddWithValue("@Date", DateTime.Now.ToString());
                        cmd.Parameters.AddWithValue("@Etime", DateTime.Now.ToString("hh:mm"));
                        cmd.Parameters.AddWithValue("@Type", "TSBP");
                        con.Open();
                        cmd.ExecuteNonQuery();
                        con.Close();
                        txtPack.Text = "";
                    }
                }
            
        

Answer 5

User281315223 posted

If you want to ensure that everything is inserted, you'll need to place your insertion logic _in_ your loop itself:

private void Insertintoleger()
{
    var query = "Insert into SBtrns (Prdno,FOrderNo,TOrderNo,IPAddress,Date,Etime,Type) values  (@Prdno,@FOrderNo,@TOrderNo,@IPAddress,@Date,@Etime,@Type)";

    foreach (GridViewRow row in GVallbb.Rows)
    {
         int prdno = int.Parse(((Label)row.FindControl("Prdno")).Text);
         SqlCommand cmd = new SqlCommand(query);
         {
             cmd.Connection = con;
             cmd.Parameters.AddWithValue("@Prdno", Session["Prdno"].ToString());
             cmd.Parameters.AddWithValue("@FOrderNo", txtOrder.Text.Trim());
             cmd.Parameters.AddWithValue("@TOrderNo", txtOrderto.Text.Trim());
             cmd.Parameters.AddWithValue("@IPAddress", lblIP.Text);
             cmd.Parameters.AddWithValue("@Date", DateTime.Now.ToString());
             cmd.Parameters.AddWithValue("@Etime", DateTime.Now.ToString("hh:mm"));
             cmd.Parameters.AddWithValue("@Type", "TSBP");
             con.Open();
             cmd.ExecuteNonQuery();
             con.Close();
             txtPack.Text = "";
          }
    }
}

If you only wanted to insert a _single_ record, which your previous code would have done then it should be working as expected. If every row is capable of performing an insertion, but you only want to perform one, you'll likely want to check the event arguments to identify the specific row so you know which text values to read and insert.

Answer 6

User288213138 posted

Hi akhterr,

i have modify your code please view and value is not too large ,but data is not getting insert into table and it's type(Prdno) in integer please view image 

foreach (GridViewRow row in GVallbb.Rows)
            {

                int prdno = int.Parse(((Label)row.FindControl("Prdno")).Text);
                Session["Prdno"] = prdno;
            }

If you are using this code then you can only get the last value of the Prdno column, or you can use Rion's code, place your insertion logic _in_ your loop itself.

private void Insertintoleger()
        {
            foreach (GridViewRow row in GVallbb.Rows)
            {

                int prdno = int.Parse(((Label)row.FindControl("Prdno")).Text);
                Session["Prdno"] = prdno;
            }

            string query = "Insert into SBtrns (Prdno,FOrderNo,TOrderNo,IPAddress,Date,Etime,Type) values  (@Prdno,@FOrderNo,@TOrderNo,@IPAddress,@Date,@Etime,@Type)";
                    SqlCommand cmd = new SqlCommand(query);
                    {
                        cmd.Connection = con;
                        cmd.Parameters.AddWithValue("@Prdno", Session["Prdno"].ToString());

                        cmd.Parameters.AddWithValue("@FOrderNo", txtOrder.Text.Trim());
                        cmd.Parameters.AddWithValue("@TOrderNo", txtOrderto.Text.Trim());

                        cmd.Parameters.AddWithValue("@IPAddress", lblIP.Text);
                        cmd.Parameters.AddWithValue("@Date", DateTime.Now.ToString());
                        cmd.Parameters.AddWithValue("@Etime", DateTime.Now.ToString("hh:mm"));
                        cmd.Parameters.AddWithValue("@Type", "TSBP");
                        con.Open();
                        cmd.ExecuteNonQuery();
                        con.Close();
                        txtPack.Text = "";
                    }
                }

These codes work fine on my side. I suggest you set breakpoints to debug your code to find question.

Best regards,

sam

Answer 7

User-367318540 posted

Hi Rion,

I have nine rows in gridview ,just last row getting insert into database 81 time 

Answer 8

User288213138 posted

Hi akhterr,

akhterr

I have nine rows in gridview ,just last row getting insert into database 81 time 

Can your share me your complete code? 

Do you want to insert data from a certain column of the GridView into the database, or insert a certain cell into the database?

Best regards,

Sam