Insert value from gridview to database where value is not equal to 0

Question

User-367318540 posted

Below is gridview and Data

 DataTable dt = new DataTable();
        protected void Page_Load(object sender, EventArgs e)
        {
            if (!IsPostBack)
            {
              
                System.Data.DataTable dt = new System.Data.DataTable();
                dt.Columns.AddRange(new System.Data.DataColumn[4] {
                        new System.Data.DataColumn("I_ID", typeof(int)),
                        new System.Data.DataColumn("I_Amt", typeof(int)),
                         new System.Data.DataColumn("C_NO", typeof(int)),
                             new System.Data.DataColumn("C_Amt", typeof(int)),
                });
                dt.Rows.Add(21, 339,500,2);
                dt.Rows.Add(22, 622,400,25);
                dt.Rows.Add(23, 226,474,23);
                dt.Rows.Add(26, 339,0,0);
                dt.Rows.Add(27, 339,0,0);
                GridView1.DataSource = dt;
                GridView1.DataBind();

            }

        }

     

<div> </div> <div>       

<div>
 Cheque No :<asp:TextBox ID="chequno" runat="server"></asp:TextBox>
                Cheque Amount<asp:TextBox ID="chqamount" runat="server"></asp:TextBox>
            <asp:Button ID="btn_Adj" runat="server" Text="Adjust_Amount" OnClick="btn_Adj_Click" />
            <br />
            <br />

              <asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="false" OnRowDataBound="GridView1_RowDataBound">

                    <Columns>
                        <asp:TemplateField HeaderText=" ID">
                            <ItemTemplate>
                                <asp:Label ID="I_ID" runat="server" Text='<%#Bind("I_ID")%>'></asp:Label>
                            </ItemTemplate>
                        </asp:TemplateField>


                        <asp:TemplateField HeaderText=" Amount">
                            <ItemTemplate>
                                <asp:Label ID="I_Amt" runat="server" Text='<%#Bind("I_Amt")%>'></asp:Label>
                            </ItemTemplate>
                        </asp:TemplateField>
                        <asp:TemplateField HeaderText=" Cheque_NO">
                            <ItemTemplate>
                                <asp:TextBox ID="C_NO" runat="server" ReadOnly="true" Text='<%#Bind("C_NO")%>' ></asp:TextBox>
                            </ItemTemplate>
                        </asp:TemplateField>
                        <asp:TemplateField HeaderText=" Cheque_Amount">
                            <ItemTemplate>
                                <asp:TextBox ID="C_Amt" runat="server" ReadOnly="true" Text='<%#Bind("C_Amt")%>' ></asp:TextBox>
                            </ItemTemplate>
                        </asp:TemplateField>
                        <asp:TemplateField HeaderText="Status">
                            <ItemTemplate>
                                <asp:DropDownList ID="ddlStatus" runat="server" >
                                    <asp:ListItem Text="Pending" Value="1"></asp:ListItem>
                                    <asp:ListItem Text="Received" Value="2"></asp:ListItem> 
                                </asp:DropDownList>

                            </ItemTemplate>
                        </asp:TemplateField>
                    </Columns>
                </asp:GridView>
        </div>

Table 

