GridView Column total on onBlur event using Javascript

GridView Column total on onBlur event using Javascript.
null
It is very desire task to show the total of gridview dynamic generate column on Blur or onchnage event using javascript.it is working fine with updatepanel.
so today i am posting this

Step1 – Create GridView

 <asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="false" 
            onrowdatabound="GridView1_RowDataBound">
        <Columns>
        
 <asp:TemplateField HeaderText="col1">
<ItemTemplate>
<asp:TextBox ID="txtcol1" Text='<%#Eval("id1") %>' runat="server">
</asp:TextBox>
 </ItemTemplate>
 </asp:TemplateField>
 
  <asp:TemplateField HeaderText="col2">
<ItemTemplate>
<asp:TextBox ID="txtcol2" Text='<%#Eval("id2") %>' runat="server">
</asp:TextBox>
 </ItemTemplate>
 </asp:TemplateField>
 
        </Columns>
        </asp:GridView>

Step2 – cearte Javascript function

    function JvfunonBlur()
    {
      var grid = document.getElementById('<%=GridView1.ClientID %>');
        var col1;
        var totalcol1 = 0;

        var col2;
        var totalcol2 = 0; 
        
          
        for (i = 0; i < grid.rows.length; i++) 
        {
            col1 = grid.rows[i].cells[0];
            col2 = grid.rows[i].cells[1];
            
             for (j = 0; j < col1.childNodes.length; j++) 
             {
                if (col1.childNodes[j].type == "text")
                 {
                 if(!isNaN(col1.childNodes[j].value) &&  col1.childNodes[j].value != "")
                     {
                        totalcol1 += parseInt(col1.childNodes[j].value)
                     }
                }
             }
            
             for (j = 0; j < col2.childNodes.length; j++)
             {
                if (col2.childNodes[j].type == "text")
                 {
                     if(!isNaN(col2.childNodes[j].value) &&  col2.childNodes[j].value != "")
                     {
                        totalcol2 += parseInt(col2.childNodes[j].value)
                     }
                }
            }
        }
        document.getElementById('<%=txttotal1.ClientID %>').value = totalcol1;
        document.getElementById('<%=txttotal2.ClientID %>').value = totalcol2;
    }
    

