Hi,

I am new to VB.NET and am working on what is pretty much my first app. The app is based on a sql data source (created within Visual Studio) and basically is intended to show who is on-call when the app is loaded.

So, i have some test data, and my form currently shows all the current times in the different support areas that we have and that all seems to be working fine.

The second part runs a function which creates labels at runtime based on how many teams are set up and populates the label.text property with the Team Name and the person on-call. It does this by using a datareader/query based on the current time and then querying the schedule table.

Now this all works, but what I am struggling with is getting it to update when either a schedule is changed in the table (i.e. an admin elsewhere may change the person who is covering that schedule) or when the time changes to another persons start of shift.

I am calling the function from a timer control which runs every 5 seconds, but I might even just use a button to refresh to make it more efficient, but both methods is not updating the persons name as when it re-runs, it seems to have the old orignal data. If I close the debugging and restart again, it gets the new data.

Is there a method or function that I need to use to reset the query and to pull in a new query?

Now, I know that my code will not be the best, but it is my first attempt so go easy on me :-)

    Public Function LoadLabels()

        ' Set up Text Boxes for the Teams

        Dim StartPosX As Integer = 12
        Dim StartPosY As Integer = 200
        Dim LabelWidth As Integer = 108
        Dim LabelHeight As Integer = 108
        Dim GapToLeave As Integer = 6

        Dim s, s2 As String
        s = "Data Source=.\SQLEXPRESS;AttachDbFilename=" & Application.StartupPath & "\ShiftRota.mdf;Integrated Security=True;User Instance=True"
        s2 = "Data Source=.\SQLEXPRESS;AttachDbFilename=" & Application.StartupPath & "\ShiftRota.mdf;Integrated Security=True;User Instance=True"

        Dim reader As SqlDataReader, reader2 As SqlDataReader, cmd As New SqlCommand, cmd2 As New SqlCommand
        Dim cn As New SqlConnection(s), cn2 As New SqlConnection(s2)


        cmd = cn.CreateCommand
        cmd2 = cn2.CreateCommand
        cn.Open()
        cn2.Open()

        cmd.CommandText = "SELECT db_team_TeamName, db_team_TeamLeader FROM dbo.db_Team"

        reader = cmd.ExecuteReader

        While (reader.Read())

            Dim myvalue As String = ""
            Dim myvalue2 As String = ""
            Dim myvalue3 As String = ""
            Dim myvalue4 As String = ""


            myvalue = reader("db_team_TeamName").ToString()

            cmd2.CommandText = "SELECT db_Schedule.db_schedule_Start, db_Schedule.db_schedule_Finish, db_TeamMember.db_teammember_FirstName, db_Team.db_team_TeamName FROM db_Schedule INNER JOIN db_TeamMember ON db_Schedule.db_schedule_TeamMemberID = db_TeamMember.db_teammember_ID INNER JOIN db_Team ON db_TeamMember.db_teammember_TeamID = db_Team.db_team_ID WHERE (db_Schedule.db_schedule_Start < { fn NOW() }) AND (db_Schedule.db_schedule_Finish > { fn NOW() }) AND (db_Team.db_team_TeamName LIKE N'" & myvalue & "')"

            reader2 = cmd2.ExecuteReader

            If reader2.HasRows = False Then

                myvalue4 = "Nobody on call for this team"

            End If

            While (reader2.Read())

                myvalue2 = reader2("db_teammember_FirstName").ToString & vbCrLf

                myvalue3 = reader2("db_team_TeamName").ToString

                If myvalue3 = myvalue Then

                    myvalue4 = reader2("db_teammember_FirstName")

                Else

                    myvalue4 = "Nobody on call for this team"

                End If

            End While

            reader2.Close()

            Dim lblTeam As New Label

            With lblTeam
                .Name = "lblTeam_" & myvalue
                .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 = myvalue & vbCrLf & myvalue4
                .AutoSize = False
                .BorderStyle = System.Windows.Forms.BorderStyle.Fixed3D

                Me.Controls.Add(lblTeam)

            End With

            StartPosX = StartPosX + LabelWidth + GapToLeave

        End While

        reader.Close()
        cn.Close()
        cn2.Close()
        s = ""
        s2 = ""
        cmd.CommandText = ""
        cmd2.CommandText = ""


    End Function

