For Each loop With nested if not running properly. VB.net

Question

I have a sub that on a button click is supposed to query my SQL database pull all the results that match ant then weed out the ones that don't fit user defined values.

dt1 and dt2 are datePickers for the start and end date. the date is stored as a string in the database and parsed on retrieval. I know there is something weird with For each loop and DELETE because of index numbers, but all the work around I have tries the IF THEN statement does not run.

 Private Sub getdata_Click(sender As Object, e As RoutedEventArgs) Handles getdata.Click

    Dim dt As New DataTable
    dt.Dispose()
    SQL.AddParam("@name", emplistadm.Text.ToString)

    SQL.ExecQuery("SELECT EmployeeName, PunchIn, PunchOut, HoursWorked FROM PUNCHES WHERE EmployeeName = @name And CurrentStatus = 0 AND Processed = 0")

    Dim dt1 As DateTime = startdate.DisplayDate
    Dim dt2 As DateTime = enddate.DisplayDate

    Dim dt1int As Int16 = DatePart(DateInterval.DayOfYear, dt1)
    Dim dt2int As Int16 = DatePart(DateInterval.DayOfYear, dt2)

    For Each dr As DataRow In dt.Rows
        Dim dt3 As DateTime = DateTime.Parse(dr("PunchIn"))
        Dim dt3int As Int16 = DatePart(DateInterval.DayOfYear, dt3)
        If dt3int > dt2int AndAlso dt3int < dt1int Then
            dr.Delete()
        End If

    Next



    dt = SQL.SQLDS.Tables(0)
    dataGrid.ItemsSource = dt.DefaultView
End Sub

I know I need to call the accept changes also but when I replace the dr.delete with msgbox("dakjhfdsalkjf") It still doesn't show the message box.

Put Option Strict On at the top of the source code file. Now the compiler tells you what you did wrong. — Hans Passant
– Hans Passant, Commented Oct 6, 2015 at 20:41
Are your dates perhaps spanning years? Your integer fields are "the number of days since the start of the year". If the Start was October 1st, 2001, and the end was January 1st 2002, the dt3int variable could never be between the two values. But, you don't need to do any conversion. You can just compare the date portions using If dt3.Date > dt2.Date AndAlso dt3.Date < dt1.Date Then... — Martin Soles
– Martin Soles, Commented Oct 6, 2015 at 20:44
Dates are always with two months so no it is not a year issue — mediatech15
– mediatech15, Commented Oct 6, 2015 at 21:01
Also if I remove the if then statement and do a msgbox to display the dt1,2,3 into values then it does for each row it has in the dataset — mediatech15
– mediatech15, Commented Oct 6, 2015 at 21:07
Is Start and End Date the range you want to display punch records for? If so, given your control names, to me, the if If dt3int > dt2int AndAlso dt3int < dt1int Then seems to read if punchin > end date to display and punchin < begin date to display so it has to be after and before the display window at the same time? — SeraM
– SeraM, Commented Oct 6, 2015 at 21:15

SeraM · Accepted Answer · 2015-10-06 23:32:39Z

0

Try this (I renamed dt1Int and dt2Int)

Private Sub getdata_Click(sender As Object, e As RoutedEventArgs) Handles getdata.Click

Dim dt As New DataTable
dt.Dispose()
SQL.AddParam("@name", emplistadm.Text.ToString)

SQL.ExecQuery("SELECT EmployeeName, PunchIn, PunchOut, HoursWorked FROM PUNCHES WHERE EmployeeName = @name And CurrentStatus = 0 AND Processed = 0")

Dim dt1 As DateTime = startdate.DisplayDate
Dim dt2 As DateTime = enddate.DisplayDate

Dim dt1 As Integer = DatePart(DateInterval.DayOfYear, dt1) 'startdateInt 
Dim dt2 As Integer = DatePart(DateInterval.DayOfYear, dt2) 'enddateInt 

dt = SQL.SQLDS.Tables(0)

For Each dr As DataRow In dt.Rows
    Dim dt3 As DateTime = DateTime.Parse(dr("PunchIn"))
    Dim dt3int As Integer = DatePart(DateInterval.DayOfYear, dt3)
    ' was:         If dt3int > dt2int AndAlso dt3int < dt1int Then
    ' should be 
    If dt3int > **dt1int** AndAlso dt3int < **dt2int** Then 
        dr.Delete()
    End If

Next

dataGrid.ItemsSource = dt.DefaultView
End Sub

Instead of
If dt3int > dt2int AndAlso dt3int < dt1int Then

use

If dt3int > dt1int AndAlso dt3int < dt2int Then

You're comparing to the wrong variables.

edited Oct 6, 2015 at 23:32

answered Oct 6, 2015 at 22:43

SeraM

4874 silver badges10 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

mediatech15 Over a year ago

No dice I replaced dr.delete with a msgbox and it never popped up.

mediatech15 Over a year ago

THANKS SO MUCH I actually figured it out about 30 minutes ago I kept reading your comments and found a few of my issues basically i delete before first date and then in elseif instead of the same line do after the second date.so i run 2 delete commands the single command just would never work.

Collectives™ on Stack Overflow

For Each loop With nested if not running properly. VB.net

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related