Calculate percentage based on other column value using C# and VB.Net in ASP.Net

Last Reply on Mar 06, 2017 08:04 AM By AnandM

Posted on Mar 06, 2017 01:58 AM

Hi,

I have three columns

Op1   Op2  Op3

4        2     2

6        2      3

I want to divide the vaues of Op1/Op2 and display the result in Op3. Any advice please.

And also if possible how do i do this

Op1   Op2  Op3 

2        4      50%

2        8     25%

Posted on Mar 06, 2017 06:14 AM

Hi Vikash21,

Its Possible you need to just bind the updated data once the data is inserted.

For eg.If you enter the new values for Op1 and Op2 then you have written code on Save Click Button then you need to bind the gridview where it will display the latest updated gridview data which gets binded from Database.


Posted on Mar 06, 2017 08:04 AM Modified on on Mar 06, 2017 08:06 AM

Hi Vikash21,

I have created a sample which full fill your requirement you need to modify the code according to your need.

SQL

CREATE TABLE Data(ID INT IDENTITY(1,1), Op1 FLOAT,Op2 FLOAT)
INSERT INTO Data VALUES(4,2)
INSERT INTO Data VALUES(6,2)

HTML

<div>
    <asp:GridView ID="gvData" AutoGenerateColumns="false" AutoGenerateEditButton="true"
        OnRowEditing="Edit" OnRowCancelingEdit="Cancel" OnRowUpdating="Update" runat="server">
        <Columns>
            <asp:TemplateField HeaderText="ID">
                <ItemTemplate>
                    <asp:Label ID="lblID" Text='<%# Eval("ID") %>' runat="server" />
                </ItemTemplate>
            </asp:TemplateField>
            <asp:TemplateField HeaderText="Op1">
                <ItemTemplate>
                    <asp:Label ID="lblOp1" Text='<%#Eval("Op1") %>' runat="server" />
                </ItemTemplate>
                <EditItemTemplate>
                    <asp:TextBox ID="txtOp1" Text='<%#Eval("Op1") %>' runat="server" />
                </EditItemTemplate>
            </asp:TemplateField>
            <asp:TemplateField HeaderText="Op2">
                <ItemTemplate>
                    <asp:Label ID="lblOp2" Text='<%# Eval("Op2") %>' runat="server" />
                </ItemTemplate>
                <EditItemTemplate>
                    <asp:TextBox ID="txtOp2" Text='<%# Eval("Op2") %>' runat="server" />
                </EditItemTemplate>
            </asp:TemplateField>
            <asp:TemplateField HeaderText="Op3">
                <ItemTemplate>
                    <asp:Label Text='<%# Eval("Op3") %>' runat="server" />
                </ItemTemplate>
            </asp:TemplateField>
        </Columns>
    </asp:GridView>
    <table border="0" cellpadding="0" cellspacing="0">
        <tr>
            <td>
                Op1:
                <asp:TextBox ID="txtOP1" runat="server" />
            </td>
            <td>
                Op2:
                <asp:TextBox ID="txtOP2" runat="server" />
            </td>
            <td>
                <asp:Button ID="btnSave" Text="Save" OnClick="Save" runat="server" />
            </td>
        </tr>
    </table>
</div>

C#

string constr = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
protected void Page_Load(object sender, EventArgs e)
{
    if (!this.IsPostBack)
    {
        BindGrid();
    }
}    

protected void Edit(object sender, GridViewEditEventArgs e)
{
    gvData.EditIndex = e.NewEditIndex;
    BindGrid();
}

protected void Cancel(object sender, GridViewCancelEditEventArgs e)
{
    gvData.EditIndex = -1;
    BindGrid();
}

protected void Update(object sender, GridViewUpdateEventArgs e)
{
    GridViewRow row = gvData.Rows[e.RowIndex];
    Label lblID = row.FindControl("lblID") as Label;
    TextBox txtOP1 = row.FindControl("txtOP1") as TextBox;
    TextBox txtOP2 = row.FindControl("txtOP2") as TextBox;
    string query = "UPDATE Data SET Op1 = @Op1,Op2 = @Op2 WHERE ID = @ID";
    using (SqlConnection con = new SqlConnection(constr))
    {
        using (SqlCommand cmd = new SqlCommand(query, con))
        {
            con.Open();
            cmd.Parameters.AddWithValue("@Op1", Convert.ToDecimal(txtOP1.Text.Trim()));
            cmd.Parameters.AddWithValue("@Op2", Convert.ToDecimal(txtOP2.Text.Trim()));
            cmd.Parameters.AddWithValue("@ID", lblID.Text);
            cmd.ExecuteNonQuery();
            con.Close();
        }
    }
    gvData.EditIndex = -1;
    BindGrid();
}