Thanks

Andrew

Why use labels? Why not a datagrid or simply a listbox or a multiline textbox?
BTW, why not enclose in a try-catch and use a name more meaningful as myvalue?
I would set one cmd.CommandText (1 connection) to:
"SELECT dbTeam.,db_TeamMember., db_Schedule.* " + _
"WHERE (db_Schedule.db_Schedule_Start < "+...+" finish > { fn NOW() }" +
"AND db_Schedule.db_schedule_TeamMemberID = db_TeamMember.db_teammember_ID"+
"AND db_Team.db_Team_ID = db_TeamMember.db_TeamMember_TeamID "
Maybe also add a sort to the CommandText?
Here on, continue with while (reader.Read()) ...

Thanks for the reply.

I know that the code needs tidying up, but that isn't my issue. I want to get it functioning first before i tidy it up and make the names more meaningful. There is lots in there that functions but needs tidying up.

What I wanted to know was how to clear/refresh the connection, so that the next time my timer control or button runs the function, it takes in new data and doesn't use the old data.

I want the form to be simple, aligned in square boxes, so labels seem to fit the bill. Is there anything wrong with using a label? Would this prevent me from achieving the data refresh? I guess if a datagrid can present it in the same way then I could use that, but are these other methods any more efficient than a label to make it beneficial?

There isn't a requirement to have the teams arranged in a sorted order, so I dont think the sort matters. There is likely to only be 5 teams and 1 person per team, but yes, the point about the 1 connection would be much cleaner and less work to perform.

Thanks

Andrew

It seems from everywhere i look that a reader cannot be reset.

So, what would be an alternative/better solution to extract the data from my SQL DB in order to simply pull some information?

As I am querying in my SQL statement for date, there won't be a lot of data...potentially around 5 or 6 teams, but there should never really be more than 1 person on-call for each team, so in theory, only about 6 records returned.

I managed to get this connection from another web source, but the more i read, i seem to be seeing reference to using an adapter and binding source, although my DB is created from within the project and there is a dataset and binding source already there as a result of creating the connection.

So, beginning to think that I just need to use these, but don't know where to start :-(

Any help would be really appreciated on getting at the data and being able to refresh the connection on a timed basis. I will cross the bridge of adding to the data once I have got this sorted :-)

Thanks

Andrew

Hi,

Ok, so I have tidied up my code a bit and taken the advice to have just 1 connection. My original reason for having 2 was that I wanted to display all the teams first whether there was anyone on call or not, however it will just have to be assumed that if no team displayed, then there is nobody on call for that team :-)

I have also changed the code to Using blocks which I think looks much neater, and made the variables more meaningful.

However, even with all of this, I still cannot find a way to completely refresh the connection/reader so that the next time it runs, it pulls in fresh data. It is still inisting on keeping the original data from the first load no matter how many .Dispose() or .Close() or =Nothing etc.

Here is my updated code...

