locked
Adding New Amount to Existing Record in user wallet RRS feed

  • Question

  • User-527908287 posted

    I have a user wallet table with user ID and Amount columns where a new record is inserted. And from that, if user makes payment into his or her wallet an update statement will update the current Amount into the new Amount. With this Server side code.

    Code Behind:

    private void UpdateWallet(string email, decimal amount)
    {
        SqlConnection conn = new SqlConnection("Data Source=(LocalDB)\\MSSQLLocalDB;AttachDbFilename=|DataDirectory|\\Dataregister.mdf;Integrated Security=True");
      
        string query = "UPDATE Wallet SET Amount = @amount WHERE Email = @email)";
      
        var cmd = new SqlCommand(query, conn);
        cmd.Parameters.AddWithValue("@email", email);
        cmd.Parameters.AddWithValue("@amount", amount);
        conn.Open();
        cmd.ExecuteNonQuery();
        conn.Close();
    }

    But this will only update the Amount for user having Zero balance. If there is already some amount in the user's wallet, i will need to first fetch the amount then add the new balance to it and update the record. Please how is this server-side code written? To fetch current amount and add new amount and update the record

    Monday, June 8, 2020 10:58 AM

Answers

  • User1535942433 posted

    Hi Donald416,

    Accroding to your description,you could select the existing value then update like this:

     string str = System.Configuration.ConfigurationManager.ConnectionStrings["aspnet-TestApplicationWithDatabase-20190820030542"].ConnectionString;
                SqlConnection con = new SqlConnection(str);
                SqlCommand cmd = new SqlCommand("SELECT Amount FROM Wallet where ", con);
                con.Open();
                SqlDataReader reader = cmd.ExecuteReader();
                reader.Read();
                var x = reader["Amount"].ToString() + @amount;
                string strSql = "update Wallet set Amount ='" + x + "' where  ";
                SqlCommand cmd2 = new SqlCommand(strSql, con);
                cmd2.Parameters.AddWithValue("@amount", amount);
    
                cmd2.ExecuteNonQuery();
                con.Close();

    Best regards,

    Yijing Sun

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, June 9, 2020 7:33 AM

