locked
Save Gridview rows into Database when text value is greater than zero RRS feed

  • Question

  • User1152553138 posted
    <%@ Page Language="C#" AutoEventWireup="true" CodeFile="TestTT.aspx.cs" Inherits="TestTT" %>
    
    <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
    
    <html xmlns="http://www.w3.org/1999/xhtml">
    <head runat="server">
        <title></title>
    
        <script type="text/javascript" src="http://ajax.googleapis.com/ajax/libs/jquery/1.8.3/jquery.min.js"></script>
    
        <script type="text/javascript">
    
            function CalculateTotals() {
                var gv = document.getElementById("<%= GridView1.ClientID %>");
                var tb = gv.getElementsByTagName("input");
                var lb = gv.getElementsByTagName("span");
    
                var sub = 0;
                var total = 0;
                var indexQ = 1;
                var indexP = 0;
                var price = 0;
    
                for (var i = 0; i < tb.length; i++) {
                    if (tb[i].type == "text") {
                        ValidateNumber(tb[i]);
    
                        price = lb[indexP].innerHTML.replace("$", "").replace(",", "");
                        sub = parseFloat(price) - parseFloat(tb[i].value);
                        if (isNaN(sub)) {
                            lb[i + indexQ].innerHTML = "0.00";
                            sub = 0;
                        }
                        else {
                            lb[i + indexQ].innerHTML = FormatToMoney(sub, "Rs.", ",", "."); ;
                        }
    
                        indexQ++;
                        indexP = indexP + 2;
    
                        total += parseFloat(sub);
                    }
                }
    
                lb[lb.length - 1].innerHTML = FormatToMoney(total, "Rs.", ",", ".");
            }
    
            function ValidateNumber(o) {
                if (o.value.length > 0) {
                    o.value = o.value.replace(/[^\d]+/g, ''); //Allow only whole numbers  
                }
            }
            function isThousands(position) {
                if (Math.floor(position / 3) * 3 == position) return true;
                return false;
            };
    
            function FormatToMoney(theNumber, theCurrency, theThousands, theDecimal) {
                var theDecimalDigits = Math.round((theNumber * 100) - (Math.floor(theNumber) * 100));
                theDecimalDigits = "" + (theDecimalDigits + "0").substring(0, 2);
                theNumber = "" + Math.floor(theNumber);
                var theOutput = theCurrency;
                for (x = 0; x < theNumber.length; x++) {
                    theOutput += theNumber.substring(x, x + 1);
                    if (isThousands(theNumber.length - x - 1) && (theNumber.length - x - 1 != 0)) {
                        theOutput += theThousands;
                    };
                };
                theOutput += theDecimal + theDecimalDigits;
                return theOutput;
            }   
        </script>  
    
    
        <script>
            $(function () {
                $(".field1").keyup(function () {
    
                    var total = 0;
                    $(".field1").each(function () {
                        total += parseFloat($(this).val());
                    });
    
                    $(".totaloffield td").eq(3).text(total);
    
    
    
                });
    
    
    
            })
    </script> 
    
    
    </head>
    <body>
        <form id="form1" runat="server">
    
    
    
       <asp:gridview ID="GridView1"  runat="server"  ShowFooter="true" AutoGenerateColumns="false"> 
        
            <Columns>  
    
            <asp:BoundField DataField="RowNumber" HeaderText="Row Number" />  
            <asp:BoundField DataField="Description" HeaderText="Item Description" />  
    
             <asp:TemplateField HeaderText="Description">  
                <ItemTemplate>  
                   <asp:Label ID="LblDescription" runat="server" Font-Bold="true" ForeColor="Blue" Text="0" ></asp:Label> 
                </ItemTemplate>             
            </asp:TemplateField>  
    
    
            <asp:TemplateField HeaderText="Item Price">  
                <ItemTemplate>  
                    <asp:Label ID="LBLPrice" runat="server" Text='<%# Eval("Price","{0:C}") %>'></asp:Label>  
                </ItemTemplate>  
                <FooterTemplate>  
                    <b>Total Qty:</b>  
                </FooterTemplate>  
            </asp:TemplateField>  
    
            <asp:TemplateField HeaderText="Quantity">  
                <ItemTemplate>  
                    <asp:TextBox ID="TXTQty" runat="server" onkeyup="CalculateTotals();" class="field1" Text="0" ></asp:TextBox>  
                </ItemTemplate>  
                <FooterTemplate>  
                    <asp:Label ID="LBLQtyTotal" runat="server" Font-Bold="true" ForeColor="Blue" Text="0" ></asp:Label>       
                    <b>Total Amount:</b>  
                </FooterTemplate>  
            </asp:TemplateField>  
    
            <asp:TemplateField HeaderText="Sub-Total">  
                <ItemTemplate>  
                    <asp:Label ID="LBLSubTotal" runat="server" ForeColor="Green" Text="0.00"></asp:Label>  
                </ItemTemplate>  
                <FooterTemplate>  
                    <asp:Label ID="LBLTotal" runat="server" ForeColor="Green" Font-Bold="true" Text="0.00"></asp:Label>  
                </FooterTemplate>  
            </asp:TemplateField>  
    
            </Columns>  
    
             <FooterStyle Font-Bold="True" CssClass="totaloffield" />
    
        </asp:gridview>  
    
    
        <asp:Button ID="BtnSave" runat="server" Text="Save" onclick="BtnSave_Click" />
    
        </form>
    
    </body>
    </html>
    
    using System;
    using System.Collections.Generic;
    using System.Linq;
    using System.Web;
    using System.Web.UI;
    using System.Web.UI.WebControls;
    using System.Data;
    using System.Data.SqlClient;
    using System.Configuration;
    using System.Web.Services;
    
    public partial class TestTT : System.Web.UI.Page
    {
    
     protected void Page_Load(object sender, EventArgs e)
        {
            if (!IsPostBack)
            {
                BindDummyDataToGrid();
            }
        }
    
     protected void BtnSave_Click(object sender, EventArgs e)
        {
            String strConnString = ConfigurationManager.ConnectionStrings["ConString"].ConnectionString;
            SqlConnection con = new SqlConnection(strConnString);
    
            foreach (GridViewRow row in GridView1.Rows)
            {
                string a = ((Label)row.FindControl("LblDescription")).Text;
                string b = ((Label)row.FindControl("LBLPrice")).Text;
                string c = ((TextBox)row.FindControl("TXTQty")).Text;
                string d = ((Label)row.FindControl("LBLSubTotal")).Text;
                            
                SqlCommand cmd121 = new SqlCommand("INSERT INTO OrderDetail ( Description,Price,Qty,Total ) values ( @Description,@Price,@Qty,@Total )", con);
    
                cmd121.Parameters.Add(new SqlParameter("@Description", a));
                cmd121.Parameters.Add(new SqlParameter("@Price", b));
                cmd121.Parameters.Add(new SqlParameter("@Qty", c));
                cmd121.Parameters.Add(new SqlParameter("@Total", d));
               
                if (row.Cells[3].Text == "0")
                {
                    Response.Write("<script> alert(' Quantity Zero ...! ');window.location.href='Test2.aspx';</script>");
                }
                else
                {
                    con.Open();
                    cmd121.ExecuteNonQuery();
                    con.Close();
                }
    
    
    
            }
    
            Response.Write("<script> alert(' Order Saved Successfully ...! ');window.location.href='Test2.aspx';</script>");
        }
    }

    Save Gridview rows into Database only when text value is greater than zero.

    Wednesday, October 10, 2018 10:47 AM