` Public Function LoadLabels()

    ' 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.


    ' Declare Label positions and sizes

    Dim StartPosX As Integer = 12
    Dim StartPosY As Integer = 200
    Dim LabelWidth As Integer = 108
    Dim LabelHeight As Integer = 108
    Dim GapToLeave As Integer = 6

    ' Declare and set SQL Connection String

    Dim sqlString As String

    sqlString = "Data Source=.\SQLEXPRESS;AttachDbFilename=" & Application.StartupPath & "\ShiftRota.mdf;Integrated Security=True;User Instance=True"

    ' 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

    Using cn As New SqlConnection(sqlString)

        Using cmd As New SqlCommand("SELECT db_Team.db_team_TeamName, db_TeamMember.db_teammember_FirstName, db_TeamMember.db_teammember_Surname, db_TeamMember.db_teammember_OnCallPhone, db_TeamMember.db_teammember_Location, db_Schedule.db_schedule_Start, db_Schedule.db_schedule_Finish FROM db_Team INNER JOIN db_TeamMember ON db_Team.db_team_ID = db_TeamMember.db_teammember_TeamID INNER JOIN db_Schedule ON db_TeamMember.db_teammember_ID = db_Schedule.db_schedule_TeamMemberID WHERE (db_Schedule.db_schedule_Start < { fn NOW() }) AND (db_Schedule.db_schedule_Finish > { fn NOW() })", cn)

            ' Open Connection

            cn.Open()

            ' Use Data Reader to store data from query

            Using reader As SqlDataReader = cmd.ExecuteReader()

                While reader.Read()

                    ' Set values to those from the Data Reader

                    teamName = reader("db_team_TeamName").ToString()
                    teamMemberFirstName = reader("db_teammember_FirstName").ToString()
                    teamMemberSurname = reader("db_teammember_Surname").ToString()
                    teamMemberMobile = reader("db_teammember_OnCallPhone").ToString()
                    teamMemberLocation = reader("db_teammember_Location").ToString()

                    ' 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

                        ' Add the label control to the form

                        Me.Controls.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

                End While

                ' Close the reader

                reader.Close()

            End Using

        End Using

    End Using

End Function`

I am hoping someone can help me to get the connection refreshed.

Thanks

Andrew

Yes, your code now looks much neater. Have you seen this thread before?

Ok, thanks xrj.

So, i have noticed that that is C# and my code is VB.NET but I have picked up 2 points from that article:

1) Using a Try Catch block. My understanding that a Using block actually is as good as it already includes a try catch type? I am new though, so still learning all of this :-) I have seen people refer to the Finally block being better/more reliable at closing connections, but had just thought that by using the Using block, this was just as good.

2) The other difference is that this says to create a Public Void. Mine is a Function, so is this the difference? Is this specific to C# and effectively the same as a Function?

I can certainly try those 2 though, I have tried just about everything else LOL

Thanks for the reply.

Andrew

Well, a Try-Catch would allow, for example if there are any transactions to cancel.
Also, if the timer calls once the function without any problem and suppose that for some reason in a next call there is an error, what would happen? Suppose cn.open() fails, a exception would rise and you would get no more information out from the database (I guess the timer thread would probably stop): you would only see the initial data in the labels.
I think its a good practice to enclose in try-catch and always, but especially during debugging, pop-out and show any error.

Be careful, though, with threading and pop-up windows: is not a good practice.
Really, the thread should send a message, in case of error, to the main thread to be noticed.

I guess your timer is an instance of System.Windows.Forms.Timer. Then, add an label to the form, say "label_error", and modify the Timer.Tick event so:

    Private Sub Timer1_Tick(sender As Object, e As EventArgs) Handles Timer1.Tick
        Try

            ' your code goes here

        Catch ex As Exception
            Timer1.Stop()
            label_Error.Text = ex.Message
        End Try
    End Sub

Ok, thanks for that.

I will have a go with all that this evening when i get home.

Thanks

Andrew

hmmm ok, so I have tried the Try...Catch block and it runs through the code ok and doesn't enter the catch part so no error reported, but it is still using old data :-(

I have followed through the code, and i can see the state of the various connections changing to Closed and my connection string and commandtext being set to Nothing or "", but they are effectively still there (different to when it first triggers as if i hover over them at first, nothing is displayed but after i close everything, the connection still seems to be there, although the properties within it reflect the closure)

I just don't know how to tell the blasted thing to get rid/delete/forget/give up!! LOL

