Sql server:how do l subtract 2 columns and store the result in the next row

Question

0.00/5 (No votes)

See more:

l have a table as follows:
Apply_ID Name Days_Applied Balance(default=12)
1 milton 1 11
2 milton 2 9

When a user(milton) first applies, the balance with a default of 12 days is subtracted by the "Days_Applied" in this case 1 which leaves the balance at 11. When the same user applies again(Apply_ID =2), the Balance of 11 is subtracted by the value of Days_Applied and it becomes 9 and so on.
My problem is that since l made Balance with a default value of 12, the Balance is subtracting the default value 12-Days_Applied and showing results as below:

Apply_ID Name Days_Applied Balance
1 milton 1 11
2 milton 2 10

What I have tried:

l have this sql query:
("UPDATE applications SET Balance = Balance-Days_Applied WHERE Apply_ID = '" & listBoxApplications.SelectedItem & "'")

l am selecting the application from a list box.

Posted 3-Apr-18 3:52am

Member 13760471

Updated 3-Apr-18 20:28pm

Add a Solution

Comments

Richard Deeming 4-Apr-18 12:33pm

Your code is vulnerable to SQL Injection[^]. NEVER use string concatenation to build a SQL query. ALWAYS use a parameterized query.

Everything you wanted to know about SQL injection (but were afraid to ask) | Troy Hunt[^]
How can I explain SQL injection without technical jargon? | Information Security Stack Exchange[^]
Query Parameterization Cheat Sheet | OWASP[^]

Using cmd As New SqlCommand("UPDATE applications SET Balanace = Balance - Days_Applied WHERE Apply_ID = @ApplyID", connection)
    cmd.Parameters.AddWithValue("@ApplyID", listBoxApplications.SelectedItem)
    
    connection.Open()
    cmd.ExecuteNonQuery()
End Using

2 solutions

Solution 2

Thanx Christiaan it worked l was able to create a view

Posted 3-Apr-18 20:29pm

Member 13760471

Comments

Richard Deeming 4-Apr-18 12:33pm

If you want to reply to a solution, click the "Have a Question or Comment?" button under that solution.

DO NOT post your reply as a new "solution".

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

Christiaan van Bergen · Accepted Answer · 2018-04-03T04:11:00

The column Balance is an agregated value. This is not something you normally store in a detail-row. Otherwise, whenever you add or update a single row, you will need to update all rows for (in this case) Milton to show the correct balance.

Why not make a table with applications:

SQL

CREATE TABLE [Applications] (Apply_ID int, [Name] nvarchar(500), Days_Applied int)

Then create a view for the balance or create a seperate table for the balance.

SQL

CREATE VIEW [View_Balance]
AS
select [Name], 12-SUM(Days_Applied) from Applications
Group BY [Name]
GO

Now you can use that view to show the current balance per user. If you choose to have a separate table for the balances, you will need to update that table after each and every insert or update to the table [Applications].