All replies

  • User475983607 posted

    Please how is this server-side code written? To fetch current amount and add new amount and update the record

    I think it is important to point out that you've asked this question several times and the community has answered the question.   The main issue with the problem statement is it does not require code.   Your main job as a programming is to come up with a design, not code, a design to solve the problem.  Get a piece of paper and pencil then list the steps needed to produce an expected outcome.  This part of the software life cycle does not require coding.  This is where you (or your team) get to decide how the application is supposed to work.  

    Secondly, I explained in one of your previous threads on this subject that you should never store a calculated account balance unless it is part of a reconciliation process.  Rather add records with positive and negative amounts for each user.  There are no updates only inserts.  Use a query to SUM the amounts.  

    Monday, June 8, 2020 11:27 AM
  • User-527908287 posted

    .

    Monday, June 8, 2020 12:10 PM
  • User-527908287 posted

    Rather add records with positive and negative amounts for each user

    These Positive and negative amounts, how is it possible to be added on a Table with 2 Columns, "Email" and "Amount" ?

    There are no updates only inserts

    Other programmers think differently. They used update query to enter amount for new users with zero balance and add new SUM new amount with an already existing amount

    Monday, June 8, 2020 1:30 PM
  • User475983607 posted

    These Positive and negative amounts, how is it possible to be added on a Table with 2 Columns, "Email" and "Amount" ?

    that's your design.  I explained a proper design in your previous thread.  The Wallet table should have at least UserId, Amount (+/-), transaction date columns.

    Other programmers think differently. They used update query to enter amount for new users with zero balance and add new SUM new amount with an already existing amount

    I'm not providing an opinion.  I'm explaining how financial systems work and I'm trying to stop you from implementing a very poor design.   In finical systems, new records are always added even when backing out a mistake.  There are never updates to a financial record.  The only time you see a calculated amounts is after a reconciliation which happens at some frequency in time. 

    How would you feel if your bank only showed the total amount in your checking account?  How would you know if a debit cleared?  What if your records do not match the bank's.  How do you figure out if the bank made a mistake or you made a mistake?  

    Monday, June 8, 2020 2:16 PM
  • User-527908287 posted

    mgebhard

    The Wallet table should have at least UserId, Amount (+/-), transaction date columns.

    Which means the Wallet Table will look like this:

    Email_ID    Credit_Amount(+)     Debit_Amount (-)    Credit_Debit   Transact_Date 

    where Email_ID = user ID

    Credit_Amount = positive amount

    Debit_Amount = negative amount

    Transact_Date = Date of Transaction

    And I will write a query that will add positive and negative amount.

    Regarding my design, every financial transaction that goes on can be traced to the dashboard of the payment gateway I want to use. My web application is only required to give value to a user after payment. Every payment comes with a reference number that can be traced to the dashboard of the payment gateway; the dashboard has transaction amount (successful and unsuccessful), transaction date and time, and much more. There will not be direct transactions between my website and user's bank; I am not creating a check out form that will require debit card details to be inputted and charged directly..all transactions goes through a gateway that handles almost everything.

    So as earlier mentioned, I am only required to give value to user after successful payment.

    I dont know if with this, it will still require to SUM positive and negative amounts?

    Monday, June 8, 2020 8:15 PM
  • User475983607 posted

    I would use one column to hold the amounts where debits are negative and credits are positive from the user's perspective.  Then you just SUM one column.

    Donald416

    Regarding my design, every financial transaction that goes on can be traced to the dashboard of the payment gateway I want to use. My web application is only required to give value to a user after payment. Every payment comes with a reference number that can be traced to the dashboard of the payment gateway; the dashboard has transaction amount (successful and unsuccessful), transaction date and time, and much more. There will not be direct transactions between my website and user's bank; I am not creating a check out form that will require debit card details to be inputted and charged directly..all transactions goes through a gateway that handles almost everything.

    Okay.  It's your app and you can design it anyway you like.  It's not what I would do.

    Donald416

    So as earlier mentioned, I am only required to give value to user after successful payment.

    I dont know if with this, it will still require to SUM positive and negative amounts?

    I'm not exactly sure what you are asking.

    The payment gateway should alert you when a transaction clears.  They might send you an email, or call an endpoint in your application, or you query the gateway to get the cleared transactions.   You'll need to read the gateway documentation.  Anyway, this action credits (adds) the user's account.

    It's not clear how an account gets debited but a debit subtracts from the total.

    Monday, June 8, 2020 8:59 PM
  • User1535942433 posted

    Hi Donald416,

    Accroding to your description,you could select the existing value then update like this:

     string str = System.Configuration.ConfigurationManager.ConnectionStrings["aspnet-TestApplicationWithDatabase-20190820030542"].ConnectionString;
                SqlConnection con = new SqlConnection(str);
                SqlCommand cmd = new SqlCommand("SELECT Amount FROM Wallet where ", con);
                con.Open();
                SqlDataReader reader = cmd.ExecuteReader();
                reader.Read();
                var x = reader["Amount"].ToString() + @amount;
                string strSql = "update Wallet set Amount ='" + x + "' where  ";
                SqlCommand cmd2 = new SqlCommand(strSql, con);
                cmd2.Parameters.AddWithValue("@amount", amount);
    
                cmd2.ExecuteNonQuery();
                con.Close();

    Best regards,

    Yijing Sun

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, June 9, 2020 7:33 AM
  • User-527908287 posted

    Thank you mgebhard,

    I hope you understood how my design is ?

    Okay.  It's your app and you can design it anyway you like.  It's not what I would do.

    I totally understand

    The payment gateway should alert you when a transaction clears.  They might send you an email, or call an endpoint in your application, or you query the gateway to get the cleared transactions.

    I already read the payment gateway's documentation. its the endpoint call that requires to give value to a user.

    Thank you for explaining your own way; I am already into it for future plan of building another 

    Tuesday, June 9, 2020 10:00 AM