Add Year to current DateTime based on condition using C# and VB.Net in ASP.Net

Last Reply one month ago By pandeyism

Posted one month ago

In the code below I am using an insert statement and before I execute the insert statement I would like to cater for weekends by the NextDueDate field which should be exactly a year in advance. If the NextDueDate is on a Saturday then I want to add 367 days and If it is on a Sunday then I want to add 366 days.

Using command As New SqlCommand("INSERT INTO tblMedicalResults (MedicalCertificateID , ID,	Code, EmployeeID , CostCentre , ExaminationTypeID , PhysicalExamType , AudiogramID , AudiogramRecID , AudiogramDate , LungFunctionID , LungFunctionDate ,	ChestResults , ECGResult , ECGDate , DrugScreeningResult , BloodGlucoseResult ,	GGTResult ,	LeftEyeDayNight , RightEyeDayNight , LeftEyeCorrDayNight ,  RightEyeCorrDayNight , VisualFieldLeftDayNight , VisualFieldRightDayNight ,	ColourVisionDayNight,	DeptPerceptionDayNight ,	OptometristYesNo ,	EyeTestDate  ,	OutcomeID ,	OutcomeRecommendationsID, OtherProblems, Notes ,ExpiryDate , DateTested	, NextDueDate,Photo) VALUES ( @MedicalCertificateID , @ID ,	@Code , @EmployeeID , @CostCentre , @ExaminationTypeID , @PhysicalExamType , @AudioGramID , @AudiogramRecID , @AudioGramDate , @LungFunctionID , @LungFunctionDate ,	@ChestResults , @ECGResult , @ECGDate , @DrugScreeningResult , @BloodGlucoseResult ,	@GGTResult ,	@LeftEyeDayNight , @RightEyeDayNight , @LeftEyeCorrDayNight , @RightEyeCorrDayNight , @VisualFieldLeftDayNight , @VisualFieldRightDayNight ,@ColourVisionDayNight,	@DeptPerceptionDayNight ,	@OptometristYesNo ,	@EyeTestDate  ,	@OutcomeID ,	@OutcomeRecommendationsID , @OtherProblems, @Notes ,@ExpiryDate , @DateTested ,	@NextDueDate,@Photo)", con)
    command.Parameters.AddWithValue("@ExpiryDate", DateTime.Now.AddDays(365))
    command.Parameters.AddWithValue("@DateTested", DateTime.Now())
    command.Parameters.AddWithValue("@Photo", image1.Src)
    command.Parameters.AddWithValue("@NextDueDate", DateTime.Now.AddDays(365)) 'DateTime.Now.AddDays(366) DateTime.Now.AddDays(+10)
    con.Open()
    command.ExecuteNonQuery()
    con.Close()
End Using

 

You are viewing reply posted by: pandeyism one month ago.
Posted one month ago

Hi Tevin,

Refer below sample.

HTML

<asp:Button ID="btnInsert" Text="Insert" runat="server" OnClick="btnInsert_Click" />

Namespaces

C#

using System.Data.SqlClient;
using System.Configuration;

VB.Net

Imports System.Data.SqlClient

Code

C#

protected void btnInsert_Click(object sender, EventArgs e)
{
    string constr = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
    SqlConnection con = new SqlConnection(constr);
    SqlCommand cm = new SqlCommand("INSERT INTO tblMedicalResults(DueDate) VALUES(@DueDate)", con);
    DateTime dueDate = DateTime.Now;
    if (dueDate.DayOfWeek == DayOfWeek.Saturday)
    {
        cm.Parameters.AddWithValue("@DueDate", dueDate.AddDays(367));
    }
    else if (dueDate.DayOfWeek == DayOfWeek.Sunday)
    {
        cm.Parameters.AddWithValue("@DueDate", dueDate.AddDays(366));
    }
    else
    {
        cm.Parameters.AddWithValue("@DueDate", dueDate);
    }
    con.Open();
    cm.ExecuteNonQuery();
    con.Close();
}

VB.Net

Protected Sub btnInsert_Click(ByVal sender As Object, ByVal e As EventArgs)
    Dim constr As String = ConfigurationManager.ConnectionStrings("constr").ConnectionString
    Dim con As SqlConnection = New SqlConnection(constr)
    Dim cm As SqlCommand = New SqlCommand("INSERT INTO tblMedicalResults(DueDate) VALUES(@DueDate)", con)
    Dim dueDate As DateTime = DateTime.Now
    If dueDate.DayOfWeek = DayOfWeek.Saturday Then
        cm.Parameters.AddWithValue("@DueDate", dueDate.AddDays(367))
    ElseIf dueDate.DayOfWeek = DayOfWeek.Sunday Then
        cm.Parameters.AddWithValue("@DueDate", dueDate.AddDays(366))
    Else
        cm.Parameters.AddWithValue("@DueDate", dueDate)
    End If
    con.Open()
    cm.ExecuteNonQuery()
    con.Close()
End Sub