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

Discussion in 'ASP .Net' started by itfetish@gmail.com, Apr 23, 2007.

  1. Guest

    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, 2007
    #1
    1. Advertising

  2. Mark Rae Guest

    <> wrote in message
    news:...

    > 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...?
     
    Mark Rae, Apr 23, 2007
    #2
    1. Advertising

  3. Guest

    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" <> wrote:
    > <> wrote in message
    >
    > news:...
    >
    > > 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, 2007
    #3
  4. Mark Rae Guest

    <> wrote in message
    news:...

    > 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...?
     
    Mark Rae, Apr 23, 2007
    #4
  5. Guest

    On Apr 23, 4:45 pm, "Mark Rae" <> wrote:
    > <> wrote in message
    >
    > news:...
    >
    > > 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, 2007
    #5
  6. Mark Rae Guest

    <> wrote in message
    news:...

    >> 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...
     
    Mark Rae, Apr 23, 2007
    #6
  7. On Apr 23, 8:53 am, 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...
     
    Alexey Smirnov, Apr 23, 2007
    #7
  8. Guest

    legends! I'll give that a go.

    Thanks heaps for all your help
     
    , Apr 23, 2007
    #8
    1. Advertising

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

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. =?Utf-8?B?YmVub2l0?=

    ListItemCollection Sort Alphabetical

    =?Utf-8?B?YmVub2l0?=, Nov 3, 2005, in forum: ASP .Net
    Replies:
    4
    Views:
    11,336
    =?Utf-8?B?U3JlZWppdGggUmFt?=
    Nov 3, 2005
  2. Ian Roddis

    xslt queries in xml to SQL queries

    Ian Roddis, Feb 26, 2006, in forum: Python
    Replies:
    3
    Views:
    1,513
    Crutcher
    Feb 26, 2006
  3. ecoolone
    Replies:
    0
    Views:
    765
    ecoolone
    Jan 3, 2008
  4. Pander Musubi

    Custom alphabetical sort

    Pander Musubi, Dec 24, 2012, in forum: Python
    Replies:
    8
    Views:
    193
  5. Roy Smith

    Re: Custom alphabetical sort

    Roy Smith, Dec 24, 2012, in forum: Python
    Replies:
    10
    Views:
    290
    Joshua Landau
    Dec 27, 2012
Loading...

Share This Page