All replies

  • User-1716253493 posted
    con.Open();
    SqlCommand cmd121 = new SqlCommand("INSERT INTO OrderDetail ( Description,Price,Qty,Total ) values ( @Description,@Price,@Qty,@Total )", con);
    String zeroerrors="";
    string sukses=""; foreach (GridViewRow row in GridView1.Rows) { if (Convert.ToInt32(row.Cells[3].Text) <= 0) { //collect zeroerrors messages } else {
    //collect sukses message string a = ((Label)row.FindControl("LblDescription")).Text; string b = ((Label)row.FindControl("LBLPrice")).Text; string c = ((TextBox)row.FindControl("TXTQty")).Text; string d = ((Label)row.FindControl("LBLSubTotal")).Text; cmd121.Parameters.Clear() cmd121.Parameters.Add(new SqlParameter("@Description", a)); cmd121.Parameters.Add(new SqlParameter("@Price", b)); cmd121.Parameters.Add(new SqlParameter("@Qty", c)); cmd121.Parameters.Add(new SqlParameter("@Total", d)); cmd121.ExecuteNonQuery(); } //show alert here to show which line sukses/error or how many errors con.Close();

    Wednesday, October 10, 2018 11:25 PM
  • User-893317190 posted

    Hi Ashraf007,

    From you code

    if (row.Cells[3].Text == "0")
                {
                    Response.Write("<script> alert(' Quantity Zero ...! ');window.location.href='Test2.aspx';</script>");
                }

    It seems that  you want to check whether the content of the cell is "0".

    If the cell  contains control , the content of the cell will always be "".

    If you want to get the text of the control in the cell , you could use  the value you get above.

    For example,  

    if (c == "0")
                {
                    Response.Write("<script> alert(' Quantity Zero ...! ');window.location.href='Test2.aspx';</script>");
                }

    Best regards,

    Ackerly Xu

    Thursday, October 11, 2018 3:06 AM
  • User1152553138 posted

    Thank you for the reply ...

    I would like to clear my scenario once again Eg: i have 10 rows out of which 6 rows has value which is greater than 0.

    What i need is i would like to save the gridview rows values into database which has value greater than 0. Only 6 rows will be inserted into database. I hope its clear.

    Thursday, October 11, 2018 6:20 AM
  • User-893317190 posted

    Hi Ashraf007,

    I think your code  could meet your requirement, the only problem is that row.Cells[3].Text should get the value you want. 

    You could debug to see whether it has the value you want. If not , you  should change to use the variable "c" instead of row.Cells[3].Text.

    I have tried , when I text in the textbox "0" ,that row will not be saved int the  database. Do you mean this?

    Or I guess you have original data in database , it have 10 records. Your method  BindDummyDataToGrid();  shows the 10 records.

    If the textbox belongs to one record is "0" , the record should not be insterted into database.If so , I think you should use update instead of insert,or it will have duplicate data.

    If not , what do you do in your method BindDummyDataToGrid();? What's the data from the method?

      if (row.Cells[3].Text == "0")
                {
                    Response.Write("<script> alert(' Quantity Zero ...! ');window.location.href='Test2.aspx';</script>");
                }
                else
                {
                    con.Open();
                    cmd121.ExecuteNonQuery();
                    con.Close();
                }
    

    Best regards,

    Ackerly Xu

    Thursday, October 11, 2018 7:02 AM
  • User1152553138 posted

    Ackerly Xu

    Hi Ashraf007,

    I think your code  could meet your requirement, the only problem is that row.Cells[3].Text should get the value you want. 

    You could debug to see whether it has the value you want. If not , you  should change to use the variable "c" instead of row.Cells[3].Text.

    I have tried , when I text in the textbox "0" ,that row will not be saved int the  database. Do you mean this?

    Or I guess you have original data in database , it have 10 records. Your method  BindDummyDataToGrid();  shows the 10 records.

    If the textbox belongs to one record is "0" , the record should not be insterted into database.If so , I think you should use update instead of insert,or it will have duplicate data.

    If not , what do you do in your method BindDummyDataToGrid();? What's the data from the method?

      if (row.Cells[3].Text == "0")
                {
                    Response.Write("<script> alert(' Quantity Zero ...! ');window.location.href='Test2.aspx';</script>");
                }
                else
                {
                    con.Open();
                    cmd121.ExecuteNonQuery();
                    con.Close();
                }

    Best regards,

    Ackerly Xu

    Yes ...

    when text in the textbox  is "0" ,then that row will not be saved into the  database. The row which has more than "0" values then that row alone should be saved into database.

    Thursday, October 11, 2018 9:31 AM
  • User-893317190 posted

    Hi Ashraf007,

    I think if you  use the code below  you could meet your requirement.

    if (c == "0")
                {
                    Response.Write("<script> alert(' Quantity Zero ...! ');window.location.href='Test2.aspx';</script>");
                }

    Could you tell me what problem you have met?And its not clear what data is in your database and what you do in your method  BindDummyDataToGrid().

    If you want to  ensure that the value in the textbox is bigger than 0 , just as oned_gk has posted , convert it to int32 and then do the comparison.

    Best regards,

    Ackerly Xu

    Friday, October 12, 2018 1:11 AM
  • User-1716253493 posted

    I guess the sample is equal your reaquirement, 4 errors and 6 sukses.

    If You don't want the messages, you can modify it. Like 6 lines inserted message

    Friday, October 12, 2018 3:21 AM
  • User1152553138 posted

    This is also not working ...

    Saturday, October 13, 2018 3:56 AM
  • User-1716253493 posted

    You need to clear parameters before re-add parameters or define new command inside the loop

    cmd121.Parameters.Clear()
    Saturday, October 13, 2018 5:16 AM