By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,362 Members | 3,489 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,362 IT Pros & Developers. It's quick & easy.

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

P: n/a
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.

Apr 23 '07 #1
Share this Question
Share on Google+
7 Replies


P: n/a
<it******@gmail.comwrote in message
news:11**********************@y80g2000hsf.googlegr oups.com...
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...?
Apr 23 '07 #2

P: n/a
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.

On Apr 23, 4:28 pm, "Mark Rae" <m...@markNOSPAMrae.netwrote:
<itfet...@gmail.comwrote in message

news:11**********************@y80g2000hsf.googlegr oups.com...
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...?

Apr 23 '07 #3

P: n/a
<it******@gmail.comwrote in message
news:11**********************@n59g2000hsh.googlegr oups.com...
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...?
Apr 23 '07 #4

P: n/a
On Apr 23, 4:45 pm, "Mark Rae" <m...@markNOSPAMrae.netwrote:
<itfet...@gmail.comwrote in message

news:11**********************@n59g2000hsh.googlegr oups.com...
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?

Apr 23 '07 #5

P: n/a
On Apr 23, 8:53 am, itfet...@gmail.com wrote:
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...

Apr 23 '07 #6

P: n/a
<it******@gmail.comwrote in message
news:11**********************@y5g2000hsa.googlegro ups.com...
>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...
Apr 23 '07 #7

P: n/a
legends! I'll give that a go.

Thanks heaps for all your help

Apr 23 '07 #8

This discussion thread is closed

Replies have been disabled for this discussion.