changing listbox bcolor everytime there is an expiration of time of the day

[hightechka]

i am new in VB.net (about 3 weeks now) and i am using visual studio 2008 to make a reservation and billing program project.

i want to change the background color of the row having the entry which time (Day Swimming 8am-5pm, night swimming 4pm-11pm and overnight swimming 9pm-11pm) will be compared to current time.

to make it easier to understand i will give an example.

customer 1 enters the resort at 8am and will expire in 5pm.
my list view displays the customers information and the time that they entered but not the expiration time.
i want to automatically change the bgcolor of the row when the current time is 5pm (rows containing Day Swimming).
i want to compare the string DAY SWIMMING to current time is it posible?

but i cant seem to understand and find a way to make the bgcolor of the row to red that tells the user that this particular customer needs to be checked immediately for billing out.

somehow i managed to populate my listview with this codes and it is all i got for now.

Public Class frmCstmr
    Private Sub refreshdata()
        Dim conn As New OleDbConnection(MyConnString)
        conn.Open()
        Dim lst
        ListView1.Items.Clear()
        lst = New ListViewItem
        Dim cmd As New OleDbCommand("Select * from Customer where status = 'checkedin'", conn)
        Dim da As New OleDbDataAdapter(cmd)
        Dim dataset As New DataSet()
        da.Fill(dataset, "Customer")
        For i = 0 To dataset.Tables("Customer").Rows.Count - 1
            lst = ListView1.Items.Add(dataset.Tables("Customer").Rows(i).Item("ID").ToString)
            lst.subitems.Add(dataset.Tables("Customer").Rows(i).Item("name").ToString)
            lst.SubItems.Add(dataset.Tables("Customer").Rows(i).Item("cottage").ToString)
            lst.SubItems.Add(dataset.Tables("Customer").Rows(i).Item("room").ToString)
            lst.SubItems.Add(dataset.Tables("Customer").Rows(i).Item("time1").ToString)
            lst.SubItems.Add(dataset.Tables("Customer").Rows(i).Item("adult").ToString)
            lst.SubItems.Add(dataset.Tables("Customer").Rows(i).Item("kids").ToString)
            lst.SubItems.Add(dataset.Tables("Customer").Rows(i).Item("seniorcitizen").ToString)
            lst.SubItems.Add(dataset.Tables("Customer").Rows(i).Item("amount").ToString)
            lst.SubItems.Add(dataset.Tables("Customer").Rows(i).Item("billingdate").ToString)
        Next
        conn.Close()

    End Sub

thank you in advance.

[cgeier]

This assumes that you already created a form and added a ListView named "ListView1" to it.

First you'll want to add a System.Windows.Forms.Timer to your form.

Open Solution Explorer:
In VS menu strip, do the following:
-Click "View"
-Select "Solution Explorer"

In solution explorer, double-click your form (ex: Form1.vb)

Add Timer to form:
In VS menu strip, do the following:
-Click "View"
-Select "Toolbox"
-Expand "All Windows Forms"
-Double-click "Timer"

You should see "Timer1" below your form. Right-click "Timer1" and select "Properties". Set "Interval" = "1000" -- this is in milliseconds, so 1000 ms = 1 sec. If you want to update every 15 seconds, change this to 15000. Change "Enabled" to "True". Double-click "Timer1" to create the "Tick" event handler. You should now see something like the following:

Public Class Form1

    Private Sub Timer1_Tick(sender As System.Object, e As System.EventArgs) Handles Timer2.Tick

    End Sub

End Class

We'll add some code to "Timer1_Tick" later.

Add module:
In VS menu strip, do the following:
-Click "Project"
-Select "Add Module". Name it "ModuleCustomer.vb"

Module ModuleCustomer

End Module

Add the following variables:

'expired customer color
Private expiredCustomerColor As Color = Color.Red
Private checkinIndex As Integer = 4

'Day Swimming
'year, month, day, hour, minute, seconds
Private DaySwimmingStartTimeDT As DateTime = New DateTime(DateTime.Now.Year, DateTime.Now.Month, DateTime.Now.Day, 8, 0, 0)
Private DaySwimmingEndTimeDT As DateTime = New DateTime(DateTime.Now.Year, DateTime.Now.Month, DateTime.Now.Day, 17, 0, 0)

