Home > Mobile >  How to calculate expiration date with vb.net and SQL Server using Visual Studio 2019 and SQLSERVER E
How to calculate expiration date with vb.net and SQL Server using Visual Studio 2019 and SQLSERVER E

Time:11-23

I'm trying to display on a DataGridView Cell, a value to represent the remaing days between two dates and i'm using DateDiff but it returns an empty cell and i don't know what i'm missing or doing wrong. My Code is this, please help.

Thank you.


Private Sub ValidadeLicencas_Load(sender As Object, e As EventArgs) Handles Me.Load

    Conectar()

    Dim cmd As New SqlCommand("SELECT AlvaraNumero,NomeEmpresa,ContactoEmpresa,ValidadeLicenca FROM EmissaoLicenca", conexoes)

    Dim da As New SqlDataAdapter
    da.SelectCommand = cmd
    Dim dt As New DataTable
    dt.Clear()
    da.Fill(dt)
    DataGridViewValidade.DataSource = dt
    conexoes.Close()

    DataGridViewValidade.AllowUserToAddRows = False

    Dim ColDias As New DataGridViewTextBoxColumn
    ColDias.Name = "drestantes"
    ColDias.HeaderText = "Dias Restantes"
    ColDias.Width = 150
    DataGridViewValidade.Columns.Insert(4, ColDias)

    For Each row As DataGridViewRow In DataGridViewValidade.Rows
        Dim expdata_v As Date = row.Cells("ValidadeLicenca").Value
        Dim calcdias_v As Integer = DateDiff(DateInterval.Year, Date.Now, expdata_v)
        row.Cells("drestantes").Value = calcdias_v
    Next

End Sub

The result should be something like this:

AlvaraNumero NomeEmpresa ContactoEmpresa ValidadeLicenca Dias Restantes
AlvaraNumero1 NomeEmpresa1 ContactoEmpresa1 30/11/2022 8
AlvaraNumero2 NomeEmpresa2 ContactoEmpresa2 25/11/2022 3

But i get this instead.

AlvaraNumero NomeEmpresa ContactoEmpresa ValidadeLicenca Dias Restantes
AlvaraNumero1 NomeEmpresa1 ContactoEmpresa1 30/11/2022
AlvaraNumero2 NomeEmpresa2 ContactoEmpresa2 25/11/2022

CodePudding user response:

It would be easier to add the date difference calculation into the SQL query.

SELECT AlvaraNumero,NomeEmpresa,ContactoEmpresa,ValidadeLicenca, DATEDIFF(YEAR, ValidadeLicenca , GETDATE()) [drestantes] FROM EmissaoLicenca
  • Related