Refreshing data when using a datareader - Page 2

[pezza]

Ok, so I am trying to merge in the amendments in to my code, as this uses a reader, so I am first trying to see if my original code works....now that I have got a SQL based connection.

In the For Loop where the labels are removed, it refers to lblName (for each lblName in...) but when i add that it complains that lblName is not declared.

Where should this declaration be made, as when i seem to add it, it seems to throw the IDE in to another tangle and starts underlining other variables :-(

The reason I wanted to do this the long way, is that I wanted to try and learn whilst merging the bits in, so that I understand it more. A reader really isn't that important, but if I can get it to work, then that is great, but if not, i can then more easily understand using a different one.

Just another question which has been puzzling me (again, through lack of experience on my part!), why would the start date in the schedule be a Primary Key? Is this just saying that BOTH combination of ID and Start Date is unique (because not more than 2 people will start at the same time)?

Also, thanks xrj - I am slowly pulling all these together to get a good understanding of my gaps :-)

Thanks

Andrew

[cgeier]

I think that it is easier to understand if I post some sample data.

Team Sample Data:

TeamMember Sample Data:

Schedule Sample Data:

A primary key must be unique--it's value cannot be inserted more than once in a table. By looking at the data in "Schedule", we can see that an employeeID (called teamMemberEmpID in table Schedule), has multiple schedules--start times and finish times. So how can we create a unique value from that? Well, in my table design, we have two choices:

Choice 1:
teamMemberEmpID, start

Choice 2:
teamMemberEmpID, finish

Question 1: Will start time always be known?

Question 2: Will finish time always be known?

Question 3: Is it possible that start OR finish will ever contain a null value?

A null value can be unique so long as another one is not entered.

It is possible that an employee is given a start time, but the finish time is subject to change based on the needs of the company. In choosing a primary key, it is better to choose data that is more likely to remain static. Therefore, I chose to use start time as the other part of my primary key.

Since, one employee can only work one shift at a time, he/she will only ever have one start date/time on a particular day. Therefore this will always make it unique.

[cgeier]

In the For Loop where the labels are removed, it refers to lblName (for each lblName in...) but when i add that it complains that lblName is not declared.

Fix: Add "As String".

Change the following code from:

'remove old labels
For Each lblName In lblNameList
    Me.Controls.RemoveByKey(lblName)
Next

To:

'remove old labels
For Each lblName As String In lblNameList
    Me.Controls.RemoveByKey(lblName)
Next

[cgeier]

'SqlDataReader' version is basically the same one that I posted previously. Other minor modifications are documented in the code.

'SQLDataAdapter' version has the changes made by xj implemented (uses SqlDataAdapter and a DataTable). I tried to document the changes that xj made the best that I could (in the "Modified" section).

Both versions contain the fix for "lblName not being declared" (see my previous post) and contain more comments / documentation than the version in my previous post.

You should be able to open / run these in your version of VS.

[cgeier]

I've updated 'getData' to use an SqlDataAdapter and added code that significantly reduces the number of times the labels are re-drawn (appearance of flashing on the form). This is an update to "DatabaseOnCallRotation-SqlDataReader.zip" but only contains a few of the changes that xrj made.

When other people make a lot of changes to your code, sometimes it no longer feels like it is your program. It is a good idea to separate the code into blocks that do a particular task (such as retrieving the data, showing the data, etc) as xrj did. This version is a progression towards that.

Using an SqlDataAdapter and DataTable will be better because it will make it easier for you to use your data as a DataSource should you decide to switch to another type of control (such as a DataGridView).

The code in 'getData' is still mostly your code, with a few additional modifications. It now uses a function called 'compareLabelLists'.

Add the following declaration inside 'Public Class MainFrm':

    'used to hold the names of the 
    'previous labels. This will prevent
    'controls from re-drawing every
    'updateInterval. Instead, only
    're-drawing if there are changes
    Dim previousLblList As List(Of Label) = New List(Of Label)