'Evening Swimming
'year, month, day, hour, minute, seconds
Private EveningSwimmingStartTimeDT As DateTime = New DateTime(DateTime.Now.Year, DateTime.Now.Month, DateTime.Now.Day, 16, 0, 0)
Private EveningSwimmingEndTimeDT As DateTime = New DateTime(DateTime.Now.Year, DateTime.Now.Month, DateTime.Now.Day, 23, 0, 0)

'Late-Night Swimming
'year, month, day, hour, minute, seconds
Private LateNightSwimmingStartTimeDT As DateTime = New DateTime(DateTime.Now.Year, DateTime.Now.Month, DateTime.Now.Day, 21, 0, 0)
Private LateNightSwimmingEndTimeDT As DateTime = New DateTime(DateTime.Now.Year, DateTime.Now.Month, DateTime.Now.Day, 23, 0, 0)

'keeps track of the swimming types in effect
'since times overlap
Private SwimmingTypesInEffectDict As New Dictionary(Of String, Boolean)

We'll use a dictionary to keep track of what "swimming types" are currently in effect, since some of the times overlap.

We're not doing anything too difficult here. Just comparing the current time of day to the time of day that each swimming type is in effect. Each swimming type uses DateTime variables to hold it's start and end time. However, the date portion isn't necessarily kept up-to-date, so rather than comparing using "DateTime.Compare", we'll use "TimeSpan.Compare". If we fall within a time of day that a particular swimming type is in effect, we add an entry to the dictionary. Since a dictionary uses two objects, we'll use the key--we won't use the value portion for anything so you can set it to any data type and set it to anything. I've created it as a Boolean and set it to 'True'.

SetSwimmingTypesInEffect:

'Add/Remove swimming types in effect based on time of day
'This updates the SwimmingTypesInEffectDict dictionary
Public Sub SetSwimmingTypesInEffect(ByVal currentTime As DateTime)

    'Day Swimming
    If TimeSpan.Compare(currentTime.TimeOfDay, DaySwimmingStartTimeDT.TimeOfDay) >= 0 AndAlso
        TimeSpan.Compare(currentTime.TimeOfDay, DaySwimmingEndTimeDT.TimeOfDay) <= 0 Then
        'Day Swimming

        'add entry to dictionary if it doesn't exist
        If Not SwimmingTypesInEffectDict.ContainsKey("Day Swimming") Then
            SwimmingTypesInEffectDict.Add("Day Swimming", True)
        End If
    Else
        'remove entry from dictionary if it exists
        If SwimmingTypesInEffectDict.ContainsKey("Day Swimming") Then
            SwimmingTypesInEffectDict.Remove("Day Swimming")
        End If
    End If

    'Evening Swimming
    If TimeSpan.Compare(currentTime.TimeOfDay, EveningSwimmingStartTimeDT.TimeOfDay) >= 0 AndAlso
        TimeSpan.Compare(currentTime.TimeOfDay, EveningSwimmingEndTimeDT.TimeOfDay) <= 0 Then
        'Evening Swimming if checkin between 1600 and 2059

        'add entry to dictionary if it doesn't exist
        If Not SwimmingTypesInEffectDict.ContainsKey("Evening Swimming") Then
            SwimmingTypesInEffectDict.Add("Evening Swimming", True)
        End If
    Else
        'remove entry from dictionary if it exists
        If SwimmingTypesInEffectDict.ContainsKey("Evening Swimming") Then
            SwimmingTypesInEffectDict.Remove("Evening Swimming")
        End If
    End If

    'Late-Night Swimming
    If TimeSpan.Compare(currentTime.TimeOfDay, LateNightSwimmingStartTimeDT.TimeOfDay) >= 0 AndAlso
        TimeSpan.Compare(currentTime.TimeOfDay, LateNightSwimmingEndTimeDT.TimeOfDay) <= 0 Then
        'Late-Night Swimming if checkin between 2100 and 2259

        'add entry to dictionary if it doesn't exist
        If Not SwimmingTypesInEffectDict.ContainsKey("Late-Night Swimming") Then
            SwimmingTypesInEffectDict.Add("Late-Night Swimming", True)
        End If
    Else
        'remove entry from dictionary if it exists
        If SwimmingTypesInEffectDict.ContainsKey("Late-Night Swimming") Then
            SwimmingTypesInEffectDict.Remove("Late-Night Swimming")
        End If
    End If

End Sub

Next, we'll write a function that determines swimming type based on the customer's checkin time. Similar to above, just comparing the times. However, since a new swimming type occurs at certain times, I've subtracted 1 minute from the end times.

GetSwimmingTypeFromCheckinTime:

