0

Hello every one i have 2 tables in ms access i just want to ask what is the code for subtracting the BALANCE-Amount Paid?

(Table1)Loan_Information

  1. Date_Today
  2. ID_No
  3. FirstName
  4. LastName
  5. Loan_Amount
  6. Interest
  7. Due
  8. Balance

(Table2)Payment

  1. ID_No
  2. FirstName
  3. LastName
  4. Amount Paid

Here's The Code that i tried. it only saving the paymentinfo but doesn`t subtract the Balance in (Table_1) Loan_Information 

ds = New DataSet
adapter = New OleDbDataAdapter("insert into [Payment] ([Date_Today], [ID_No], [FirstName], [LastName], [Amount_Paid]) VALUES " & "('" & txtPaymentDate.Text & "','" & txtPaymentIDNo.Text & "','" & txtPaymentFirstName.Text & "','" & txtPaymentLastName.Text & "','" & txtPaymentAmount.Text & "')", conn)
adapter.Fill(ds, "Payment")


ds2 = New DataSet
adapter = New OleDbDataAdapter("update [Loan_Information] set Balance = Balance - '" & txtPaymentAmount.Text & "' where ID_No = " & txtIDNo.Text, conn)
adapter.Fill(ds2, "Loan_Information")
Igor
  • 60,821
  • 10
  • 100
  • 175
jwick019
  • 7
  • 3
  • 1
    **Always use parameterized sql and avoid string concatenation** to add values to sql statements. See [How can I add user-supplied input to an SQL statement?](https://stackoverflow.com/q/35163361/1260204), and [Exploits of a Mom](https://xkcd.com/327/). – Igor Aug 22 '19 at 15:29
  • Remember that for access (ole) you need to use `?` as place holders and parameters are position dependent (array position must match location in the statement). Use parameters and your "issue" will go away. – Igor Aug 22 '19 at 15:30
  • Okay, Thank you so much i'll try the reference that you given. – jwick019 Aug 22 '19 at 15:39
  • 1
    @Igor You can use named parameters on the `VALUES()` side, too. Only the position matters. – Jimi Aug 22 '19 at 20:14
  • @Igor Your link uses `.AddWithValue` I believe the `.Add` method with datatype and size is preferred. – Mary Aug 25 '19 at 16:41
  • @Igor Also, ditto on Jimi's comment. Named parameters are much easier to read and spot an error in position than a string of ?. – Mary Aug 25 '19 at 16:43

0 Answers0