3 Part question on vb.net sql and timers

[EDN Admin]

This is going to be kinda long so bear with me.
I have the following hack, er I mean code and it works 99%
[its about 250 lines]
The form has 2 timers named Clock and TCData
It has 1 Groupbox <br/>
It has 2 Richtext Boxes named TimeBox and NotesBox<br/>
It has 1 Button named ClkInBtn<br/>
And it has one Textbox named ClkInBtn<br/>

<pre class="prettyprint lang-vb Imports System.Data
Imports System.Data.SqlClient

Public Class TimeClock
Const NL As String = vbCrLf simple hack to rember what the code for newline is
Dim Now As DateTime

Private Sub TimeClock_Load(sender As System.Object, e As System.EventArgs) Handles MyBase.Load
Clock.Start()
TCData.Start()
End Sub
Private Sub TimeClock_Closing(sender As Object, e As System.ComponentModel.CancelEventArgs) Handles MyBase.Closing
TimeRe.Show()
End Sub
Private Sub Clock_Tick(sender As System.Object, e As System.EventArgs) Handles Clock.Tick
Now = DateTime.Now
TimeBox.Text = Now.ToString("hh:mm:ss tt")
End Sub
Private Sub ClkInBtn_Click(sender As System.Object, e As System.EventArgs) Handles ClkInBtn.Click
LoginOut()
End Sub
Private Function PopUser()
Try
Dim con As SqlConnection = New SqlConnection("Data Source=Stark;Initial Catalog=tcgpc;Persist Security Info=True;User ID=sa;Password=mypassword)
Dim i As Integer
Dim dt As New DataTable()
con.Open()
Dim sqlCmd As New SqlCommand("SELECT * from Users", con)
Dim sqlD As New SqlDataAdapter(sqlCmd)
sqlD.Fill(dt)
Dim UCount = dt.Rows.Count - 1
If dt.Rows.Count > 0 Then
For i = 0 To UCount

If i = 0 Then
FillUser(i, 50, 30)
End If
If i = 1 Then
FillUser(i, 50, 70)
End If
If i = 2 Then
FillUser(i, 50, 110)
End If
If i = 3 Then
FillUser(i, 50, 150)
End If
If i = 4 Then
FillUser(i, 50, 190)
End If
If i = 5 Then
FillUser(i, 50, 230)
End If
If i = 6 Then
FillUser(i, 50, 270)
End If
If i = 7 Then
FillUser(i, 50, 310)
End If
If i = 8 Then
FillUser(i, 50, 350)
End If

2nd column
If i = 9 Then
FillUser(i, 280, 30)
End If
If i = 10 Then
FillUser(i, 280, 70)
End If
If i = 11 Then
FillUser(i, 280, 110)
End If
If i = 12 Then
FillUser(i, 280, 150)
End If
If i = 13 Then
FillUser(i, 280, 190)
End If
If i = 14 Then
FillUser(i, 280, 230)
End If
If i = 15 Then
FillUser(i, 280, 270)
End If
If i = 16 Then
FillUser(i, 280, 310)
End If
If i = 17 Then
FillUser(i, 280, 350)
End If
3rd column
If i = 18 Then
FillUser(i, 510, 30)
End If
If i = 19 Then
FillUser(i, 510, 70)
End If
If i = 20 Then
FillUser(i, 510, 110)
End If
If i = 21 Then
FillUser(i, 510, 150)
End If
If i = 22 Then
FillUser(i, 510, 190)
End If
If i = 23 Then
FillUser(i, 510, 230)
End If
If i = 24 Then
FillUser(i, 510, 270)
End If
If i = 25 Then
FillUser(i, 510, 310)
End If
If i = 26 Then
FillUser(i, 510, 350)
End If
Next
con.Close()
con = Nothing
i = Nothing
dt = Nothing
sqlCmd = Nothing
sqlD = Nothing
UCount = Nothing
End If
Catch ex As Exception
NotesBox.Text = "First Error " + ex.Message.ToString()
End Try
Return Nothing
End Function
Private Sub FillUser(ByVal UN, ByVal POST, ByVal POSL)
Try
Dim dcon As SqlConnection = New SqlConnection("Data Source=Stark;Initial Catalog=tcgpc;Persist Security Info=True;User ID=sa;Password=mypassword)
Dim dta As New DataTable()
dcon.Open()
Dim dsqlCmd As New SqlCommand("SELECT * from Users", dcon)
Dim sqlDa As New SqlDataAdapter(dsqlCmd)
sqlDa.Fill(dta)
Dim PositionT = POST
Dim PositionL = POSL
Dim IconP = 10
If UN < 9 Then
IconP = 10
ElseIf UN > 8 And UN < 18 Then
IconP = 240
ElseIf UN > 17 And UN < 27 Then
IconP = 470
End If
Dim lbl1 As Label = New Label()
Dim Pb1 As PictureBox = New PictureBox()
GroupBox1.Controls.Add(lbl1)
GroupBox1.Controls.Add(Pb1)
lbl1.AutoSize = True
lbl1.Font = New Font(lbl1.Font.FontFamily, 22)
lbl1.Location = New System.Drawing.Point(PositionT, PositionL)
lbl1.Text = dta.Rows(UN)("UserName").ToString()
Dim Ico = dta.Rows(UN)("Icon").ToString()
Dim LI = dta.Rows(UN)("LoggedIn").ToString()
Pb1.SizeMode = PictureBoxSizeMode.StretchImage
Pb1.Location = New System.Drawing.Point(IconP, POSL)
Pb1.Size = New System.Drawing.Size(30, 30)
If Ico = 1 Then
Pb1.Image = My.Resources._1
ElseIf Ico = 2 Then
Pb1.Image = My.Resources._2
ElseIf Ico = 3 Then
Pb1.Image = My.Resources._3
ElseIf Ico = 4 Then
Pb1.Image = My.Resources._4
End If
If LI = 1 Then
lbl1.ForeColor = System.Drawing.Color.Blue
Else
lbl1.ForeColor = System.Drawing.Color.Black
End If
dcon.Close()
dcon = Nothing
dta = Nothing
dsqlCmd = Nothing
sqlDa = Nothing
Catch ex As Exception
NotesBox.Text = " 2nd Error " + ex.Message.ToString()
End Try
End Sub
Private Sub TCData_Tick(sender As System.Object, e As System.EventArgs) Handles TCData.Tick
PopUser()
End Sub
Private Sub LoginOut()
Try
Dim cn As SqlConnection = New SqlConnection("Data Source=Stark;Initial Catalog=tcgpc;Persist Security Info=True;User ID=sa;Password=mypassword)
Dim cmd As SqlCommand = New SqlCommand()
Dim dr As SqlDataReader
Dim UN As String = EmpIdBox.Text
cmd.Connection = cn
cmd.CommandText = "Select UserID,LoggedIn From Users Where UserID =" & UN & ""
cn.Open()
dr = cmd.ExecuteReader()
Do While dr.Read()
If dr.Item("LoggedIn").ToString() = 1 Then
Dim con As SqlConnection = New SqlConnection("Data Source=Stark;Initial Catalog=tcgpc;Persist Security Info=True;User ID=sa;Password=mypassword)
yup
Dim rows As Integer
Dim myCommand As SqlCommand = con.CreateCommand()
Try
con.Open()
myCommand.CommandText = "Update Users Set LoggedIn = 0 WHERE UserID = " & UN & ""
rows = myCommand.ExecuteNonQuery()
Catch ex As SqlException
MessageBox.Show(ex.Message)
Finally
con.Close()
con = Nothing
myCommand = Nothing
End Try
EmpIdBox.Text = ""
Else
Nope
Dim con As SqlConnection = New SqlConnection("Data Source=Stark;Initial Catalog=tcgpc;Persist Security Info=True;User ID=sa;Password=mypassword)
Dim rows As Integer
Dim myCommand As SqlCommand = con.CreateCommand()
Try
con.Open()
myCommand.CommandText = "Update Users Set LoggedIn = 1 WHERE UserID = " & UN & ""
rows = myCommand.ExecuteNonQuery()
Catch ex As SqlException
MessageBox.Show(ex.Message)
Finally
con.Close()
con = Nothing
myCommand = Nothing
End Try
EmpIdBox.Text = ""
End If
Loop
dr.Close()
cn.Close()
cn = Nothing
cmd = Nothing
dr = Nothing
Catch ex As Exception
NotesBox.Text = "Error 3 " + ex.Message.ToString()
End Try
End Sub
End Class[/code]
There are 3 things that do not work properly though
1 when I sign in or out it doesnt update the form even though the timer will show if I add a new user whilst running [though it doesnt update if I remove a user ]
2 It seems that every time to refreshes the GroupBox is takes some memory and it slowly keeps eating memory and I cant see anywhere that I am leaving anything open.
3 is there a way I can change the part<br/>

<pre class="prettyprint lang-vb For i = 0 To UCount
If i = 0 Then
FillUser(i, 50, 30)
End If
Next [/code]
so I dont have to specifically hard code them the way I have it now?

lastly does this forum have a way to preview a post before posting?

<br/>

View the full article