accumulate total in sql

[swathys]

hi,

I need to knw on how to accumulate the total amount in sql table. I have created a table below but it does not calculate accurately. By mean it just total up current top up amount with the 1st value of grand total.
How do i total up to get the accurate figure.

ID Amount GrandTotal
99 1000 1000
99 500 1500
99 300 1300
99 200 1200

Please help

[kvprajapati]

>accumulate total in sql

You need to write Stored-Procedure. Read this article - http://www.sqlteam.com/article/calculating-running-totals

[swathys]

hi
below is my code but i have no idea where to perform the calculation. hope that you could help me to solve the problem.

form1

Private Sub frmCurrentBalance_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        Dim inData As String

        inData = xmlTDES.DecryptData(encAmt)
        lblTopUp.Text = Mid(inData, 11, Len(inData) - 10)
     
        topUpAmt = Val(lblTopUp.Text) + Val(lblBalance.Text)
        lblBalance.Text = topUpAmt + GetGrandTotal(Form1.BoothID.Text)
        Update_RetailerData()
        Update_AccumulatedTotal()
        Update_TotalBalance()

         End Sub

=======================================================================================

module

Public Function Update_TotalBalance() As Boolean
        Dim myConnection As SqlConnection
        Dim myCommand As SqlCommand
        Dim strSQL As String = ""
        Dim connStr As String

        connStr = "server=(local); database=Credit; Trusted_Connection=yes"

        myConnection = New SqlConnection(connStr)

        Try

        strSQL = "INSERT INTO dbo.RetailerBalance([BoothID], [GrandTotal]) VALUES ('" & Form1.txtBoothID.Text & "','" & frmCurrentBalance.lblBalance.Text & "')"

        If myConnection.State = 0 Then
            myConnection.Open()
        End If


         myCommand = New SqlCommand(strSQL, myConnection)
        myCommand.ExecuteNonQuery()
        myCommand.CommandText = strSQL


        Catch e As Exception
            MsgBox(Err.Description)
        End Try

        myConnection.Close()

    End Function