compareLabelLists:

    Private Function compareLabelLists(ByVal list1 As List(Of Label), ByVal list2 As List(Of Label))
        Dim areListsEqual As Boolean = True

        Try

            'if the number of elements is different
            'the lists can't be equal
            If list1.Count <> list2.Count Then
                areListsEqual = False
            Else

                'if the text of the label in the 
                'two lists is different return false
                For i As Integer = 0 To list1.Count - 1
                    If String.Compare(list1(i).Text, list2(i).Text) <> 0 Then
                        areListsEqual = False
                    End If
                Next
            End If
        Catch ex As Exception
            errorCount += 1
            If errorCount < 2 Then
                System.Windows.Forms.MessageBox.Show("Error (CompareLists): " & ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
            End If
        End Try

        Return areListsEqual
    End Function

getData:

    Private Sub getData()
        ' This function configures and displays the label(s) for each team that is currently on-call.  It extracts the data from the SQL database in to a
        ' Data Reader so that the values of the person on-call for each team can be pulled in to each label.  The query pulls out the team, teammember and
        ' schedule details by querying the current date/time and examining who is on-call at that time for each team.

        'holds data retrieved from database
        Dim retDataTable As New DataTable

        'hold all of our new labels
        Dim lblList As New List(Of Label)

        Dim rowCount As Integer = 0


        ' Declare Label positions and sizes

        Dim StartPosX As Integer = 12
        Dim StartPosY As Integer = 100 'changed from 200 to 100
        Dim LabelWidth As Integer = 108
        Dim LabelHeight As Integer = 108
        Dim GapToLeave As Integer = 6




        ' Declare and set SQL Connection String
        ' Declare values to be used in the label from the query

        Dim teamName As String = ""
        Dim teamMemberFirstName As String = ""
        Dim teamMemberSurname As String = ""
        Dim teamMemberMobile As String = ""
        Dim teamMemberLocation As String = ""


        ' Create SQL Connection and query using SQL Command based on Connection String

        Console.WriteLine("Connection String: " + SQLExpressDB.connectStr)


        Using cn As New SqlConnection(SQLExpressDB.connectStr)
            Dim sqlText As String = String.Empty
            sqlText = "SELECT Team.name as 'TeamName', TeamMember.firstName, "
            sqlText += "TeamMember.Surname, TeamMember.onCallPhone, TeamMember.location, "
            sqlText += "Schedule.start, Schedule.finish FROM Team "
            sqlText += "INNER JOIN TeamMember "
            sqlText += "ON Team.teamID = TeamMember.teamID "
            sqlText += "INNER JOIN Schedule "
            sqlText += "ON TeamMember.employeeID = Schedule.TeamMemberEmpID "
            sqlText += "WHERE (Schedule.start < { fn NOW() }) AND (Schedule.finish > { fn NOW() })"


            Try

                ' Open Connection
                cn.Open()

                'use SqlDataAdapter to get data from query
                Using da As New SqlDataAdapter(sqlText, cn)

                    Try
                        'use SqlDataAdapter to store data from query
                        da.Fill(retDataTable)
                    Catch ex1 As Exception
                        Throw New Exception("Error in dataAdapter. " + ex1.Message)
                    End Try


                    'initialize rowCount
                    rowCount = 0

                    For Each row As DataRow In retDataTable.Rows


                        'keep track of number
                        'of rows retrieved
                        rowCount += 1

                        ' Set values to those from the Data Reader

                        teamName = row("TeamName").ToString()
                        teamMemberFirstName = row("firstName").ToString()
                        teamMemberSurname = row("surname").ToString()
                        teamMemberMobile = row("onCallPhone").ToString()
                        teamMemberLocation = row("location").ToString()

                        Console.WriteLine("TeamName: " & teamName)
                        Console.WriteLine("firstName: " & teamMemberFirstName)

                        ' Declare variable for new label
                        Dim lblTeam As New Label

                        ' Initialise the Text property of the new label
                        lblTeam.Text = Nothing

                        With lblTeam

                            ' Set the label properties

                            .Name = "lblTeam_" & teamName
                            .Font = New Font(lblTeam.Font.FontFamily, 10)
                            .Size = New System.Drawing.Size(LabelWidth, LabelHeight)
                            .Location = New System.Drawing.Point(StartPosX, StartPosY)
                            .ForeColor = System.Drawing.Color.Black
                            .Text = teamName & vbCrLf & teamMemberFirstName & " " & teamMemberSurname & vbCrLf & teamMemberMobile & vbCrLf & teamMemberLocation
                            .AutoSize = False
                            .BorderStyle = System.Windows.Forms.BorderStyle.Fixed3D


                            'keep track of labels that were created
                            'add lblTeam to list. 

                            lblList.Add(lblTeam)
                        End With

                        ' Set up the starting position for the next label

                        StartPosX = StartPosX + LabelWidth + GapToLeave

                        ' Initialise/clear all variables

                        teamName = Nothing
                        teamMemberFirstName = Nothing
                        teamMemberSurname = Nothing
                        teamMemberMobile = Nothing
                        teamMemberLocation = Nothing

                    Next

                End Using

            Catch ex As SqlException
                'prevents lots of error messagebox windows
                'if an error occurs
                errorCount += 1

                If errorCount < 2 Then
                    System.Windows.Forms.MessageBox.Show("Error (Open Connection): " & ex.Message & System.Environment.NewLine & System.Environment.NewLine & "Connection String: " & System.Environment.NewLine & SQLExpressDB.connectStr, "Error - Select", MessageBoxButtons.OK, MessageBoxIcon.Error)
                End If

            Catch ex As Exception
                'prevents lots of error messagebox windows
                'if an error occurs
                errorCount += 1

                If errorCount < 2 Then
                    System.Windows.Forms.MessageBox.Show("Error (Open Connection): " & ex.Message & System.Environment.NewLine & System.Environment.NewLine & "Connection String: " & System.Environment.NewLine & SQLExpressDB.connectStr, "Error - Select", MessageBoxButtons.OK, MessageBoxIcon.Error)
                End If
            End Try
        End Using


        'compare lists to see if anything has changed
        'Removing and re-drawing the labels only
        'when there are changes will reduce re-drawing
        'frequency of the labels and minimizing the
        'flashing effect that re-drawing creates


        If compareLabelLists(previousLblList, lblList) = False Or previousLblList.Count <= 0 Then

            'remove old labels
            For Each lbl As Label In previousLblList
                Me.Controls.RemoveByKey(lbl.Name)
            Next

            'add labels to form
            For Each lblItem As Label In lblList
                Me.Controls.Add(lblItem)
            Next

        End If

        'update status
        ToolStripStatusLabel1.Text = "Status: " & rowCount & " records retrieved (last update: " & DateTime.Now & ")"

        'refresh the StatusStrip1
        'to ensure the ToolStripStatusLabel1
        'text is updated
        StatusStrip1.Refresh()


        'update previousLblName
        previousLblList = lblList


        'stop timer if errors are occuring
        'to prevent too many messagebox messages
        If errorCount > 2 Then
            stopTimer()
            ToolStripStatusLabel1.Text = "Status: Automatic updates disabled. Click 'Tools' -> 'Refresh' to re-enable."

            'refresh to ensure ToolStripStatusLabel1.Text
            'is updated
            StatusStrip1.Refresh()
        End If

    End Sub

I've included the files below. I called it 'DatabaseOncallRotation-SqlDataAdapter-v0.3" because it uses an SqlDataAdapter, but doesn't include all the modifications that xrj made. It should work with your version of VS.

[pezza]

Great, thank you.

I am going to have a proper look at all this, this evening when I get home. I might even just use this instead of trying to merge in to mine, its just that I also have some code at the moment that displays the times in different zones for informational purposes, so just need to bring that in.

My biggest point really is that I want to understand it, rather than just using something that someone has done :-) as over time, i will need to update it

