Retrieve value of one column in a table

Question

Here is my problem:

I have 2 tables Accounts and Transaction Logs. in Accounts table, it has column "Amount" which is a base amount of an account. and in Trans Logs table, it also has column "Amount" which is additional (add or minus to the base amount) amount of the account. and I don't know how to retrieve that base amount to edit it, then save it back to the table. That means I need to get a value of the right column by using Acc_No to find. I'm using DataSet, by the way. i think it should go like this:

Dim Amount as Decimal
Amount = *the code to retrieve the base amount*
Amount = Amount + txtAmount.Text
*the code to save the new amount back to Accounts table*

Thank you!

Answer 1

Private Sub UpdateAmount(ByVal Acc_No As Integer, ByVal Amt As Double, ByVal Acc_Type As String)

    'Retrieve columns from accounts table
    Dim cnntStr As String = "Data Source=DUC-91D85F3F8C2\SQLEXPRESS;Initial Catalog=BasicAccounting;Integrated Security=True"
    Dim cn As New SqlConnection(cnntStr)
    cn.Open()
    Dim da As SqlDataAdapter
    Dim ds As New Data.DataSet
    Dim stmt As String = "SELECT Amount, Acc_Type FROM ACCOUNTS WHERE Acc_No = " & Acc_No
    da = New SqlDataAdapter(stmt, cn)
    da.Fill(ds, "ACCOUNTS")

    'To modify the amount
    Dim type As String
    type = ds.Tables(0).Rows(0).Item("Acc_Type")
    Dim amount As Decimal
    amount = ds.Tables(0).Rows(0).Item("Amount")

    If type = Acc_Type Then
        amount = amount + Amt
    ElseIf type <> Acc_Type Then
        amount = amount - Amt
    End If

    'To update the amount
    stmt = "UPDATE ACCOUNTS SET Amount = " & amount & " WHERE Acc_No = " & Acc_No
    da = New SqlDataAdapter(stmt, cn)
    da.Fill(ds, "ACCOUNTS")
    da.Update(ds, "ACCOUNTS")
End Sub