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

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

Mark Rae

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...?
 
I

itfetish

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

Mark Rae

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...?
 
I

itfetish

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?
 
M

Mark Rae

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

Guest

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

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Members online

No members online now.

Forum statistics

Threads
473,743
Messages
2,569,478
Members
44,899
Latest member
RodneyMcAu

Latest Threads

Top