Add Update credit debit value in database using C# and VB.Net in ASP.Net

Hi George616,

Refer below sample code for your reference.

HTML

<asp:TextBox runat="server" ID="txtCreditDebit" />
<asp:Button Text="Make Payment" runat="server" OnClick="OnCredit" />
<asp:Button Text="Get Service" runat="server" OnClick="OnDebit" />

Namespaces

C#

using System.Configuration;
using System.Data;
using System.Data.SqlClient;

VB.Net

Imports System.Configuration
Imports System.Data
Imports System.Data.SqlClient

Code

C#

protected void OnCredit(object sender, EventArgs e)
{
    string email = "xxx@me.com";
    decimal credit = Convert.ToDecimal(GetCreditDebit(email).Rows[0]["credit"]);
    decimal debit = Convert.ToDecimal(GetCreditDebit(email).Rows[0]["debit"]);
    credit = credit + Convert.ToInt64(txtCreditDebit.Text);
    decimal balance = credit - debit;

    using (SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["conString"].ConnectionString))
    {
        string query = "Update CreditDebit SET credit = @credit, balance = @balance WHERE email = @email";
        using (SqlCommand cmd = new SqlCommand(query))
        {
            cmd.Connection = con;
            cmd.Parameters.AddWithValue("@credit", credit);
            cmd.Parameters.AddWithValue("@balance", balance);
            cmd.Parameters.AddWithValue("@email", email);
            con.Open();
            cmd.ExecuteNonQuery();
            con.Close();
        }
    }
}

protected void OnDebit(object sender, EventArgs e)
{
    string email = "xxx@me.com";
    decimal credit = Convert.ToDecimal(GetCreditDebit(email).Rows[0]["credit"]);
    decimal debit = Convert.ToDecimal(GetCreditDebit(email).Rows[0]["debit"]);
    decimal balance = Convert.ToDecimal(GetCreditDebit(email).Rows[0]["balance"]);
    if (balance > Convert.ToDecimal(txtCreditDebit.Text))
    {
        debit = debit + Convert.ToDecimal(txtCreditDebit.Text);
        balance = credit - debit;
        using (SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["conString"].ConnectionString))
        {
            string query = "Update CreditDebit SET debit = @debit, balance = @balance WHERE email = @email";
            using (SqlCommand cmd = new SqlCommand(query))
            {
                cmd.Connection = con;
                cmd.Parameters.AddWithValue("@debit", debit);
                cmd.Parameters.AddWithValue("@balance", balance);
                cmd.Parameters.AddWithValue("@email", email);
                con.Open();
                cmd.ExecuteNonQuery();
                con.Close();
            }
        }
    }
    else
    {
        ClientScript.RegisterClientScriptBlock(this.GetType(), "alert", "alert('Insufficient balance.');", true);
    }
}

private static DataTable GetCreditDebit(string email)
{
    using (SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["conString"].ConnectionString))
    {
        string query = "SELECT email,credit,debit,Balance FROM CreditDebit WHERE email = @email";
        using (SqlCommand cmd = new SqlCommand(query))
        {
            cmd.Connection = con;
            cmd.Parameters.AddWithValue("@email", email);
            SqlDataAdapter sda = new SqlDataAdapter(cmd);
            DataTable dt = new DataTable();
            sda.Fill(dt);

            return dt;
        }
    }
}

VB.Net

Protected Sub OnCredit(ByVal sender As Object, ByVal e As EventArgs)
    Dim email As String = "xxx@me.com"
    Dim credit As Decimal = Convert.ToDecimal(GetCreditDebit(email).Rows(0)("credit"))
    Dim debit As Decimal = Convert.ToDecimal(GetCreditDebit(email).Rows(0)("debit"))
    credit = credit + Convert.ToInt64(txtCreditDebit.Text)
    Dim balance As Decimal = credit - debit
    Using con As SqlConnection = New SqlConnection(ConfigurationManager.ConnectionStrings("conString").ConnectionString)
        Dim query As String = "Update CreditDebit SET credit = @credit, balance = @balance WHERE email = @email"
        Using cmd As SqlCommand = New SqlCommand(query)
            cmd.Connection = con
            cmd.Parameters.AddWithValue("@credit", credit)
            cmd.Parameters.AddWithValue("@balance", balance)
            cmd.Parameters.AddWithValue("@email", email)
            con.Open()
            cmd.ExecuteNonQuery()
            con.Close()
        End Using
    End Using
End Sub

Protected Sub OnDebit(ByVal sender As Object, ByVal e As EventArgs)
    Dim email As String = "xxx@me.com"
    Dim credit As Decimal = Convert.ToDecimal(GetCreditDebit(email).Rows(0)("credit"))
    Dim debit As Decimal = Convert.ToDecimal(GetCreditDebit(email).Rows(0)("debit"))
    Dim balance As Decimal = Convert.ToDecimal(GetCreditDebit(email).Rows(0)("balance"))
    If balance > Convert.ToDecimal(txtCreditDebit.Text) Then
        debit = debit + Convert.ToDecimal(txtCreditDebit.Text)
        balance = credit - debit
        Using con As SqlConnection = New SqlConnection(ConfigurationManager.ConnectionStrings("conString").ConnectionString)
            Dim query As String = "Update CreditDebit SET debit = @debit, balance = @balance WHERE email = @email"
            Using cmd As SqlCommand = New SqlCommand(query)
                cmd.Connection = con
                cmd.Parameters.AddWithValue("@debit", debit)
                cmd.Parameters.AddWithValue("@balance", balance)
                cmd.Parameters.AddWithValue("@email", email)
                con.Open()
                cmd.ExecuteNonQuery()
                con.Close()
            End Using
        End Using
    Else
        ClientScript.RegisterClientScriptBlock(Me.[GetType](), "alert", "alert('Insufficient balance.');", True)
    End If
End Sub

Private Shared Function GetCreditDebit(ByVal email As String) As DataTable
    Using con As SqlConnection = New SqlConnection(ConfigurationManager.ConnectionStrings("conString").ConnectionString)
        Dim query As String = "SELECT email,credit,debit,Balance FROM CreditDebit WHERE email = @email"
        Using cmd As SqlCommand = New SqlCommand(query)
            cmd.Connection = con
            cmd.Parameters.AddWithValue("@email", email)
            Dim sda As SqlDataAdapter = New SqlDataAdapter(cmd)
            Dim dt As DataTable = New DataTable()
            sda.Fill(dt)
            Return dt
        End Using
    End Using
End Function