I will have a look tonight and let you know.

Really appreciate all the help here.

Thanks

Andrew

[pezza]

Hi,

So, I have had a play, and everything looks perfect!! I was able to open the solution this time so all looking good.

I have tested 3 scenarios:

1) Changing existing schedule to a different person ID
2) Changing Start/End Times
3) Adding a new schedule for another person from a different team

All working perfectly :-)

I am now streamling my functions from my round the world timezone clocks, which I also want to display on this form for NY, UK, Lithuania, India and Singapore so that whoever is using the program, from wherever they happen to be, they will know what the times are in the different support locations (not that it will make them think twice about ringing me up at 3AM!). I have decided to enforce a strict "All schedule date/times to be in UTC" though....atleast until I can work out how to identify and convert, but for now, it is better than what they have!

I am also still reading through all the code to understand how everything fits together and what it does.

Once I have finished, I will try to make it available, but before I do that, i will need to make it so that the support locations are variable as mine are currently hard coded.

Thank you both and I will let you know how I get on!

Andrew

[pezza]

Just a screenshot with my timezones so far! Hopefully it works

Just need to tidy them up so that it is clear whether AM or PM and the date :-)

[xrjf]

@pezza Those clock images look fine. I had a little taste managing timezones. Here is a screenshot:

[xrjf]

