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
