SQLTeam.com | Weblogs | Forums

Looping issue

I am using the below code in my VB program.

While I understand this is not a VB forum I need to understand how I loop through the SQL to read each row (currently there are only three rows) and read the data as shown below then alert if the criteria is met. Currently it is only reading the 1st row then exiting - with no errors.

I'm trying to figure out how looping in SQL works. And assistance.

Private Sub MedCertExpire()
Dim cmd As New SqlCommand
Dim reader As SqlDataReader

    cmd.CommandType = CommandType.Text
    cmd.Connection = New SqlConnection With {
        .ConnectionString = My.MySettings.Default.PETS_DatabaseConnectionString
    }
    cmd.Connection.Open()

    cmd.CommandText = "SELECT EMPLOYEE_FIRST_NAME 
                            , DATEDIFF(DAY, GETDATE(), PHYSICAL_DATE) AS PHYSICAL_EXPIRES
                            , DATEDIFF(DAY, GETDATE(), CDL_EXPIRE_DATE) AS CDL_EXPIRES
                            , DATEDIFF(DAY, GETDATE(), TWIC_EXPIRES) AS TWIC_EXPIRES
                            , DATEDIFF(DAY, GETDATE(), PASSPORT_EXPIRES) AS PASSPORT_EXPIRES
                            , DATEDIFF(DAY, GETDATE(), ANNUAL_REVIEW_DATE) AS ANNUAL_REVIEW
                         FROM EMPLOYEE_TABLE"

    Try
        reader = cmd.ExecuteReader()
        Dim yName As String
        Dim yMedCert As Integer
        Dim yCDL As Integer
        Dim yTWIC As Integer
        Dim yPass As Integer
        Dim yRev As Integer

        With reader
            .Read()

            yName = .GetString(0)
            yMedCert = .GetInt32(1)
            yCDL = .GetInt32(2)
            yTWIC = .GetInt32(3)
            yPass = .GetInt32(4)
            yRev = .GetInt32(5)

            Select Case yMedCert
                Case Is <= 0
                    Call New MessageBoxMedCertExp(yName, yMedCert).ShowDialog()
                Case Is <= 30
                    Call New MessageBoxMedCert(yName, yMedCert).ShowDialog()
            End Select

            Select Case yCDL
                Case Is <= 0
                    Call New MessageBoxCDLExp(yName, yCDL).ShowDialog()
                Case Is <= 30
                    Call New MessageBoxCDL(yName, yCDL).ShowDialog()
            End Select

            Select Case yTWIC
                Case Is <= 0
                    Call New MessageBoxTWICExp(yName, yTWIC).ShowDialog()
                Case Is <= 30
                    Call New MessageBoxTWIC(yName, yTWIC).ShowDialog()
            End Select

            Select Case yPass
                Case Is <= 0
                    Call New MessageBoxPassExp(yName, yPass).ShowDialog()
                Case Is <= 30
                    Call New MessageBoxPassport(yName, yPass).ShowDialog()
            End Select

            Select Case yRev
                Case Is <= 0
                    Call New MessageBoxEmployeeReviewEXP(yName, yRev).ShowDialog()
                Case Is <= 30
                    Call New MessageBoxEmployeeReview(yName, yRev).ShowDialog()
            End Select

            Return
        End With

        reader.Close()
    Catch ex As Exception
        MsgBox(ex)
    End Try

    cmd.Connection.Close()
End Sub

I dont see any loop in this code.
This logic seems odd

Case Is <= 0
 Case Is <= 30

-1 is both less than 0 and less than 30.

Logic by chance?

There is no loop and this is what I am trying to nail down how to do.

The check is to see if the date is less than or equal to zero - if it is then show a specific message box.... if it is within 0 to 30 days either side of the of the date it will alert with either an item is coming due or is now past due.

In plain English, if the current date is within 30 days of the date read, but has not went beyond, then show an alert that something is due within 30 days (or less). If the date read is already past then alert with the secondary alert that the due date has passed x days ago. The code works as intended with the exception that I am not looping through each record. It sees and reacts on the first record properly but then moves and never checks the 2nd and later records for the same.

Example: Today's date is currently 04-11-2022. If the data would be read an alert box comes up stating something is due today. If we were a week prior to the above listed date the alert box comes up and notes the item is due in 7 days. Tomorrow then the same record would be read stating the item due is now past-due by 1 day and so on.

while reader.Read()
...
end while

I apologize - I had resolved the issue prior and forgot I had posted this here... FYI @jacroberts - your answer was the correct solution.