Call method at financial year end date in VB.Net

Hi nirmal90,

You need to check a condition if Date is 31 and Month is March then execute the insert code.

Public Sub CalculateAndInsertBalances()
    ' Check if Date is 31 and Month is March.
    If DateTime.Now.Month = 3 AndAlso DateTime.Now.Day = 31 Then
        If con.State = ConnectionState.Open Or conn.State = ConnectionState.Open Then
            con.Close()
            conn.Close()
        End If
        Try
            con.Open()
            conn.Open()
            Dim cmdString As String = "SELECT CID, CompName, CompAdd, SUM(Debit) - SUM(Credit) as ClosingBalance FROM CompLedger WHERE YEAR(Date1) = @year GROUP BY CID, CompName, CompAdd"
            Dim cmd As New SqlCommand(cmdString, conn)
            cmd.Parameters.AddWithValue("@year", SqlDbType.Int).Value = endYearField.Text
            Dim reader As SqlDataReader = cmd.ExecuteReader()

            ' Insert the closing balance for the current financial year and the opening balance for the next financial year into the FinancialYearBalance table
            If reader.HasRows Then
                While reader.Read()
                    Dim cid As String = reader("CID").ToString()
                    Dim CompName As String = reader("CompName").ToString()
                    Dim CompAdd As String = reader("CompAdd").ToString()
                    Dim closingBalance As Decimal = Convert.ToDecimal(reader("ClosingBalance"))


                    Dim cmdString1 As String = "INSERT INTO BalanceSheet (CID, CompName, CompAdd, Year, OpeningBalance, ClosingBalance) VALUES (@cid, @CompName, @CompAdd, @year, @openingBalance, @closingBalance)"
                    cmd = New SqlCommand(cmdString1, con)
                    cmd.Parameters.AddWithValue("@CID", SqlDbType.NVarChar).Value = cid
                    cmd.Parameters.AddWithValue("@CompName", SqlDbType.NVarChar).Value = CompName
                    cmd.Parameters.AddWithValue("@CompAdd", SqlDbType.NVarChar).Value = CompAdd
                    cmd.Parameters.AddWithValue("@year", SqlDbType.Int).Value = endYearField.Text
                    cmd.Parameters.AddWithValue("@openingBalance", SqlDbType.Decimal).Value = 0 ' Opening balance is NULL for the current financial year
                    cmd.Parameters.AddWithValue("@closingBalance", SqlDbType.Decimal).Value = closingBalance
                    cmd.ExecuteNonQuery()

                    Dim cmdString2 As String = "INSERT INTO BalanceSheet (CID, CompName, CompAdd, Year, OpeningBalance, ClosingBalance) VALUES (@cid, @CompName, @CompAdd, @nextYear, @openingBalance, @closingBalance)"
                    cmd = New SqlCommand(cmdString2, con)
                    cmd.Parameters.AddWithValue("@cid", SqlDbType.NVarChar).Value = cid
                    cmd.Parameters.AddWithValue("@CompName", SqlDbType.NVarChar).Value = CompName
                    cmd.Parameters.AddWithValue("@CompAdd", SqlDbType.NVarChar).Value = CompAdd
                    cmd.Parameters.AddWithValue("@nextYear", SqlDbType.Int).Value = endYearField.Text + 1
                    cmd.Parameters.AddWithValue("@openingBalance", SqlDbType.Decimal).Value = closingBalance ' Opening balance for the next financial year is the closing balance of the current financial year
                    cmd.Parameters.AddWithValue("@closingBalance", SqlDbType.Decimal).Value = 0 ' Closing balance is NULL for the next financial year
                    cmd.ExecuteNonQuery()

                End While
                MessageBox.Show("CID: " & reader("CID").ToString() & vbCrLf & "CompName: " & reader("CompName").ToString() & vbCrLf & "CompAdd: " & reader("CompAdd").ToString() & vbCrLf & "ClosingBalance: " & Convert.ToDecimal(reader("ClosingBalance")).ToString())
            Else
                MessageBox.Show("No data returned from query.")
            End If

            reader.Close()

            con.Close()
            conn.Close()

            MessageBox.Show("Balance Sheet Updated Successfully!", "Table Exists", MessageBoxButtons.OK, MessageBoxIcon.Information)

        Catch ex As Exception
            Cursor.Current = Cursors.Default
            MessageBox.Show("Error " & ex.Message)
        End Try
    End If
End Sub