Public Function GetSwimmingTypeFromCheckinTime(ByVal checkin As DateTime) As String

    If TimeSpan.Compare(checkin.TimeOfDay, LateNightSwimmingStartTimeDT.TimeOfDay) >= 0 AndAlso
        TimeSpan.Compare(checkin.TimeOfDay, LateNightSwimmingEndTimeDT.AddMinutes(-1).TimeOfDay) <= 0 Then

        'Late-Night Swimming if checkin between 
        'LateNightSwimmingStartTimeDT and LateNightSwimmingEndTimeDT
        'subtracted 1 minute from end time due to overlapping times
        Return "Late-Night Swimming"

    ElseIf TimeSpan.Compare(checkin.TimeOfDay, EveningSwimmingStartTimeDT.TimeOfDay) >= 0 AndAlso
        TimeSpan.Compare(checkin.TimeOfDay, EveningSwimmingEndTimeDT.AddMinutes(-1).TimeOfDay) <= 0 Then

        'Evening Swimming if checkin between 
        'EveningSwimmingStartTimeDT and EveningSwimmingEndTimeDT
        'subtracted 1 minute from end time due to overlapping times
        Return "Evening Swimming"

    ElseIf TimeSpan.Compare(checkin.TimeOfDay, DaySwimmingStartTimeDT.TimeOfDay) >= 0 AndAlso
        TimeSpan.Compare(checkin.TimeOfDay, DaySwimmingEndTimeDT.AddMinutes(-1).TimeOfDay) <= 0 Then
        'Day Swimming if checkin between
        'DaySwimmingStartTimeDT and DaySwimmingEndTimeDT
        'subtracted 1 minute from end time due to overlapping times
        Return "Day Swimming"

    Else
        Return "Swimming type not defined."
    End If

End Function

