Using Readers for SQL queries in a loop, want to sort results in alphabetical VB.net

  • Thread starter Thread starter itfetish
  • Start date Start date
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.
 
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.

Are these really two separate databases, as opposed to two separate
tables...?

If so, are they on the same server at least...?
 
Yes they are both on a SQL2000 server, One database is called Infobase
and was made years ago, and one is called WhereAreYou

Stupid that they are separated I know.
 
Yes they are both on a SQL2000 server, One database is called Infobase
and was made years ago, and one is called WhereAreYou
OK.

Stupid that they are separated I know.

Indeed, but are both databases on the same server...?
 
yep, both on the same machine, is there a way to query both databases
at the same time or something?

Indeed there is! That's why I was asking...

From Database1:

SELECT * FROM Database2..MyTable WHERE...
 
yep, both on the same machine, is there a way to query both databases
at the same time or something?

SELECT ... FROM [database_name1].[dbo].[table1] T1, [database_name2].
[dbo].[table2] T2 WHERE...
 
Back
Top