Click here to Skip to main content
15,031,495 members
Please Sign up or sign in to vote.
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
Updated 3-Apr-18 20:28pm
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

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].
   
Thanx Christiaan it worked l was able to create a view
   
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".

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