Click here to Skip to main content
14,734,026 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
i have 1 column name amt. and the data are like thes(120,200,400,600 etc)

now on this column i want to perform sum function. so whatever value i get on that i need to calculate 15% percenatge which will be renamed as premium and then i want to display it datagridview showing the amt,totalamt and premium in datagridview.

e.g if amt column total is 10000 ,thn 15% on 10000=1500(this is premium)

so in datagridview it will show as
amt totalamt premium fulltotal
120 10000 1500 11500
200
400
600

Can anyone please help me.

its urgent, please i request you all.

thanks in advance
Posted
Comments
sudeshna from bangkok 1-Aug-13 2:08am
   
Dim cn As New SqlConnection("Data Source=.\INSTANCE;initial catalog=stamp;user=sa;password=gariahat")
Dim da As New SqlDataAdapter
Dim cmd As New SqlCommand
Dim ds As New DataSet
'Dim dr As SqlDataReader

Dim tb1 As DataTable
Try
cmd.Connection = cn
cn.Open()
da = New SqlDataAdapter("select broker_id,name,amt,sum(amt) as Total_lots, 0.15* total_lots as premium order by broker_id,lot_no ", cn)
tb1 = New DataTable
da.Fill(tb1)
itemDataGridView.DataSource = tb1

cn.Close()



Catch ex As Exception
MsgBox(ex.Message)
End Try
sudeshna from bangkok 1-Aug-13 2:09am
   
this si my code and i know its wrong, this is not the way. so i want the correct way how to do it

1 solution

For this problem solved by using RowDataBound Event of gridView

<asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="true" OnRowDataBound="CustomersGridView_RowDataBound">
 </asp:GridView>



c# page code

DataTable dt = new DataTable();
    protected void Page_Load(object sender, EventArgs e)
    {
        dt.Columns.Add("amt", typeof(Int32));
        dt.Columns.Add("pamt", typeof(Int32));
        dt.Columns.Add("damt", typeof(Int32));
        DataRow dr=dt.NewRow();
        dr[0] = 200;
        dt.Rows.Add(dr);
        DataRow dr1 = dt.NewRow();
        dr1[0] = 400;
        dt.Rows.Add(dr1);
        GridView1.DataSource = dt;
        GridView1.DataBind();
    }
   protected void CustomersGridView_RowDataBound(Object sender, GridViewRowEventArgs e)
    {

        if (e.Row.RowType == DataControlRowType.DataRow)
        {
            // Display the company name in italics.
            e.Row.Cells[1].Text =Convert.ToString(Convert.ToInt32(e.Row.Cells[0].Text)*15/100);
            e.Row.Cells[2].Text = Convert.ToString(Convert.ToInt32(e.Row.Cells[1].Text) * 15);
        }

    }
   
v2
Comments
sudeshna from bangkok 1-Aug-13 3:17am
   
can you please give me the code in vb.net?
sudeshna from bangkok 1-Aug-13 3:18am
   
and the values for the amt column is inserted at runtime, so those values have to be fetched from database. in my code, i cant define the values of the amt column
Vandana_Pathak 1-Aug-13 4:57am
   
da = New SqlDataAdapter("select broker_id,name,amt,sum(amt) as Total_lots, 0.15* total_lots as premium order by broker_id,lot_no ", cn)
Above you give code,can u explain this query is correct in your code.Because this query does not have from clause.If u clear doubt then i can help you and give solution properly
sudeshna from bangkok 1-Aug-13 6:28am
   
oh sorry i was doing some editing and forgot to enter the from clause.

there should be from tablename which is in my case item(table name)
sudeshna from bangkok 1-Aug-13 6:30am
   
select broker_id,name,amt,sum(amt) as Total_lots, 0.15* total_lots as premium from item order by broker_id,lot_no ", cn)

this actually will be the query which i had written but still its wrong, can you help me now?

thanks in advance
Vandana_Pathak 1-Aug-13 7:19am
   
Try this
select broker_id,name,amt,sum(amt) as Total_lots, 0.15* sum(amt) as premium from item group by broker,name,amt order by broker_id,lot_no
OR
select a.*,a.Total_lots * 0.15 from (select broker_id,name,amt,sum(amt) as Total_lots from item group by broker,name,amt order by broker_id,lot_no) a
sudeshna from bangkok 9-Aug-13 1:49am
   
in datagridview it will show as
amt totalamt premium fulltotal
120 10000 1500 11500
200
400
600
sudeshna from bangkok 9-Aug-13 1:51am
   
but in your query its showing as individual sum of a number and premium

its showing like
amt total_lots premium
120 240 36
240 480 72

like this its showing

but i want a query based on my agent id,as many lot_no inserted,that full lot_no total and that total figure 15% should be calculated for premium.

so 1 figure for premium and 1 figure for total_lots

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900