All the text data come from TimeZoneInfo.GetSystemTimeZones()(Click Here). My current language is set to "es-es", in your's text data will be in your current language.

[pezza]

Thanks xrj,

Yeah, I have got most of it from the TimeZoneInfo, and whilst it is working at the moment, I know that I need to work on it :-)

The clocks are not my creation, they are courtesy of VBDT which I found here Click Here

It's quite neat in that it is just a control with the associated XML for the descriptions. The only thing is that the time is read only and is controlled by a UTCOffset property.....which once you understand how to manipulate that, as you obviously have :-) it becomes quite useful. Would love to have a different face, but sometimes simple is best.

I am still playing with the different properties and members of the TimeZoneInfo though, as the more I start coding, the more my head gets mashed up trying to work out where I am up to and what time i need to feed it, mainly with all the Daylight Savings times! I think once i get that base time, then the rest should flow and I can then reduce my code considerably, as it works, but its a mess LOL

It is just finding the time at the moment in between work, my University Degree, Family and Candy Crush :-) but as I said before, its one of those things that I have been told not to bother doing, but I want to for my own learning, and if it works, then they can use it if they want to. Where I work, we have so many systems that has all this data, but nothing ever gets done about using the right sources, and every data source always seems to have slightly different information, so eventually we will use the intended data sources, but it is still all good learning and fun :-)

PS. I can't seem to get that solution to open, as its in a new version. Any chance you could get that down to a version that will work with 2005? I had a look the other night at the newest versions and nearly fell off my chair when i seen the prices LOL The truth is I got this copy free when I attended a seminar, and its the full monty, which apparently costs about £10,000. Which I dont quite have spare....so I am not sure whether one of the "basic" versions would do, or whether it would restrict something that I am using at the moment, such as not being able to compile etc.

Thanks

Andrew

[cgeier]

TimeZoneInfo looks like it is only supported in .NET versions >= 3.5

Here are some other resources:

.NET Framework Versions and Dependencies

Multi-Targeting Support

Global Time

VS Express 2010

VS Express 2012 - Desktop

VS Express 2013 - Desktop

[xrjf]