I can see that the timer is working as stepping through the code i can see it repeating. I also have a button which does the same call to the funtion and even clicking that doesn't refresh it, so it isn't just the timer control.

I did wonder at one point whether the timer was causing a glitch with whether the connection was opened again, given that it runs every 5 seconds (at the time), but I removed this last night and just had the button, but still to no avail :-(

I am beginning to think that I might have to use an adapter to bind this to, but i need to do a bit more reading to work out that one.

Oh well, back to the drawing board. I just can't believe how difficult it is to code an instruction to completely close and re new a connection!

Thanks

Andrew

Well, perhaps it does not make the difference, but:

    Public Function LoadLabels() As Int32
        Dim cn As New SqlClient.SqlConnection("your connection string")
        Dim cmd As New SqlClient.SqlCommand( _
            "SELECT db_Team.db_team_TeamName, db_TeamMember.db_teammember_FirstName, db_TeamMember.db_teammember_Surname, db_TeamMember.db_teammember_OnCallPhone, db_TeamMember.db_teammember_Location, db_Schedule.db_schedule_Start, db_Schedule.db_schedule_Finish FROM db_Team INNER JOIN db_TeamMember ON db_Team.db_team_ID = db_TeamMember.db_teammember_TeamID INNER JOIN db_Schedule ON db_TeamMember.db_teammember_ID = db_Schedule.db_schedule_TeamMemberID WHERE (db_Schedule.db_schedule_Start < { fn NOW() }) AND (db_Schedule.db_schedule_Finish > { fn NOW() })", cn)
        cmd.CommandType = CommandType.Text
        Dim da As New SqlClient.SqlDataAdapter(cmd)
        Dim results As New DataTable("Results")
        'results.Locale = Globalization.CultureInfo.CurrentCulture
        Try
            cn.Open()
            da.Fill(results)
            label_Error.Text = results.Rows.Count + " Rows Found."
            For i As Int32 = 0 To results.Rows.Count - 1
                Dim teamName As String = _
                    results.Rows(i).Item("db_team_TeamName").ToString()
                Dim teamMemberFirstName As String = _
                    results.Rows(i).Item(" teamMemberFirstName").ToString
                ' ....
                Dim lblTeam As New Label
                With lblTeam
                    '...
                    .Text = teamName + vbCrLf + _
                        teamMemberFirstName ' + ....
                    '...
                End With
            Next
        Catch ex As Exception
            ' Display any errors...   
            label_Error.Text = "ERROR: " + ex.Message
            da.Dispose()
        Finally
            cn.Close()
        End Try

    End Function

here an example what i do with a label and a reader :

Try
            Using conn As New SqlConnection("Your Connection String")
                conn.Open()
                Dim command As New SqlCommand("select team_name, address, contact_person from team", conn)
                Dim reader As SqlDataReader = command.ExecuteReader
                If reader.Read() Then
                    Label1.Text = reader("team_name").ToString & vbCrLf & reader("address").ToString & vbCrLf & reader("contact_person").ToString
                End If
                reader.Close()
                command.Dispose()
                conn.Close()
            End Using
        Catch ex As Exception
            MsgBox(ex.Message)
        End Try

i have test it too with a timer and a button, it should work for you.

Have you answered?: what kind of source database am I going to access? Is it a TextFile, MySql, Access, Excel, Sql Server, and so on? The answer will determine 1) what kind of library OleDb, SqlServer, ... to use and 2) what connection string should I use.

I hadn't answered yet, as first chance i have had :-) I gave up last night as I tried your code (first code) and it still wasn't clearing the connection, so i am beginning to think now that it is the connection itself.

As per my connection string, it is a SQLEXPRESS, but it is one that I have created from within the project so whether that is indeed SQLEXPRESS, i am not sure. It's a mdf that is created by the wizard. Maybe the closure isn't working if i have referenced an incorrect SQL connection type?? Just clutching at straws now.

