如何更新月度/年度记录 [英] How to update monthly/annual records

问题描述

好吧，我已经尝试了几天，但我真的不知道代码，我正在寻求帮助。



我的问题：



我正在制作一个程序来收集和更新每个成员捐赠/十分之一的数据。成员每周捐赠，然后数据库表包含每周，每月和每年专栏。现在，我需要知道如何在保持每周记录的同时更新月度和年度列。因为每次我添加新记录时，月度和年度列都不会更新。



代码到目前为止：

Alright, I've been trying for days but I really don't know the code and i'm seeking help.

My problem:

I'm making a program that will collect and update data on each members donation/tithes. Members donate weekly, then the database table has Weekly, Monthly and Annual Column. Now, I need to know how to update the Monthly and Annual column while still keeping the weekly record. Because every time I add a new record the monthly and annual column does not update.

Code so far:

Private Sub btnAddOffertory_Click(sender As Object, e As EventArgs) Handles btnAddOffertory.Click
        cn.Open()
        Using cmd As New SqlClient.SqlCommand("INSERT INTO tblOffertory(ID, Name, Weekly, Date, Monthly, Annual)VALUES(@ID, @Name, @Weekly, @Date, @Monthly, @Annual)", cn)
            cmd.Parameters.Add(New SqlClient.SqlParameter("@ID", SqlDbType.Int)).Value = DGOList.SelectedRows(0).Cells(0).Value
            cmd.Parameters.Add(New SqlClient.SqlParameter("@Name", SqlDbType.VarChar, 50)).Value = DGOList.SelectedRows(0).Cells(1).Value
            cmd.Parameters.Add(New SqlClient.SqlParameter("@Weekly", SqlDbType.Int)).Value = txtOffertory.Text
            cmd.Parameters.Add(New SqlClient.SqlParameter("@Date", SqlDbType.Date)).Value = dtpOffertory.Text
            cmd.Parameters.Add(New SqlClient.SqlParameter("@Monthly", SqlDbType.Int)).Value = 0
            cmd.Parameters.Add(New SqlClient.SqlParameter("@Annual", SqlDbType.BigInt)).Value = 0
            i = cmd.ExecuteNonQuery
        End Using
        If (i > 0) Then
            Using cmd As New SqlClient.SqlCommand("UPDATE tblOffertory SET Monthly += @Monthly WHERE Date = '" & dtpOffertory.Text & "' AND ID = " & DGOList.SelectedRows(0).Cells(0).Value, cn)
                cmd.Parameters.Add(New SqlClient.SqlParameter("@Monthly", SqlDbType.Int)).Value = txtOffertory.Text
            End Using
        End If
        cn.Close()
        ShowORecord()
        OClear()
    End Sub

我的尝试：



我试过这个

What I have tried:

I tried this

Using cmd As New SqlClient.SqlCommand("UPDATE tblOffertory SET Monthly += @Monthly WHERE Date = '" & dtpOffertory.Text & "' AND ID = " & DGOList.SelectedRows(0).Cells(0).Value, cn)
                cmd.Parameters.Add(New SqlClient.SqlParameter("@Monthly", SqlDbType.Int)).Value = txtOffertory.Text
            End Using

推荐答案

最简单的方法是......不要！

忘记每周，每月和年度列：使用DATE和AmountPaid列保留单个列，以及将成员作为外键引用的MemberID。 （我可能也会添加一个ID IDENTITY列，但不是严格要求的。）

然后你可以使用查询来收集你需要的信息：

The easiest way is...not to!
Forget "weekly", "monthly", and "annual" columns: keep a single column with a DATE and a AmountPaid column, plus a MemberID which refers back to the Member as a Foreign key. (I'd probably add an ID IDENTITY column as well, but it's not strictly needed).
Then you can use queries to gather the info you need when you need it:

SELECT MemberID, YEAR(EnterDate) AS [TotYear], SUM(AmountPaid) AS Total FROM [MyTable] GROUP BY MemberID, YEAR(EnterDate)
ORDER BY MemberID, [TotYear] DESC

您可以合并年份，月份，以及使用JOIN的Week版本，可以为您提供单个报告。

You can combine the Year, Month, and Week versions of these with JOIN to give you a single report.

这篇关于如何更新月度/年度记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！