CREATE TABLE [dbo].[tbl_Received](
[R_ID] [int] NOT NULL,
[I_ID] [int] NULL,
[Cheque_No] [int] NULL,
[Cheque_Amount] [int] NULL,
[Status] [varchar](50) NULL,
[R_Date] [date] NULL,
CONSTRAINT [PK_tbl_Received] PRIMARY KEY CLUSTERED 
(
[R_ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

Answer 1

User475983607 posted

The programming problem you are trying to solve is not clear.   Do you need help understanding the page life cycle and using events in Web Forms?  Do you need help designing a solution and can you explain the design intent?  Why is the GridView populated in code?  Are you trying to simulate a different data source than the table shown in the schema?

Answer 2

User-367318540 posted

Hi mgebhard,

i am saying that want to insert those data from gridview to database where row value is not equal to 0,in below image i highlighted in red square ,that data will insert into database

https://ibb.co/3fDzhRC 

Answer 3

User475983607 posted

akhterr

Hi mgebhard,

i am saying that want to insert those data from gridview to database where row value is not equal to 0,in below image i highlighted in red square ,that data will insert into database

https://ibb.co/3fDzhRC 

You have to understand the design is not good for web forms.  The GridView is designed to update one record at a time not several.  All the GridView events are geared toward updating a single record or doing something when the grid is bound.  Anyway, the following code is very fragile but it will work.  Feel free to tweak the code to fit your needs. 

<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="default.aspx.cs" Inherits="WebFormsDemo.Gridviews._default" %>

<!DOCTYPE html>

<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title></title>
</head>
<body>
    <form id="form1" runat="server">
        <div>
            Cheque No :<asp:TextBox ID="chequno" runat="server"></asp:TextBox>
            Cheque Amount<asp:TextBox ID="chqamount" runat="server"></asp:TextBox>
            <asp:Button ID="btn_Adj" runat="server" Text="Adjust_Amount" OnClick="btn_Adj_Click" />
            <br />
            <br />

            <asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="false" OnRowDataBound="GridView1_RowDataBound">
                <Columns>
                    <asp:TemplateField HeaderText=" ID">
                        <ItemTemplate>
                            <asp:Label ID="L_I_ID" runat="server" Text='<%#Bind("I_ID")%>'></asp:Label>
                            <asp:HiddenField ID="I_ID"  runat="server" Value='<%#Bind("I_ID")%>'></asp:HiddenField>
                        </ItemTemplate>
                    </asp:TemplateField>
                    <asp:TemplateField HeaderText=" Amount">
                        <ItemTemplate>
                            <asp:Label ID="L_I_Amt" runat="server" Text='<%#Bind("I_Amt")%>'></asp:Label>
                            <asp:HiddenField ID="I_Amt"  runat="server" Value='<%#Bind("I_Amt")%>'></asp:HiddenField>
                        </ItemTemplate>
                    </asp:TemplateField>
                    <asp:TemplateField HeaderText=" Cheque_NO">
                        <ItemTemplate>
                            <asp:TextBox ID="C_NO"  runat="server" ReadOnly="true" Text='<%#Bind("C_NO")%>'></asp:TextBox>
                        </ItemTemplate>
                    </asp:TemplateField>
                    <asp:TemplateField HeaderText=" Cheque_Amount">
                        <ItemTemplate>
                            <asp:TextBox ID="C_Amt"  runat="server" ReadOnly="true" Text='<%#Bind("C_Amt")%>'></asp:TextBox>
                        </ItemTemplate>
                    </asp:TemplateField>
                    <asp:TemplateField HeaderText="Status">
                        <ItemTemplate>
                            <asp:DropDownList ID="ddlStatus" runat="server">
                                <asp:ListItem Text="Pending" Value="1"></asp:ListItem>
                                <asp:ListItem Text="Received" Value="2"></asp:ListItem>
                            </asp:DropDownList>
                        </ItemTemplate>
                    </asp:TemplateField>
                </Columns>
            </asp:GridView>
            <hr />
            <div>
                <asp:Button ID="UpdateRecords" runat="server" Text="Update Records" OnClick="UpdateRecords_Click" />
            </div>
            <div>
                <asp:Literal ID="Literal1" Mode="PassThrough"  runat="server" Text=""></asp:Literal>
            </div>
        </div>
    </form>
</body>
</html>

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;

namespace WebFormsDemo.Gridviews
{
    public partial class _default : System.Web.UI.Page
    {
        protected void Page_Load(object sender, EventArgs e)
        {
            if (!IsPostBack)
            {

                System.Data.DataTable dt = new System.Data.DataTable();
                dt.Columns.AddRange(new System.Data.DataColumn[4] {
                        new System.Data.DataColumn("I_ID", typeof(int)),
                        new System.Data.DataColumn("I_Amt", typeof(int)),
                         new System.Data.DataColumn("C_NO", typeof(int)),
                             new System.Data.DataColumn("C_Amt", typeof(int)),
                });
                dt.Rows.Add(21, 339, 500, 2);
                dt.Rows.Add(22, 622, 400, 25);
                dt.Rows.Add(23, 226, 474, 23);
                dt.Rows.Add(26, 339, 0, 0);
                dt.Rows.Add(27, 339, 0, 0);
                GridView1.DataSource = dt;
                GridView1.DataBind();

            }
        }

        protected void btn_Adj_Click(object sender, EventArgs e)
        {

        }



        protected void GridView1_RowDataBound(object sender, GridViewRowEventArgs e)
        {
            if(e.Row.RowType == DataControlRowType.DataRow )
            {
                int id = int.Parse(((HiddenField)e.Row.FindControl("I_ID")).Value);
                int amount = int.Parse(((HiddenField)e.Row.FindControl("I_Amt")).Value);
                int checkNumber = int.Parse(((TextBox)e.Row.FindControl("C_NO")).Text);
                int checkAmount = int.Parse(((TextBox)e.Row.FindControl("C_Amt")).Text);

                if (amount != 0 & checkAmount != 0)
                {
                    InsertRecord(id, amount, checkNumber, checkAmount);
                }
            }           
        }

        protected void UpdateRecords_Click(object sender, EventArgs e)
        {
            Literal1.Text = string.Empty;

           for (int i = 0; i < GridView1.Rows.Count; i++)
            {
                int id = int.Parse(((HiddenField)GridView1.Rows[i].Cells[0].Controls[0].FindControl("I_ID")).Value);
                int amount = int.Parse(((HiddenField)GridView1.Rows[i].Cells[0].Controls[0].FindControl("I_Amt")).Value);
                int checkNumber = int.Parse(((TextBox)GridView1.Rows[i].Cells[0].Controls[0].FindControl("C_NO")).Text);
                int checkAmount = int.Parse(((TextBox)GridView1.Rows[i].Cells[0].Controls[0].FindControl("C_Amt")).Text);

                if (amount != 0 & checkAmount != 0)
                {
                    InsertRecord(id, amount, checkNumber, checkAmount);
                }
            }

        }

        protected void InsertRecord(int id, int amount, int checkNumber, int checkAmount)
        {
            Literal1.Text += id + ", " + amount + ", " + checkNumber + ", " + checkAmount + "<br />";
        }
    }
}

Answer 4

User288213138 posted

Hi akhterr

i am saying that want to insert those data from gridview to database where row value is not equal to 0,in below image i highlighted in red square ,that data will insert into database

You can try below code:

 Cheque No :<asp:TextBox ID="chequno" runat="server"></asp:TextBox>
            Cheque Amount<asp:TextBox ID="chqamount" runat="server"></asp:TextBox>
            <asp:Button ID="btn_Adj" runat="server" Text="Adjust_Amount" OnClick="btn_Adj_Click" />
            <br />
            <br />

            <asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="false" OnRowDataBound="GridView1_RowDataBound">

                <Columns>
                    <asp:TemplateField HeaderText=" ID">
                        <ItemTemplate>
                            <asp:Label ID="I_ID" runat="server" Text='<%#Bind("I_ID")%>'></asp:Label>
                        </ItemTemplate>
                    </asp:TemplateField>


                    <asp:TemplateField HeaderText=" Amount">
                        <ItemTemplate>
                            <asp:Label ID="I_Amt" runat="server" Text='<%#Bind("I_Amt")%>'></asp:Label>
                        </ItemTemplate>
                    </asp:TemplateField>
                    <asp:TemplateField HeaderText=" Cheque_NO">
                        <ItemTemplate>
                            <asp:TextBox ID="C_NO" runat="server" ReadOnly="true" Text='<%#Bind("C_NO")%>'></asp:TextBox>
                        </ItemTemplate>
                    </asp:TemplateField>
                    <asp:TemplateField HeaderText=" Cheque_Amount">
                        <ItemTemplate>
                            <asp:TextBox ID="C_Amt" runat="server" ReadOnly="true" Text='<%#Bind("C_Amt")%>'></asp:TextBox>
                        </ItemTemplate>
                    </asp:TemplateField>
                    <asp:TemplateField HeaderText="Status">
                        <ItemTemplate>
                            <asp:DropDownList ID="ddlStatus" runat="server">
                                <asp:ListItem Text="Pending" Value="1"></asp:ListItem>
                                <asp:ListItem Text="Received" Value="2"></asp:ListItem>
                            </asp:DropDownList>

                        </ItemTemplate>
                    </asp:TemplateField>
                </Columns>
            </asp:GridView>
            <asp:Button ID="Button1" runat="server" Text="Button" OnClick="Button1_Click" />



DataTable dt = new DataTable();
        protected void Page_Load(object sender, EventArgs e)
        {
            if (!IsPostBack)
            {
                System.Data.DataTable dt = new System.Data.DataTable();
                dt.Columns.AddRange(new System.Data.DataColumn[4] {
                        new System.Data.DataColumn("I_ID", typeof(int)),
                        new System.Data.DataColumn("I_Amt", typeof(int)),
                         new System.Data.DataColumn("C_NO", typeof(int)),
                             new System.Data.DataColumn("C_Amt", typeof(int)),
                });
                dt.Rows.Add(21, 339, 500, 2);
                dt.Rows.Add(22, 622, 400, 25);
                dt.Rows.Add(23, 226, 474, 23);
                dt.Rows.Add(26, 339, 0, 0);
                dt.Rows.Add(27, 339, 0, 0);
                GridView1.DataSource = dt;
                GridView1.DataBind();
            }

        }
      
        public string constr = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
        protected void Button1_Click(object sender, EventArgs e)
        {
            foreach (GridViewRow row in GridView1.Rows)
            {
                TextBox tbC_NO = (TextBox)row.FindControl("C_NO");
                TextBox tbC_Amt = (TextBox)row.FindControl("C_Amt");
                if (Convert.ToInt32(tbC_NO.Text) != 0 && Convert.ToInt32(tbC_Amt.Text) != 0)
                {
                    string query = "INSERT INTO tbl_Received(Cheque_No,Cheque_Amount) VALUES(@Cheque_No,@Cheque_Amounto)";
                    SqlConnection con = new SqlConnection(constr);
                    SqlCommand cmd = new SqlCommand(query);
                    cmd.Parameters.AddWithValue("@Cheque_No", tbC_NO.Text);
                    cmd.Parameters.AddWithValue("@Cheque_Amounto", tbC_Amt.Text);
                    cmd.Connection = con;
                    con.Open();
                    cmd.ExecuteNonQuery();
                }
            }
        }

And you need to change the Is Identity of the field I_ID of your table to True

Best regards,

Sam