Something different happens though on initial load, because everytime i quit debugging and start again, the new data is there. I just cant get it to refresh during.

I haven't tried your last code, which for the sake of sanity, I will try this evening or tomorrow

I am not 100% sure, but I think the connection string should look like this:

Data Source=.\SQLExpress;Integrated Security=true;
User Instance=true;AttachDBFilename=|DataDirectory|\InstanceDB.mdf;
Initial Catalog=InstanceDB;

You may also inspect connections strings yourself here

...and for "user instances" Click Here

I believe that your issue is that you are creating a label for each person, but never attempt to remove them before updating the data. So if a person's shift is over you will still see their label because you haven't removed it.

How to fix it:
Keep track of the name of the labels you are adding and delete them each time before querying the database. I will use a list:

Public Class Form1

        'list to keep track of label names
        'that were dynamically added
        Dim lblNameList As List(Of String) = New List(Of String)

        ....

Delete the old labels (from the previous query), before getting the updated data from the database:

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

            Using cn As New SqlConnection(sqlString)

            ...

Clear the previous list and add the names of the newly created labels to our list:

    'clear the previous list
    lblNameList.Clear()

    'keep track of labels that were created
    'add lblTeam.Name to list
    lblNameList.Add(lblTeam.Name)

    ' Add the label control to the form
    Me.Controls.Add(lblTeam)

I think updating your form every 5 seconds is excessive. Every 15 to 30 seconds is more than enough. Once per 45 or 60 seconds may be better.

Also, in my opinion it is not good practice to include the table names in the column names (unless you are using a reserved word in your column name and can't find any other name to name it).

If you should decide to place your labels onto a panel, you will need to reference each label by it's parent. To do so, you need to first find it's parent. So instead of:

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

           Using cn As New SqlConnection(sqlString)

            ...

You would do something like this:

        'remove old labels
        For Each lblName In lblNameList

            'holds the parent control of
            'the control we are looking for
            Dim parentCtrl As Control = Nothing

            'find the label name and put 
            'results into list
            Dim ctrlList = Me.Controls.Find(lblName, True).ToList()

            'find parent control name
            'this is so we can reference the 
            'control in relation to it's parent.
            'This is necessary if an object
            'is placed on a panel.
            For Each ctrlItem In ctrlList
                parentCtrl = ctrlItem.Parent
            Next

            If parentCtrl IsNot Nothing Then

                'remove the label
                parentCtrl.Controls.RemoveByKey(lblName)
            End If

            'Me.Controls.RemoveByKey(lblName)
        Next


        Using cn As New SqlConnection(sqlString)

            ...

Here's some of the code I used. The database structure is slightly different from what you currently have.

I use the following table names:

  • Team
  • TeamMember
  • Schedule

Team:
1857c8ab21a8994b719f43a7701aaf70

ebc52fb9aa11fa5e472d1ffe24e7edbc

TeamMember:
9218831db65cd60fa4cbfdb9ace28c43

bd4806bee9f4139639fbbd155f9ed770

Schedule:
6c84dbba35db3f70560019d416df0ebc

8e6f58845dc62628ee7d9385eafa6e1d

fbf740cf3ceba571050c500f1754a83d

And here is the sql select statement text:

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() })"

The connection strings I used:

'Project => Add New Item => Service-based Database
'SQLExpress as a service (Service-based Database)
'Private connectStr = "Server=.\SQLExpress;Database=ShiftRota;Trusted_Connection=Yes;"

'Project => Add New Item => Local Database
'SQLExpress as a file (Local Database)
'Private connectStr As String = "Data Source=.\SQLEXPRESS;AttachDbFilename=" & Application.StartupPath & "\ShiftRota.mdf;Integrated Security=True;User Instance=True"