Step3 – Call this javascript function on OnRowDataBound on csharp code

 protected void GridView1_RowDataBound(object sender, GridViewRowEventArgs e)
    {
        if (e.Row.RowType == DataControlRowType.DataRow)
        {
            TextBox txt = (TextBox)e.Row.FindControl("txtcol1");
            txt.Attributes.Add("onBlur", "JvfunonBlur();");

            TextBox txt1 = (TextBox)e.Row.FindControl("txtcol2");
            txt1.Attributes.Add("onBlur", "JvfunonBlur();");
        
        }

Whole Code At Once

.Aspx Page

<%@ Page Language="C#" AutoEventWireup="true"  CodeFile="Default.aspx.cs" Inherits="_Default" %>

<!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>Untitled Page</title>
    <script type="text/javascript">
    function JvfunonBlur()
    {
      var grid = document.getElementById('<%=GridView1.ClientID %>');
        var col1;
        var totalcol1 = 0;

        var col2;
        var totalcol2 = 0; 
        
          
        for (i = 0; i < grid.rows.length; i++) 
        {
            col1 = grid.rows[i].cells[0];
            col2 = grid.rows[i].cells[1];
            
             for (j = 0; j < col1.childNodes.length; j++) 
             {
                if (col1.childNodes[j].type == "text")
                 {
                 if(!isNaN(col1.childNodes[j].value) &&  col1.childNodes[j].value != "")
                     {
                        totalcol1 += parseInt(col1.childNodes[j].value)
                     }
                }
             }
            
             for (j = 0; j < col2.childNodes.length; j++)
             {
                if (col2.childNodes[j].type == "text")
                 {
                     if(!isNaN(col2.childNodes[j].value) &&  col2.childNodes[j].value != "")
                     {
                        totalcol2 += parseInt(col2.childNodes[j].value)
                     }
                }
            }
        }
        document.getElementById('<%=txttotal1.ClientID %>').value = totalcol1;
        document.getElementById('<%=txttotal2.ClientID %>').value = totalcol2;
    }
    
    
    </script>
</head>
<body>
    <form id="form1" runat="server">
    <div>
    
        <asp:ScriptManager ID="ScriptManager1" runat="server">
        </asp:ScriptManager>
    

    <asp:UpdatePanel ID="UpdatePanel1" runat="server">
    <ContentTemplate>
    
        <asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="false" 
            onrowdatabound="GridView1_RowDataBound">
        <Columns>
        
 <asp:TemplateField HeaderText="col1">
<ItemTemplate>
<asp:TextBox ID="txtcol1" Text='<%#Eval("id1") %>' runat="server">
</asp:TextBox>
 </ItemTemplate>
 </asp:TemplateField>
 
  <asp:TemplateField HeaderText="col2">
<ItemTemplate>
<asp:TextBox ID="txtcol2" Text='<%#Eval("id2") %>' runat="server">
</asp:TextBox>
 </ItemTemplate>
 </asp:TemplateField>
 
        </Columns>
        </asp:GridView>
       
        Total
         <br />
        <asp:TextBox ID="txttotal1" runat="server"></asp:TextBox>
        <asp:TextBox ID="txttotal2" runat="server"></asp:TextBox>
        <br />
        
    </ContentTemplate>
    </asp:UpdatePanel>
        </div>
    </form>
</body>
</html>

.C# Code

using System;
using System.Configuration;
using System.Data;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Xml.Linq;

public partial class _Default : System.Web.UI.Page 
{
    protected void Page_Load(object sender, EventArgs e)
    {
        if (!Page.IsPostBack)
        {
            FillGrid();
        }

    }
    private void FillGrid()
    {
        DataTable dt = new DataTable();
        dt.Columns.Add("id1", typeof(int));
        dt.Columns.Add("id2", typeof(int));
        dt.Rows.Add(0, 0);
        dt.Rows.Add(0, 0);

        GridView1.DataSource = dt;
        GridView1.DataBind();
        
    }
    protected void GridView1_RowDataBound(object sender, GridViewRowEventArgs e)
    {
        if (e.Row.RowType == DataControlRowType.DataRow)
        {
            TextBox txt = (TextBox)e.Row.FindControl("txtcol1");
            txt.Attributes.Add("onBlur", "JvfunonBlur();");

            TextBox txt1 = (TextBox)e.Row.FindControl("txtcol2");
            txt1.Attributes.Add("onBlur", "JvfunonBlur();");
        
        }
    }
}

Advertisements
Posted in Asp.Net, Grid View, JavaScript | 7 Comments

Transaction in Ado.Net

Transaction is the collection of Commands. Transaction work on nothing or All function.

Means if a command fails then all command fails .

Steps to Put Transaction in Ado.net
Setp1 : create the sqlTransaction object
Step 2: Define the Connection property. And set the BeginTransaction() function with Connection object.
Step3: write the Sql Commands
Step4: pass transaction object in command()
Step5: commit()/Rollback Transaction
For Example

//create your connection property first
SqlTransaction st;
SqlCommand cmd;
con.Open();
st = con.BeginTransaction();
try
{
cmd = new SqlCommand("Insert into A (id,Fname) values (1,'aashish')", con,st);
cmd.ExecuteNonQuery();
cmd = new SqlCommand("Insert into B (id1,Lname) values (1,'aashish')", con,st);
cmd.ExecuteNonQuery();
 st.Commit();
}
catch
{
 st.Rollback();
}


Posted in Asp.Net, Sql | Leave a comment

Age Calculate using Sql Query(Age Function in sql)

Step1 – Create Function fn_GetAge

 

 


set ANSI_NULLS ON

set QUOTED_IDENTIFIER ON

go

&nbsp;

ALTER FUNCTION [dbo].[fn_GetAge]

(@DOB as datetime, @now as datetime)

returns int

as

BEGIN

DECLARE @age INT

IF(@DOB IS NOT NULL)

BEGIN

SELECT @age =

CASE WHEN MONTH(@DOB) &gt; MONTH(@now)

THEN DATEDIFF(YYYY,@DOB,@now)-1

WHEN MONTH(@DOB) &lt; MONTH(@now)

THEN DATEDIFF(YYYY,@DOB, @now)

WHEN MONTH(@DOB) = MONTH(@now)

THEN

CASE WHEN DAY(@DOB) &gt; DAY(@now)

THEN DATEDIFF(YYYY, @DOB, @now) - 1

ELSE

DATEDIFF(YYYY,@DOB, @now)

END

END

END

ELSE

SELECT @age = 0

RETURN @age

END

 

 

 

Step 2: Call this Functin

 


select dbo.[fn_GetAge]('1946-05-05','2011-03-13')

 

Posted in Sql | Leave a comment

Execute Store procedure form Csharp (System.Diagnostics.Process)

Step1 :   Create a Query for creating a table and name this query Proc.sql and store it into C Drive

if object_Id('test_table')is not null drop table test_table
create table test_table
(
id int,
[Name] nvarchar(30)
)

 

 

Step2 :    in .CS file copy and paste this code

//change your dbname

ViewState["dbname"] = "yourdbname";
string ServerName = System.Environment.MachineName;
string CommandLineStringInfo = "-S " + ServerName + " -d " + ViewState["dbname"].ToString() + " -i C:\\proc.sql -o C:\\proc.Log";
System.Diagnostics.ProcessStartInfo myInfoInfo = new System.Diagnostics.ProcessStartInfo("sqlCMD", CommandLineStringInfo);
myInfoInfo.CreateNoWindow = true;
myInfoInfo.WindowStyle = ProcessWindowStyle.Hidden;
System.Diagnostics.Process.Start(myInfoInfo);
Process p = new Process();
p.StartInfo = myInfoInfo;
p.Start();
p.WaitForExit();

 

 

Step 3: For testing check if table created or not

Run this query

use [yourdbname]
select * from test_table
Posted in Asp.Net, Sql | 1 Comment

trigger in sql(after trigger)

Update one Table after insert or update data into another Table

 

here We Have Two Tables

table1 and table2,and we want to udpate table2 using after trigger

 

Step1

create table1

Create table table1

(

Id1 int Identity  (1,1) Not Null,

EmpCode1 nvarchar(10),

Name1 nvarchar(20)

)

 

 

Step2

create table2

Create table table2

(

Id2 int Identity  (1,1) Not Null,

EmpCode2 nvarchar(10),

Name2 nvarchar(20)

)

 

Step3

create After trigger on table1

set ANSI_NULLS ON

set QUOTED_IDENTIFIER ON

go

Create trigger [updatetable2]

on [dbo].[table1]

After insert,update

As

Begin

Set NoCount on

Declare @EmpCode nvarchar(50)

Declare @name nvarchar(50)

set @EmpCode = (select [EmpCode1] from inserted)

set @name = (select [Name1] from inserted)

if not exists (select * from table2 where [EmpCode2] = @EmpCode)

begin

insert into  table2 ([EmpCode2],[Name2])values (@EmpCode,@name)

end

else

begin

update table2 set [Name2] = @name where [EmpCode2] = @EmpCode

end

end

 

 

Step4

insert data into table1 for testing and show the result

insert into table1 (EmpCode1,Name1)values ('001','Aashish')

select * from table1

select * from table2

 

 

Step5

update data into table1 for testing and show affect on table2

update table1 set Name1 = 'Dynamic' where EmpCode1 = '001'

select * from table1

select * from table2
Posted in Sql | Leave a comment

update data from one table to another using join

— update data from one table to another using join

Update t1 set t1.Amount = t2.NetAmount from EmpSalary t1
inner join EmpMonthlySalary t2 on t1.EmpCode = t2.EmpCode
Posted in Sql | Leave a comment

cursor in sql

–Declare Cursor

Declare  FetchEmpCodeonebyone
cursor for
select EmpCode from empmaster
Declare @EmpCode nvarchar(20)
open FetchEmpCodeonebyone
Fetch Next from FetchEmpCodeonebyone into @EmpCode
while (@@Fetch_status = 0)
begin
--your logic start
select @EmpCode
-- your logic end
Fetch Next from FetchEmpCodeonebyone into @EmpCode
end
--Close and Deallocate Cursor
Close FetchEmpCodeonebyone
Deallocate FetchEmpCodeonebyone
Posted in Sql | Leave a comment