Lastly, we just need to update the ListView items (rows) with a different color to show the user that the swimming type time has expired (based on the user's checkin time). We'll use "GetSwimmingTypeFromCheckinTime" for this and then we'll see if the customer's swimming type is currently in effect by checking to see if an entry exists in the "SwimmingTypesInEffectDict" dictionary.

UpdateListViewItemColor:

Public Sub UpdateListViewItemColor(ByVal lv As ListView)
    If lv.Items.Count > 0 Then
        For i As Integer = 0 To lv.Items.Count - 1
            Dim currentItem As ListViewItem = lv.Items(i)

            'Get customer checkin from ListView.
            'Then get customer's swimming type based on 
            'his/her checkin time.
            Dim customerSwimmingType As String = GetSwimmingTypeFromCheckinTime(DateTime.Parse(currentItem.SubItems(checkinIndex).Text))

            'If the dictionary contains the swimming type as the key, 
            '(ex: "Day Swimming") then the swimming type is in effect.
            'Otherwise, the swimming type isn't in effect and the color
            'of the customer's row in the ListView will be changed
            If Not SwimmingTypesInEffectDict.ContainsKey(customerSwimmingType) Then
                'change row color to specified color
                lv.Items(i).BackColor = expiredCustomerColor
            End If
        Next
    End If
End Sub

Now, well make use of our code. Go back to the form (ex: Form1), and add code so that "Timer1_Tick" looks like the following:

Private Sub Timer1_Tick(sender As System.Object, e As System.EventArgs) Handles Timer1.Tick
    'get current time
    Dim currentTime As DateTime = DateTime.Now()

    'update/set swimming types in effect
    SetSwimmingTypesInEffect(currentTime)

    'update listview color for expired items
    UpdateListViewItemColor(ListView1)

    'ToolStripStatusLabel1.Text = currentTime.ToString("MM/dd/yyyy  HH:mm:ss")
    'StatusStrip1.Refresh()
End Sub

Additionally, you can add a "StatusStrip" to your form to show the time. Add a StatusStrip from the ToolBox. Then on the StatusStrip (bottom of form) "StatusStrip1", click on the downward arrow, and select "StatusLabel". Then uncomment the following lines, in the code above:

ToolStripStatusLabel1.Text = currentTime.ToString("MM/dd/yyyy  HH:mm:ss")
StatusStrip1.Refresh()

[hightechka]

thank you cgeier for your response,

i copied your snippets to my project but what is ModuleCustomer for?
and im also getting this error

Error 25 'Public Sub UpdateListViewItemColor(lv As System.Windows.Forms.ListView)' has multiple definitions with identical signatures.

and to all if statements with AndAlso. ill attach my project here.

[Eternal Newbie]

"multiple definitions with identical signatures" error means you have 2 (or more) subs with one same name. I suggest u might post those lines twice so better re-check, just delete all unwanted lines.

cgeier's "ModuleCustomer" was to: Seperate sub for timer with others, so you can use it on others forms in your project. Just guessing.

[cgeier]

ModuleCustomer contains all the code posted above, and some additional code for initializing a ListView that is new (hasn't been changed using the designer). I included it to show how columns can be added in code rather than in the designer. Additionally, there is some code that adds some rows for testing purposes. It is included for anyone who wishes to download the code and run it without the need of creating a database.

Before going further, if you haven't already done so, I recommend that you turn on line numbering.

In VS, do the following:
-Click "Tools" in menu bar
-Select "Options"
-Expand "Text Editor"
-Expand "All Languages"
-Click "General"
-In right pane, under "Display", check "Line numbers".
-Click "OK"

You can use "Ctl-G" to go to a particular line number. You have the same sub listed starting in line number 109 and line number 128. How did I find that? Use "Ctl-F" to search for "UpdateListViewItemColor". The error "...has multiple definitions with identical signatures" means that you have more than one Sub with the exact same name and same parameter definitions. In this case, you entered the same Sub twice.

Also, the code I posted was created a different version of VS. So there is also the error "Expression expected." This occurs when code spans multiple lines. To fix this add an underscore to the end of each line that continues on the next line.

Change from:

If TimeSpan.Compare(currentTime.TimeOfDay, EveningSwimmingStartTimeDT.TimeOfDay) >= 0 AndAlso
    TimeSpan.Compare(currentTime.TimeOfDay, EveningSwimmingEndTimeDT.TimeOfDay) <= 0 Then

To:

If TimeSpan.Compare(currentTime.TimeOfDay, EveningSwimmingStartTimeDT.TimeOfDay) >= 0 AndAlso _
    TimeSpan.Compare(currentTime.TimeOfDay, EveningSwimmingEndTimeDT.TimeOfDay) <= 0 Then

or you can just put all the code on one line and won't have this issue.

[cgeier]

Also, you should Use Parameterized Queries to Avoid SQL Injection Attacks

For example, in "frmAdmin" (btnSave_Click) you have the following code:

Dim cmdsave As New OleDbCommand("INSERT INTO Room(id,Room_Name,Available,Quantity,Price)" & _
                                "VALUES('" & txtid.Text & "','" & txtname.Text & "'," & _
                                " '" & txtquantity.Text & "','" & txtquantity.Text & "','" & txtprice.Text & "')", conn)

cmdsave.ExecuteNonQuery()

to change to use parameterized queries, do the following:

Dim cmdsave As New OleDbCommand("INSERT INTO Room(id,Room_Name,Available,Quantity,Price)" & _
                                        "VALUES(@id, @Room_Name, @Available, @Quantity, @Price", conn)

Then add the parameters using one of the following methods (versions).

Version 1: (use AddWithValue)

cmdsave.Parameters.AddWithValue("@id", txtid.Text)
cmdsave.Parameters.AddWithValue("@Room_Name", txtname.Text)
cmdsave.Parameters.AddWithValue("@Available", txtquantity.Text)
cmdsave.Parameters.AddWithValue("@Quantity", txtquantity.Text)
cmdsave.Parameters.AddWithValue("@Price", txtprice.Text)

Version 2: (specify data type)

cmdsave.Parameters.Add("@id", OleDbType.VarChar).Value = txtid.Text
cmdsave.Parameters.Add("@Room_Name", OleDbType.VarChar).Value = txtname.Text
cmdsave.Parameters.Add("@Available", OleDbType.VarChar).Value = txtquantity.Text
cmdsave.Parameters.Add("@Quantity", OleDbType.VarChar).Value = txtquantity.Text
cmdsave.Parameters.Add("@Price", OleDbType.VarChar).Value = txtprice.Text

Version 3:

Dim idParam As New OleDbParameter("@id", txtid.Text)
cmdsave.Parameters.Add(idParam)

'do similarly for the other parameters

Version 4:

Dim idParam As New OleDbParameter("@id", OleDbType.VarChar)
idParam.Value = txtid.Text
cmdsave.Parameters.Add(idParam)

'do similarly for the other parameters

There are other ways of adding the parameters, these are just a few.

Then execute:

cmdsave.ExecuteNonQuery()

[hightechka]

thanks for the advice cgeier, ill do the parametarized query after the listview thing. i see you've opened my project, thankyou for the time you are spending to my questions.
we declared and defined terms/words (correct me if im wrong) to dictionary but still theres this blue zigzag lines on some lines.

where should i put the function? module or the frmCustomer? i tried both but still theres a zigzag underline.

Public Sub UpdateListViewItemColor(ByVal lv As ListView)
        If lv.Items.Count > 0 Then
            For i As Integer = 0 To lv.Items.Count - 1
                Dim currentItem As ListViewItem = lv.Items(i)
                'Get customer checkin from ListView.
                'Then get customer's swimming type based on
                'his/her checkin time.
                Dim customerSwimmingType As String = GetSwimmingTypeFromCheckinTime(DateTime.Parse(currentItem.SubItems(checkinIndex).Text))
                'If the dictionary contains the swimming type as the key,
                '(ex: "Day Swimming") then the swimming type is in effect.
                'Otherwise, the swimming type isn't in effect and the color
                'of the customer's row in the ListView will be changed
                If Not SwimmingTypesInEffectDict.ContainsKey(customerSwimmingType) Then
                    'change row color to specified color
                    lv.Items(i).BackColor = expiredCustomerColor
                End If
            Next
        End If
    End Sub

[hightechka]

there was an error in this line, it belongs to
Public Sub UpdateListViewItemColor(ByVal lv As ListView)

Dim customerSwimmingType As String = GetSwimmingTypeFromCheckinTime(DateTime.Parse(currentItem.SubItems(checkinIndex).Text))

The string was not recognized as a valid DateTime. There is a unknown word starting at index 0.

and im sorry but the late night time isnt 9pm to 11pm, it should be 9pm to 2am

[hightechka]

sorry for being newbie.

[cgeier]

The value in currentItem.SubItems(checkinIndex).Text is not something that can be converted to DateTime, because it either isn't a Date/Time or isn't in a format that is recognizable to DateTime.Parse

[hightechka]

maybe because it is on the short time format? on my db it is date/time but forms like mm/dd/yyyy/hh:mm:ss. ill try to make it recognize by your code later at home and will get back to you after result. thank you.

[cgeier]

Replace the '/' between yyyy and hh and it may work. It looks like your database design could use some work. Is this a theoretical resort (ie: homework assgmt) or a real place. After looking through your project I had difficulty figuring out where swimming type comes into play. Can ppl just visit to use the pool/beach and pay a small fee? Is there an extra fee for additional people when renting a cottage/room?

[hightechka]

thank you cgeier for answering my questions and i have learned a lot from you.

i figured out and solved my problem thru this codes.

Dim conn As New OleDbConnection(MyConnString)
        conn.Open()

        Dim currenttime As DateTime = FormatDateTime(Now, DateFormat.LongTime)

        If ListView1.Items.Count > 0 Then
            For i As Integer = 0 To ListView1.Items.Count - 1
                Dim cmd As New OleDbCommand("Select * from Customer where id = '" & ListView1.Items(i).SubItems(0).Text & "'", conn)
                Dim da As New OleDbDataAdapter(cmd)
                Dim dataset As New DataSet()
                da.Fill(dataset, "Customer")
                If dataset.Tables("customer").Rows.Count > 0 Then
                    If dataset.Tables("Customer").Rows(0).Item("time1").ToString = "Day Swimming" And dataset.Tables("Customer").Rows(0).Item("status").ToString = "checkedin" Then
                        If currenttime.Hour >= 17 Then
                            ListView1.Items(i).BackColor = Color.Red
                        End If
                    ElseIf dataset.Tables("Customer").Rows(0).Item("time1").ToString = "Night Swimming" And dataset.Tables("Customer").Rows(0).Item("status").ToString = "checkedin" Then
                        If currenttime.Hour >= 23 Then
                            ListView1.Items(i).BackColor = Color.Yellow
                        End If
                    ElseIf dataset.Tables("Customer").Rows(0).Item("time1").ToString = "Overnight Swimming" And dataset.Tables("Customer").Rows(0).Item("status").ToString = "checkedin" Then
                        If currenttime.Hour >= 2 Then
                            ListView1.Items(i).BackColor = Color.Blue
                        End If
                    ElseIf dataset.Tables("Customer").Rows(0).Item("status").ToString = "checkedout" Then
                        ListView1.Items(i).BackColor = Color.Gray
                    End If
                End If
            Next
        End If


        conn.Close()

thanks again.