'Project => Add New Item => Local Database
'SQLExpress as a file (Local Database)
Private connectStr As String = "Data Source=.\SQLEXPRESS;AttachDbFilename=" & Application.StartupPath & "\ShiftRota.mdf;Trusted_Connection=Yes;User Instance=True"

"SQLExpress.vb" and "DefaultData.vb" are attached in "Files.zip" below. The connection string is specified in the top of "SQLExpress.vb".

To create a database using "SQLExpress.vb":

SQLExpressDB.CreateDatabase("ShiftRota")

To create "Team" table:

SQLExpressDB.CreateTeamTbl()

To create "TeamMember" table:

SQLExpressDB.CreateTeamMemberTbl()

To create "Schedule" table:

SQLExpressDB.CreateScheduleTbl()

To insert the default data (in "DefaultData.vb"):

DefaultData.InsertData()

Here is the table creation code. It is in "SQLExpress.vb" which is in "Files.zip". I will put it here in case the file becomes unavailable.

Connection String (same as above):

'Project => Add New Item => Service-based Database
'SQLExpress as a service (Service-based Database)
'Private connectStr = "Server=.\SQLExpress;Database=ShiftRota;Trusted_Connection=Yes;"

'Project => Add New Item => Local Database
'SQLExpress as a file (Local Database)
'Private connectStr As String = "Data Source=.\SQLEXPRESS;AttachDbFilename=" & Application.StartupPath & "\ShiftRota.mdf;Integrated Security=True;User Instance=True"

'Project => Add New Item => Local Database
'SQLExpress as a file (Local Database)
Private connectStr As String = "Data Source=.\SQLEXPRESS;AttachDbFilename=" & Application.StartupPath & "\ShiftRota.mdf;Trusted_Connection=Yes;User Instance=True"

CreateTeamTbl:

    Public Sub CreateTeamTbl()

        Try

            Using cn As New SqlConnection(connectStr)
                Dim sqlText As String = String.Empty

                sqlText = "CREATE TABLE Team (teamID nvarchar(25) NOT NULL "
                sqlText += "CONSTRAINT PK_Team_teamID PRIMARY KEY,"
                sqlText += "name nvarchar(25))"

                'open connection
                cn.Open()

                'create new SqlCommand
                Using sqlCmd As New SqlCommand(sqlText, cn)

                    'execute
                    sqlCmd.ExecuteNonQuery()
                End Using

                Console.WriteLine("Table: Team created.")
            End Using

        Catch ex As SqlClient.SqlException
            Console.WriteLine("Error:: CreateTeamTbl: " & ex.Message)
        Catch ex As Exception
            Console.WriteLine("Error:: CreateTeamTbl: " & ex.Message)
        End Try
    End Sub

CreateTeamMemberTbl:

    Public Sub CreateTeamMemberTbl()

        Try

            Using cn As New SqlConnection(connectStr)
                Dim sqlText As String = String.Empty

                sqlText = "CREATE TABLE TeamMember (employeeID nvarchar(25) NOT NULL "
                sqlText += "CONSTRAINT PK_TeamMember_employeeID PRIMARY KEY, "
                sqlText += "firstName nvarchar(50), "
                sqlText += "surName nvarchar(50), "
                sqlText += "onCallPhone nvarchar(25), "
                sqlText += "location nvarchar(25), "
                sqlText += "teamID nvarchar(25), "
                sqlText += "CONSTRAINT FK_TeamMember_TeamID_Team_TeamID FOREIGN KEY(teamID) "
                sqlText += "REFERENCES Team(teamID) "
                sqlText += "ON DELETE CASCADE ON UPDATE CASCADE)"

                'open connection
                cn.Open()

                'create new SqlCommand
                Using sqlCmd As New SqlCommand(sqlText, cn)

                    'execute
                    sqlCmd.ExecuteNonQuery()
                End Using

                Console.WriteLine("Table: TeamMember created.")
            End Using

        Catch ex As SqlClient.SqlException
            Console.WriteLine("Error:: CreateTeamMemberTbl: " & ex.Message)
        Catch ex As Exception
            Console.WriteLine("Error:: CreateTeamMemberTbl: " & ex.Message)
        End Try
    End Sub