Right, TimeZoneInfo not supported in .NET versions <3.5. There is Here a class similar. I've added to the project and build for .NET v2.0. I have had a bit of problems to understand Time-Zone-Class and perhaps it was not necessary but I have added two methods:

    ''' <summary>
    ''' Returns a value indicating whether this time 
    ''' zone is within a daylight saving time period.
    ''' </summary>
    <DebuggerHidden()> _
    Public Shared Function IsDaylightSavingTime(dtUTC As DateTime, tzi As TimeZoneInfo) As Boolean
        Dim dUtcNow As DateTime = dtUTC.AddMinutes(-(tzi._tzi.bias))
        Dim sUtcNow As DateTime = dtUTC.AddMinutes(-(tzi._tzi.bias + tzi._tzi.daylightBias))
        Dim dt As DaylightTime
        Try
            If tzi._tzi.daylightDate.wMonth <= tzi._tzi.standardDate.wMonth Then
                'Daylight saving time starts and ends in the same year
                dt = tzi.GetDaylightChanges(dUtcNow.Year)
                If dt.Delta <> TimeSpan.Zero Then
                    If dUtcNow >= dt.Start AndAlso sUtcNow < dt.End Then
                        Return True
                    Else
                        'Return False
                    End If
                End If
            Else
                'Daylight saving time starts and ends in diferent years
                dt = tzi.GetDaylightChanges(sUtcNow.Year)
                If dt.Delta <> TimeSpan.Zero Then
                    If dUtcNow < dt.Start AndAlso sUtcNow >= dt.End Then
                        'Return False
                    Else
                        Return True
                    End If
                End If
            End If
        Catch ex As Exception
            Dim s As String = ex.Message
        End Try
        Return False
    End Function