protected void Save(object sender, EventArgs e)
{
    string query = "INSERT INTO Data VALUES(@Op1,@Op2)";
    using (SqlConnection con = new SqlConnection(constr))
    {
        using (SqlCommand cmd = new SqlCommand(query, con))
        {
            con.Open();
            cmd.Parameters.AddWithValue("@Op1", Convert.ToDecimal(txtOP1.Text.Trim()));
            cmd.Parameters.AddWithValue("@Op2", Convert.ToDecimal(txtOP2.Text.Trim()));
            cmd.ExecuteNonQuery();
            BindGrid();
            con.Close();
        }
    }
}

public void BindGrid()
{
    string query = "SELECT ID,Op1,Op2,CONVERT(VARCHAR(10),(Op2*100/Op1 ))+'%' AS Op3 FROM Data";
    using (SqlConnection con = new SqlConnection(constr))
    {
        using (SqlCommand cmd = new SqlCommand(query, con))
        {
            using (SqlDataAdapter sda = new SqlDataAdapter(cmd))
            {
                using (DataTable dt = new DataTable())
                {
                    sda.Fill(dt);
                    gvData.DataSource = dt;
                    gvData.DataBind();
                }
            }
        }
    }
}

VB.Net

Private constr As String = ConfigurationManager.ConnectionStrings("constr").ConnectionString
Protected Sub Page_Load(sender As Object, e As EventArgs)
	If Not Me.IsPostBack Then
		BindGrid()
	End If
End Sub

Protected Sub Edit(sender As Object, e As GridViewEditEventArgs)
	gvData.EditIndex = e.NewEditIndex
	BindGrid()
End Sub

Protected Sub Cancel(sender As Object, e As GridViewCancelEditEventArgs)
	gvData.EditIndex = -1
	BindGrid()
End Sub

Protected Sub Update(sender As Object, e As GridViewUpdateEventArgs)
	Dim row As GridViewRow = gvData.Rows(e.RowIndex)
	Dim lblID As Label = TryCast(row.FindControl("lblID"), Label)
	Dim txtOP1 As TextBox = TryCast(row.FindControl("txtOP1"), TextBox)
	Dim txtOP2 As TextBox = TryCast(row.FindControl("txtOP2"), TextBox)
	Dim query As String = "UPDATE Data SET Op1 = @Op1,Op2 = @Op2 WHERE ID = @ID"
	Using con As New SqlConnection(constr)
		Using cmd As New SqlCommand(query, con)
			con.Open()
			cmd.Parameters.AddWithValue("@Op1", Convert.ToDecimal(txtOP1.Text.Trim()))
			cmd.Parameters.AddWithValue("@Op2", Convert.ToDecimal(txtOP2.Text.Trim()))
			cmd.Parameters.AddWithValue("@ID", lblID.Text)
			cmd.ExecuteNonQuery()
			con.Close()
		End Using
	End Using
	gvData.EditIndex = -1
	BindGrid()
End Sub

Protected Sub Save(sender As Object, e As EventArgs)
	Dim query As String = "INSERT INTO Data VALUES(@Op1,@Op2)"
	Using con As New SqlConnection(constr)
		Using cmd As New SqlCommand(query, con)
			con.Open()
			cmd.Parameters.AddWithValue("@Op1", Convert.ToDecimal(txtOP1.Text.Trim()))
			cmd.Parameters.AddWithValue("@Op2", Convert.ToDecimal(txtOP2.Text.Trim()))
			cmd.ExecuteNonQuery()
			BindGrid()
			con.Close()
		End Using
	End Using
End Sub

Public Sub BindGrid()
	Dim query As String = "SELECT ID,Op1,Op2,CONVERT(VARCHAR(10),(Op2*100/Op1 ))+'%' AS Op3 FROM Data"
	Using con As New SqlConnection(constr)
		Using cmd As New SqlCommand(query, con)
			Using sda As New SqlDataAdapter(cmd)
				Using dt As New DataTable()
					sda.Fill(dt)
					gvData.DataSource = dt
					gvData.DataBind()
				End Using
			End Using
		End Using
	End Using
End Sub

ScreenShot