CreateScheduleTbl:

    Public Sub CreateScheduleTbl()

        Try

            Using cn As New SqlConnection(connectStr)
                Dim sqlText As String = String.Empty

                sqlText = "CREATE TABLE Schedule (teamMemberEmpID nvarchar(25) NOT NULL, "
                sqlText += "start datetime NOT NULL, "
                sqlText += "finish datetime, "
                sqlText += "CONSTRAINT PK_teamMemberEmpID_start "
                sqlText += "PRIMARY KEY(teamMemberEmpID, start), "
                sqlText += "CONSTRAINT FK_Schedule_TeamMemberEmpID_TeamMember_EmployeeID FOREIGN KEY(teamMemberEmpID) "
                sqlText += "REFERENCES TeamMember(employeeID) "
                sqlText += "ON DELETE CASCADE ON UPDATE CASCADE)"

                'open connection
                cn.Open()

                'create new SqlCommand
                Using sqlCmd As New SqlCommand(sqlText, cn)

                    'execute
                    sqlCmd.ExecuteNonQuery()
                End Using

                Console.WriteLine("Table: Schedule created.")
            End Using

        Catch ex As SqlClient.SqlException
            Console.WriteLine("Error:: CreateScheduleTbl: " & ex.Message)
        Catch ex As Exception
            Console.WriteLine("Error:: CreateScheduleTbl: " & ex.Message)
        End Try
    End Sub

Thanks for the comments.

I will have a try of this over this weekend, but even the variables show the old data as well before it gets to the part where it sets the labels and these variables are set to "" and I have also tried setting them to nothing before they are re-assigned.

I'm convinced that it has to be something to do with the data not coming in when it is refreshed, as if i try running the code with nobody on-call, it correctly shows no labels. If I open the table and add a schedule, i would expect it to then run through at least the sections within the Try part, but it isn't so it is as if on the next run, it still has no records found, and I have also tried with an amendment to the person on-call and when I have stepped through the code again after it has initially set a label, it has set the variables to Nothing but when they are set to the new values, again the variables show the old unchanged data :-(

So, it does seem that the actual results brought from the query are not the latest data.

I will try out these different suggestions from you both and will report back.

At the moment, it is one of those projects for work, that I am doing in my own time as its not critical and therefore I just thought I would have a go and learn a bit on the way :-) So it is just a case of doing a bit whenever i can fit it in with my OU work.
Thanks

Andrew

Here is a project I created. It is based on the code you posted above.

It requires that you have SQLExpress installed.

Download SQL Management Studio. It will make it easier for you when using a service-based database. (You can use the 2012 version with SQLExpress 2005/2008)

Thanks cgeier,

I am running VS2005 at the moment, so it won't open. I have managed to open the project "files" as part of the opening wizard, but loads of build errors so I guess the code is dependent on stuff that isn't in 2005.

I am going to have a look through the code though and see if i can identify any key differences that may hopefully help to work out where my code is going wrong :-)

I really appreciate all the help from you both on this, and I am sure that when I do find the issue, it will be one of those moments where I kick myself! lol

Thanks

Andrew

Ok, so the executable, i hav got to load...created database and tables and updated some data and I can see it updating straight away!!

Now, the major difference here is that in mine, the database seems to be internal to the project as it doesn't appear in SQL Management Studio either under the local PC connection or the PC\sqlexpress, so I wonder if this is the issue as to why the connections are not releasing or updating. The other difference of course is that up till now, I have been debugging the project, rather than running as an exe, so whether the internal debug may not be releasing the connection, I don't know.

Anyway, I guess my next step now is to create this database within SQL itself and edit my connection and then see if it makes any difference.....and then take it from there :-) I guess it is process of elimination!

