returning results in rows

Discussion in 'ASP General' started by Ken, Sep 17, 2003.

  1. Ken

    Ken Guest

    I have a db with the fields Day, Month, Year and Message. How can I select
    all of the messages for the current week starting with sunday and return the
    results as a list?

    Thanks
    Ken
    Ken, Sep 17, 2003
    #1
    1. Advertising

  2. Ken

    Ray at Guest

    By using a SELECT query. What part are you stuck on? How far have you
    gotten so far?

    Ray at work

    "Ken" <> wrote in message
    news:...
    > I have a db with the fields Day, Month, Year and Message. How can I select
    > all of the messages for the current week starting with sunday and return

    the
    > results as a list?
    >
    > Thanks
    > Ken
    >
    >
    Ray at, Sep 17, 2003
    #2
    1. Advertising

  3. What kind of database are you using???

    Why did you separate day, month and year? It's fine if your date ranges
    never cross month or year boundaries, but you're going to have a hard time
    querying a range like 2003-12-26 -> 2004-01-03.

    Bad column name choices too, by the way. Three of your four columns are
    reserved words.

    Anyway. You can't reliably get a list from a set of columns, as this
    violates set theory. (Databases are used to store sets of data. If you are
    not storing sets, but rather lists, just use a flat file.) Granted, there
    are ways to do it, but this is better handled in the application.

    First, you have to figure out when Sunday was, then return a set of ROWS
    that fall in that category.

    sunday = dateadd("d", 1-datepart("w", date()), date())
    sunday = year(sunday) & "-" & month(sunday) & "-" & day(sunday)
    sql = "SELECT [day], [month], [year], message FROM sometable WHERE CDate("'"
    & [year] & "-" & [month] & "-" [day] & "'") >= CDate('" & sunday & "')"
    set rs = conn.execute(sql)

    Then, use ASP to translate the rows into a list,

    thislist = ""
    prevday = -1
    do while not rs.eof
    if rs(0) <> prevday then
    response.write thislist & "<p>"
    prevday = rs(0)
    response.write rs(2) & "-" & rs(1) & "-" & rs(0) & ":"
    thislist = rs(3)
    else
    thislist = thislist & ", " & rs(3)
    end if
    rs.movenext
    loop
    response.write thislist & "<p>"




    "Ken" <> wrote in message
    news:...
    > I have a db with the fields Day, Month, Year and Message. How can I select
    > all of the messages for the current week starting with sunday and return

    the
    > results as a list?
    >
    > Thanks
    > Ken
    >
    >
    Aaron Bertrand - MVP, Sep 17, 2003
    #3
    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. Subba Rao via DotNetMonster.com

    script for moving rows up and down and traverse thru rows of HTML table

    Subba Rao via DotNetMonster.com, Mar 19, 2005, in forum: ASP .Net
    Replies:
    0
    Views:
    8,193
    Subba Rao via DotNetMonster.com
    Mar 19, 2005
  2. helpful sql
    Replies:
    0
    Views:
    791
    helpful sql
    May 19, 2005
  3. Arjen Hoekstra
    Replies:
    0
    Views:
    538
    Arjen Hoekstra
    Aug 2, 2005
  4. Jason James

    Rows the rows of a grid view?

    Jason James, Aug 10, 2006, in forum: ASP .Net
    Replies:
    0
    Views:
    383
    Jason James
    Aug 10, 2006
  5. VijayRama
    Replies:
    2
    Views:
    2,291
    Gregory A. Beamer
    Oct 12, 2009
Loading...

Share This Page