I
itfetish
Ok heres my problem, I have a database which has a table in it that
has all the staff members who are currently signed out of hte office.
That has their staff ID, and the time they are out till etc, then
another database has the staff table in it, which matches the ID to
names.
I am trying to do a basic query for all people who are signed out at
the moment for a front page of our intranet. I've got that all sorted,
but I can only sort the results by StaffID (which is uselss) or Time
due back in, not by name like I want.
Here is my code so far:
conn.Open()
conn2.Open()
Dim SignedOutCommand As New SqlCommand("Select StaffID,
InTime, HaveMob FROM StaffOut ORDER BY InTime", conn)
Dim FirstName
Dim LastName
Dim x = 0
Dim loopcount = 0
Dim IDReader As SqlDataReader =
SignedOutCommand.ExecuteReader()
While IDReader.Read()
Dim InTime
Dim HaveMob
Dim InDate
Dim ComingIn
StaffID = IDReader.Item("StaffID")
ComingIn = IDReader.Item("InTime")
InDate = Left(ComingIn, 10)
InTime = Right(ComingIn, 11)
Dim NameCommand As New SqlCommand("Select FirstName,
LastName FROM Staff WHERE StaffID = '" & StaffID & "'", conn2)
Dim NameReader As SqlDataReader =
NameCommand.ExecuteReader()
While NameReader.Read()
FirstName = NameReader.Item("FirstName")
FirstName = ToTitleCase(FirstName)
LastName = NameReader.Item("LastName")
LastName = ToTitleCase(LastName)
If DateValue(InDate) > DateValue(Now()) Then
NameLabel.Text &= "<tr><td>" & FirstName & " " &
LastName & "</td><td><Font color='blue'>" & InDate & "</font><br></
tr>"
ElseIf DateValue(InDate) = DateValue(Now) Then
If TimeValue(Now) > TimeValue(InTime) Then
NameLabel.Text &= "<tr><td>" & FirstName & " "
& LastName & "</td><td><Font color='red'>" & InTime & "</font><br></
tr>"
Else
NameLabel.Text &= "<tr><td>" & FirstName & " "
& LastName & "</td><td>" & InTime & "<br></tr>"
End If
Else
NameLabel.Text &= "<tr><td><Font color='red'>" &
FirstName & " " & LastName & "</font></td><td><Font color='red'>" &
InTime & "</font><br></tr>"
End If
End While
NameReader.Close()
End While
IDReader.Close()
conn.Close()
conn2.Close()
NameLabel.Text &= "</table>"
with con and conn2 being my two connections for the different
databases.
As you can see it loops through all the people who are out, and for
each of these it does another sql query to find out their name, then
adds a row to the table in my namelable.text (I am using a user
control)
I'm just beginning with ASP.net, I know a bit of ASP and PHP, so the
most familiar way for me to interact with databases is as above, I
know there is all these different ways to but I dont know how to just
yet. If it is the only way to do things I will go and learn one of
those ways.
has all the staff members who are currently signed out of hte office.
That has their staff ID, and the time they are out till etc, then
another database has the staff table in it, which matches the ID to
names.
I am trying to do a basic query for all people who are signed out at
the moment for a front page of our intranet. I've got that all sorted,
but I can only sort the results by StaffID (which is uselss) or Time
due back in, not by name like I want.
Here is my code so far:
conn.Open()
conn2.Open()
Dim SignedOutCommand As New SqlCommand("Select StaffID,
InTime, HaveMob FROM StaffOut ORDER BY InTime", conn)
Dim FirstName
Dim LastName
Dim x = 0
Dim loopcount = 0
Dim IDReader As SqlDataReader =
SignedOutCommand.ExecuteReader()
While IDReader.Read()
Dim InTime
Dim HaveMob
Dim InDate
Dim ComingIn
StaffID = IDReader.Item("StaffID")
ComingIn = IDReader.Item("InTime")
InDate = Left(ComingIn, 10)
InTime = Right(ComingIn, 11)
Dim NameCommand As New SqlCommand("Select FirstName,
LastName FROM Staff WHERE StaffID = '" & StaffID & "'", conn2)
Dim NameReader As SqlDataReader =
NameCommand.ExecuteReader()
While NameReader.Read()
FirstName = NameReader.Item("FirstName")
FirstName = ToTitleCase(FirstName)
LastName = NameReader.Item("LastName")
LastName = ToTitleCase(LastName)
If DateValue(InDate) > DateValue(Now()) Then
NameLabel.Text &= "<tr><td>" & FirstName & " " &
LastName & "</td><td><Font color='blue'>" & InDate & "</font><br></
tr>"
ElseIf DateValue(InDate) = DateValue(Now) Then
If TimeValue(Now) > TimeValue(InTime) Then
NameLabel.Text &= "<tr><td>" & FirstName & " "
& LastName & "</td><td><Font color='red'>" & InTime & "</font><br></
tr>"
Else
NameLabel.Text &= "<tr><td>" & FirstName & " "
& LastName & "</td><td>" & InTime & "<br></tr>"
End If
Else
NameLabel.Text &= "<tr><td><Font color='red'>" &
FirstName & " " & LastName & "</font></td><td><Font color='red'>" &
InTime & "</font><br></tr>"
End If
End While
NameReader.Close()
End While
IDReader.Close()
conn.Close()
conn2.Close()
NameLabel.Text &= "</table>"
with con and conn2 being my two connections for the different
databases.
As you can see it loops through all the people who are out, and for
each of these it does another sql query to find out their name, then
adds a row to the table in my namelable.text (I am using a user
control)
I'm just beginning with ASP.net, I know a bit of ASP and PHP, so the
most familiar way for me to interact with databases is as above, I
know there is all these different ways to but I dont know how to just
yet. If it is the only way to do things I will go and learn one of
those ways.