Thanks again, and I will update further once I have done some more tinkering.

Andrew

The code should work with your local project database too--I tested it both ways. In "SQLExpressDB.vb" (connectStr), comment out the "connectStr" that is currently used and uncomment one of the other ones. I couldn't include the database in the .zip file because it made the file too large.

Read about SQL Server Express User Instances

*"...Databases running on a user instance are opened in single-user mode only, and it is not possible for multiple users to connect to databases running on a user instance...."

I also noticed that when VS is open, the database connection is not released, however it didn't affect my project. Only if you tried to connect to the database file (or attach it in SQL Management Studio). If you eliminate the using statements and close the connection yourself, you may not have that problem any more.

You may notice some flashing when an update is occuring because the labels are removed and added again. A work-around, would be to check to see if any of the data has changed. If it has, delete the labels and re-add them. If not, don't do anything. Could use another list and compare the lists using List.Except (compare the names).

@cgeier: your code works like a charm. Just did some minor changes. For example, MainForm.getData is a function returning a datatable:

    Public Function getData(ByRef err As Exception) As DataTable
        ' 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.
        Dim retDataTable As New DataTable
        Try
            Dim rowCount As Integer = 0

            'update status
            ToolStripStatusLabel1.Text = "Status: "

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


            ' 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()
                    Using cmd As New SqlCommand(sqlText, cn)
                        Dim dA As New SqlDataAdapter(sqlText, cn)
                        Try
                            dA.Fill(retDataTable)
                        Catch ex1 As Exception
                            Throw New Exception("Error in dataAdapter. " + ex1.Message)
                        End Try
                    End Using
                Catch ex2 As Exception
                    Throw New Exception("Error in connection. " + ex2.Message)
                End Try
            End Using
        Catch ex As Exception
            'prevents lots of error messagebox windows
            'if an error occurs
            errorCount += 1
            err = New Exception("Error (SQLDataReader): " + ex.Message)
        Finally
            '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 Try
        Return retDataTable
    End Function

So, all previous calls to getData now call doTasks():

    Private Sub localTimer_Tick(ByVal sender As Object, ByVal e As EventArgs) Handles localTimer.Tick

        'this occurs every # of ms set in localTimer.Interval
        doTasks()
    End Sub
    Sub doTasks()
        Try
            Dim err As Exception = Nothing
            Dim dt As DataTable = getData(err)
            If err IsNot Nothing Then
                ToolStripStatusLabel1.Text = "Status: Error " & err.Message
            Else
                If dt IsNot Nothing Then
                    showData(dt)
                Else
                    'remove old labels
                    For Each lblName In lblNameList
                        Me.Controls.RemoveByKey(lblName)
                    Next
                End If
            End If
        Catch ex As Exception
            ToolStripStatusLabel1.Text = "Status: Error " & ex.Message
        End Try
    End Sub

New process showData, clears and populates the labels, just as before, but the data is contained in datatable dtClick Here:

    Sub showData(dt As DataTable)
        ' Use Data Reader to store data from query
        Try
            ' Declare Label positions and sizes

            Dim StartPosX As Integer = 12
            Dim StartPosY As Integer = 200
            Dim LabelWidth As Integer = 108
            Dim LabelHeight As Integer = 108
            Dim GapToLeave As Integer = 6


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

            ' 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 = ""

            For Each row As DataRow In dt.Rows
                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

                    ' Add the label control to the form

                    'keep track of labels that were created
                    'add lblTeam.Name to list
                    lblNameList.Add(lblTeam.Name)

                    Me.Controls.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

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

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

        Catch ex As SqlException
            Beep()
            ToolStripStatusLabel1.Text = "Status: Error " & ex.Message
        Catch ex As Exception
            ToolStripStatusLabel1.Text = "Status: Error " & ex.Message
        End Try

    End Sub

The mentioned modifications.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.