and

    ''' <summary>
    ''' Returns the daylight saving time for a particular TimeZoneInfo and year.
    ''' </summary>
    ''' <param name="year">The year to which the daylight 
    ''' saving time period applies.</param>
    <DebuggerHidden()> _
    Public Function GetDaylightChanges( _
    ByVal year As Integer, tzInfo As TimeZoneInfo) As System.Globalization.DaylightTime
        Dim tzi As New TimeZoneInformation

        Dim key As RegistryKey = Registry.LocalMachine.OpenSubKey( _
        "SOFTWARE\Microsoft\Windows NT\CurrentVersion\Time Zones", False)
        If Not key Is Nothing Then
            Dim subKey As RegistryKey = key.OpenSubKey(tzInfo._id, False)
            If Not subKey Is Nothing Then
                Dim subKey1 As RegistryKey = subKey.OpenSubKey("Dynamic DST", False)
                If Not subKey1 Is Nothing Then
                    If Array.IndexOf(subKey1.GetValueNames, CStr(year)) <> -1 Then
                        tzi.SetBytes(CType(subKey1.GetValue(CStr(year)), Byte()))
                    Else
                        Me.Refresh()
                        tzi = tzInfo._tzi
                    End If
                    subKey1.Close()
                Else
                    Me.Refresh()
                    tzi = tzInfo._tzi
                End If
                subKey.Close()
            Else
                Throw New Exception("Unknown time zone.")
            End If
        Else
            Throw New KeyNotFoundException( _
            "Cannot find the windows registry key (Time Zone).")
        End If
        key.Close()
        Dim dStart, dEnd As DateTime
        dStart = Me.GetStartDate(tzi, year)
        dEnd = Me.GetEndDate(tzi, year)
        If dStart <> Date.MinValue AndAlso dEnd <> Date.MinValue Then
            Return New DaylightTime( _
            dStart, dEnd, New TimeSpan(0, -tzInfo._tzi.daylightBias, 0))
        Else
            Return New DaylightTime(dStart, dEnd, New TimeSpan(0, 0, 0))
        End If
    End Function

The object is to call IsDayLightSavingTime(currentUTC as DateTime, tzi as TimeZoneInfo) in:

    Private Sub DGV1_CellEndEdit(sender As Object, e As DataGridViewCellEventArgs) Handles DGV1.CellEndEdit
        Try
            ' Editing a row's time becames current time:

            Dim row As DataGridViewRow = DGV1.Rows(e.RowIndex)
            Dim sID As String = row.Cells("ID").Value
            sCurID = sID
            Dim dt As DateTime = CType(row.Cells(2).Value, DateTime)
            Dim tzi As TimeZoneInfo = vTzi(row.Index)
            tUTC = dt.ToUniversalTime
            If TimeZoneInfo.IsDaylightSavingTime(tUTC, tzi) Then
                tUTC.Add(tzi.CurrentUtcOffset)
            End If
            ts = New TimeSpan(Now.Ticks)
            UpdateCtrls(tUTC, sCurID)
            If chkUpdate.Checked Then
                Timer1.Start()
            End If
        Catch ex As Exception

        End Try
    End Sub

...after a datetime cell in datagridview DGV1 has been edited, so the new time becomes the current time.

[pezza]

Hi, managed to get some trial downloads and set up a virtual machine with Studio Ultimate 2013.

So, just playing with that at the moment. I might end up investing in Professional version, but I need to understand what I can, or more specifically can't do with the versions, compared to what I have now. I know for a fact that I don't need the one I have (Team Server) just for individual development, but just need to make sure that whatever I get will allow me to build and compile anything that I do so that it can be used an an executable :-)

Maybe even Express will be enough, which I believe is free!

The strange thing is though, that my 2005 version does seem to work with the TimeZoneInfo as I have been accessing it and pulling back information from it...I think I just had to import the reference?? Anyway, going to have a play.

Will post back soon...hopefully once I have all this working.

Thanks

Andrew

[xrjf]

Really, don't you want to start a new thread? Anyway, thanks for the info, I'll take note. I've been developing till VS2010 with Pro version because time ago I had modified a "simple" USB device driver and needed to update for newer Windows. But it seems I will no longer need more updates and so that version.

[xrjf]

Don't misunderstand me, please. I appreciate your efforts and wait to your news.
Best Regards.

[xrjf]

Sorry, as you can see my english is not so good, specially when writing fast. I really meant I wait for your news or reply.

[pezza]

Yeah, thats a valid point. I guess i just didn't want to let go :-)

Just before I close this down, just a question on this TimeZoneInfo...

I have managed to get Visual Studio Professional 2013 for Free as part of some MS DreamSpark thing as I am doing a degree at the OU, so I am now on the latest version.

When I import the project now and set it to the latest .NET version (because it now complains that the code was written for .NET 2!) it decides to underline all the TimeZoneInfo declarations in blue, saying that TimeZoneInfo is ambiguous in the namespace System :-( But I don't know what I am doing wrong. When I start typing, it recognises the class but I can't work out why it is complaining.

Searching around, i can only see references to the time being ambiguous, but at this point I am just trying to declare it and it isn't aware of any time yet.

Any ideas on this? Did you come across anything like this when you was looking at the class?

Thanks

Andrew

[xrjf]

Well, my VStudio express does not bother for Timezoneinfo class, neither the project is version 2.0 nor 4.5. I think an easy workaround would be to rename TimeZoneInfo class in file TimeZoneInfo.vb to myTimeZoneInfo class or any other name you wish.

[pezza]

So it seems to be after the project is upgraded by VS that it tends to mess up the Timezoneinfo....if i create a brand new project, all resolves fine, so I am going to start from fresh project using all the code from both. Bit tedious, but will be good to go through each bit again and should help me to understand it better :-)

Thanks cg and xrj for all your help with this and I will mark this as solved.

Thanks

Andrew

[xrjf]

I hope you have put the code in order. Sorry, I'm perhaps a little late, but was busy.
Here is the timezoneinfo renamed as "myTimeZoneInfo" and a new class, that inherits from panel to draw from scratch a clock:

...and the code:

[pezza]

Thanks xrj.

By the way, my post shows that artemix also resolved this? I can't see any post from artemix, so not sure why they have got credit :-)

Anyway, yeah - I have typed it all out and i ended up creating the code for the time zone information again. It would seem that my original attempt was overkill (as is usually the case with my attempt at coding!) and all I needed was to declare some variables with the TimeZoneInfo class pulling each of the ID's of the timezones and then i could use the utcoffset value in the analog clocks to set each one to the utcoffset acquired from the TimeZoneInfo.

It seems to work, but of course at the moment, I am only testing in UK, but it gets the TimeZoneInfo based on the Now() function, so in theory...it should work.

I just need to get one of our colleagues in India to test to see if the other times still work when he runs it on his PC :-) I have still got a bit to do though, and now just starting on all the other DB parts such as admin pages for adding new members, schedules and teams etc.

To be honest, it is now more of a personal learning aid, as I have very little time to put in, so chances are they will use something else by the time i get to finish this.

Thanks

Andrew