Writing query statement to Access Database from ASP page

Discussion in 'ASP General' started by George Stout, Jan 20, 2004.

  1. George Stout

    George Stout Guest

    First off I do not know alot about writing queries to an Access
    Database from an ASP page. This is why I need help.

    I have an Events database for 6 colleges in our metro area. On the
    homepage I have to display the next event for each college. That would
    give me 6 events listed on the page. I have been trying to figure out
    how to write a query statement in my ASP page to select just the most
    current event from each college. I have not had any success
    whatsoever. Any guideance or help would be appreciated.

    Thanks.
    George Stout, Jan 20, 2004
    #1
    1. Advertising

  2. George Stout

    Ray at Guest

    What's the layout of the database? (See here http://www.aspfaq.com/5006)

    Ray at work

    "George Stout" <> wrote in message
    news:...
    > First off I do not know alot about writing queries to an Access
    > Database from an ASP page. This is why I need help.
    >
    > I have an Events database for 6 colleges in our metro area. On the
    > homepage I have to display the next event for each college. That would
    > give me 6 events listed on the page. I have been trying to figure out
    > how to write a query statement in my ASP page to select just the most
    > current event from each college. I have not had any success
    > whatsoever. Any guideance or help would be appreciated.
    >
    > Thanks.
    Ray at, Jan 20, 2004
    #2
    1. Advertising

  3. George Stout

    Brynn Guest

    Do you need to know the next event from todays date ... and do you
    have the date for each event in your database ... and do you want the
    next event to be todays if there is one, or tomorrows.

    you may have to ...

    select eventDate, eventCollege, eventName, eventDesc from Events
    where eventDate >= Date()
    order by eventCollege desc, eventDate desc

    I would use getrows, and throw this into an array ... or use my
    dbconn.asp script on my site. The array would have the equivaent of 4
    columns.

    <%
    Dim theCollege: theCollege = ""
    For row = 0 to ubound(eventsArray,2)
    If Not theCollege = eventsArray(1,n) Then
    theCollege = eventsArray(1,n)
    With Response
    .Write "your code display for this record in the array"
    End With
    End If
    %>

    -See, the above code has an array.
    -theCollege starts as nothin
    -the array is ordered by college then by date
    -this loop will go through the entire array and only write the first
    record for the college ... which will be the one you need ... then it
    won't write another until it is the next college.

    See?

    With more info on your DB structure, I may be able to narrow it down a
    bit.

    I THINK this will be a faster way than selecting top1 for each college
    .... that would be 6 hits to the database each time

    PERSONALLY

    I would create a text file ... first line with the date, then the next
    6 lines with the html for the display of the "next events" ... I would
    have a subroutine that ...

    -checks to see if the date on the first line = today

    -if not, goto code that rewrites the text file from the database to
    update it

    -else, show the text files html

    THIS WOULD SAVE BIG TIME ON YOUR DATABASE

    I am a MAJOR FREAK about not using the database if possible.

    I would even suggest making that text file an asp file that is
    included ...

    <!-- #include virtual="/scripts/latestEvents.asp" -->

    -if no date
    -redirect to rewrite of asp inlclude that does the date check

    -else
    -nothing ... just show the below html

    the include could be like...

    <%
    Dim theDate
    theDate = "1/19/04"
    If Not theDate = Date() Then
    '// Rewrite this page .. must rewrite the below HTML and the value
    for theDate variable to todays
    Response.Redirect("thisPageRewriteUtility.asp")
    End If
    '// Else ... no action and it will simply display the html below this
    %>
    <tr><td>College</td><td>Event</td></tr>
    <tr><td>College</td><td>Event</td></tr>
    <tr><td>College</td><td>Event</td></tr>
    <tr><td>College</td><td>Event</td></tr>
    <tr><td>College</td><td>Event</td></tr>
    <tr><td>College</td><td>Event</td></tr>




    +++++++++++++++++++++++++++++==
    This would cause the database to only be hit the first time by the
    first user each day ... then would be static from here ... I have used
    this technique several times, and it would definately be the fastest
    way to have a dynamic, static page =) AND YOU NEED SPEED ...
    especially since it is your home page we are talking about.

    Brynn
    www.coolpier.com

    P.S. if you need further assistance, let me know ... if this sounds
    too complicated, let me know and I may be able to help further or
    write a quick couple of scripts. Contact me at the following link if
    need be ...

    http://www.coolpier.com/cp/developer/contact.asp










    On 20 Jan 2004 10:12:03 -0800, (George Stout) wrote:

    >First off I do not know alot about writing queries to an Access
    >Database from an ASP page. This is why I need help.
    >
    >I have an Events database for 6 colleges in our metro area. On the
    >homepage I have to display the next event for each college. That would
    >give me 6 events listed on the page. I have been trying to figure out
    >how to write a query statement in my ASP page to select just the most
    >current event from each college. I have not had any success
    >whatsoever. Any guideance or help would be appreciated.
    >
    >Thanks.


    Brynn
    www.coolpier.com

    I participate in the group to help give examples of code.
    I do not guarantee the effects of any code posted.
    Test all code before use!
    Brynn, Jan 20, 2004
    #3
  4. George Stout

    Brynn Guest

    I am so glad I have a contact me form, instead of my email address up
    .... I got some VERY graphic annoying messages after posting this ...
    not from anyone in this post ... just someone that read it ... LOL.

    I will have my contactMe tool open for anyone to use ... the new one
    has Js validation, a limiter on messages per day, and lets you control
    the color scheme ...

    test.coolpier.com




    On Tue, 20 Jan 2004 19:00:30 GMT, (Brynn) wrote:

    >
    >Do you need to know the next event from todays date ... and do you
    >have the date for each event in your database ... and do you want the
    >next event to be todays if there is one, or tomorrows.
    >
    >you may have to ...
    >
    >select eventDate, eventCollege, eventName, eventDesc from Events
    >where eventDate >= Date()
    >order by eventCollege desc, eventDate desc
    >
    >I would use getrows, and throw this into an array ... or use my
    >dbconn.asp script on my site. The array would have the equivaent of 4
    >columns.
    >
    ><%
    >Dim theCollege: theCollege = ""
    >For row = 0 to ubound(eventsArray,2)
    >If Not theCollege = eventsArray(1,n) Then
    >theCollege = eventsArray(1,n)
    >With Response
    > .Write "your code display for this record in the array"
    >End With
    >End If
    >%>
    >
    >-See, the above code has an array.
    >-theCollege starts as nothin
    >-the array is ordered by college then by date
    >-this loop will go through the entire array and only write the first
    >record for the college ... which will be the one you need ... then it
    >won't write another until it is the next college.
    >
    >See?
    >
    >With more info on your DB structure, I may be able to narrow it down a
    >bit.
    >
    >I THINK this will be a faster way than selecting top1 for each college
    >... that would be 6 hits to the database each time
    >
    >PERSONALLY
    >
    >I would create a text file ... first line with the date, then the next
    >6 lines with the html for the display of the "next events" ... I would
    >have a subroutine that ...
    >
    >-checks to see if the date on the first line = today
    >
    >-if not, goto code that rewrites the text file from the database to
    >update it
    >
    >-else, show the text files html
    >
    >THIS WOULD SAVE BIG TIME ON YOUR DATABASE
    >
    >I am a MAJOR FREAK about not using the database if possible.
    >
    >I would even suggest making that text file an asp file that is
    >included ...
    >
    ><!-- #include virtual="/scripts/latestEvents.asp" -->
    >
    >-if no date
    > -redirect to rewrite of asp inlclude that does the date check
    >
    >-else
    > -nothing ... just show the below html
    >
    >the include could be like...
    >
    ><%
    >Dim theDate
    > theDate = "1/19/04"
    >If Not theDate = Date() Then
    > '// Rewrite this page .. must rewrite the below HTML and the value
    >for theDate variable to todays
    > Response.Redirect("thisPageRewriteUtility.asp")
    >End If
    >'// Else ... no action and it will simply display the html below this
    >%>
    ><tr><td>College</td><td>Event</td></tr>
    ><tr><td>College</td><td>Event</td></tr>
    ><tr><td>College</td><td>Event</td></tr>
    ><tr><td>College</td><td>Event</td></tr>
    ><tr><td>College</td><td>Event</td></tr>
    ><tr><td>College</td><td>Event</td></tr>
    >
    >
    >
    >
    >+++++++++++++++++++++++++++++==
    >This would cause the database to only be hit the first time by the
    >first user each day ... then would be static from here ... I have used
    >this technique several times, and it would definately be the fastest
    >way to have a dynamic, static page =) AND YOU NEED SPEED ...
    >especially since it is your home page we are talking about.
    >
    >Brynn
    >www.coolpier.com
    >
    >P.S. if you need further assistance, let me know ... if this sounds
    >too complicated, let me know and I may be able to help further or
    >write a quick couple of scripts. Contact me at the following link if
    >need be ...
    >
    >http://www.coolpier.com/cp/developer/contact.asp
    >
    >
    >
    >
    >
    >
    >
    >
    >
    >
    >On 20 Jan 2004 10:12:03 -0800, (George Stout) wrote:
    >
    >>First off I do not know alot about writing queries to an Access
    >>Database from an ASP page. This is why I need help.
    >>
    >>I have an Events database for 6 colleges in our metro area. On the
    >>homepage I have to display the next event for each college. That would
    >>give me 6 events listed on the page. I have been trying to figure out
    >>how to write a query statement in my ASP page to select just the most
    >>current event from each college. I have not had any success
    >>whatsoever. Any guideance or help would be appreciated.
    >>
    >>Thanks.

    >
    >Brynn
    >www.coolpier.com
    >
    >I participate in the group to help give examples of code.
    >I do not guarantee the effects of any code posted.
    >Test all code before use!


    Brynn
    www.coolpier.com

    I participate in the group to help give examples of code.
    I do not guarantee the effects of any code posted.
    Test all code before use!
    Brynn, Jan 21, 2004
    #4
  5. George Stout

    George Stout Guest

    I will give this a shot. Reading through it it was a bit confusing to
    me. I really appreciate the help. I will let you know in a couple of
    days.

    Thanks.

    George

    (Brynn) wrote in message news:<>...
    > Do you need to know the next event from todays date ... and do you
    > have the date for each event in your database ... and do you want the
    > next event to be todays if there is one, or tomorrows.
    >
    > you may have to ...
    >
    > select eventDate, eventCollege, eventName, eventDesc from Events
    > where eventDate >= Date()
    > order by eventCollege desc, eventDate desc
    >
    > I would use getrows, and throw this into an array ... or use my
    > dbconn.asp script on my site. The array would have the equivaent of 4
    > columns.
    >
    > <%
    > Dim theCollege: theCollege = ""
    > For row = 0 to ubound(eventsArray,2)
    > If Not theCollege = eventsArray(1,n) Then
    > theCollege = eventsArray(1,n)
    > With Response
    > .Write "your code display for this record in the array"
    > End With
    > End If
    > %>
    >
    > -See, the above code has an array.
    > -theCollege starts as nothin
    > -the array is ordered by college then by date
    > -this loop will go through the entire array and only write the first
    > record for the college ... which will be the one you need ... then it
    > won't write another until it is the next college.
    >
    > See?
    >
    > With more info on your DB structure, I may be able to narrow it down a
    > bit.
    >
    > I THINK this will be a faster way than selecting top1 for each college
    > ... that would be 6 hits to the database each time
    >
    > PERSONALLY
    >
    > I would create a text file ... first line with the date, then the next
    > 6 lines with the html for the display of the "next events" ... I would
    > have a subroutine that ...
    >
    > -checks to see if the date on the first line = today
    >
    > -if not, goto code that rewrites the text file from the database to
    > update it
    >
    > -else, show the text files html
    >
    > THIS WOULD SAVE BIG TIME ON YOUR DATABASE
    >
    > I am a MAJOR FREAK about not using the database if possible.
    >
    > I would even suggest making that text file an asp file that is
    > included ...
    >
    > <!-- #include virtual="/scripts/latestEvents.asp" -->
    >
    > -if no date
    > -redirect to rewrite of asp inlclude that does the date check
    >
    > -else
    > -nothing ... just show the below html
    >
    > the include could be like...
    >
    > <%
    > Dim theDate
    > theDate = "1/19/04"
    > If Not theDate = Date() Then
    > '// Rewrite this page .. must rewrite the below HTML and the value
    > for theDate variable to todays
    > Response.Redirect("thisPageRewriteUtility.asp")
    > End If
    > '// Else ... no action and it will simply display the html below this
    > %>
    > <tr><td>College</td><td>Event</td></tr>
    > <tr><td>College</td><td>Event</td></tr>
    > <tr><td>College</td><td>Event</td></tr>
    > <tr><td>College</td><td>Event</td></tr>
    > <tr><td>College</td><td>Event</td></tr>
    > <tr><td>College</td><td>Event</td></tr>
    >
    >
    >
    >
    > +++++++++++++++++++++++++++++==
    > This would cause the database to only be hit the first time by the
    > first user each day ... then would be static from here ... I have used
    > this technique several times, and it would definately be the fastest
    > way to have a dynamic, static page =) AND YOU NEED SPEED ...
    > especially since it is your home page we are talking about.
    >
    > Brynn
    > www.coolpier.com
    >
    > P.S. if you need further assistance, let me know ... if this sounds
    > too complicated, let me know and I may be able to help further or
    > write a quick couple of scripts. Contact me at the following link if
    > need be ...
    >
    > http://www.coolpier.com/cp/developer/contact.asp
    >
    >
    >
    >
    >
    >
    >
    >
    >
    >
    > On 20 Jan 2004 10:12:03 -0800, (George Stout) wrote:
    >
    > >First off I do not know alot about writing queries to an Access
    > >Database from an ASP page. This is why I need help.
    > >
    > >I have an Events database for 6 colleges in our metro area. On the
    > >homepage I have to display the next event for each college. That would
    > >give me 6 events listed on the page. I have been trying to figure out
    > >how to write a query statement in my ASP page to select just the most
    > >current event from each college. I have not had any success
    > >whatsoever. Any guideance or help would be appreciated.
    > >
    > >Thanks.

    >
    > Brynn
    > www.coolpier.com
    >
    > I participate in the group to help give examples of code.
    > I do not guarantee the effects of any code posted.
    > Test all code before use!
    George Stout, Jan 21, 2004
    #5
    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?U1M=?=
    Replies:
    3
    Views:
    3,827
    Juan T. Llibre
    May 15, 2005
  2. Learner
    Replies:
    1
    Views:
    984
    Marina Levit [MVP]
    Jan 30, 2006
  3. Chuck
    Replies:
    0
    Views:
    253
    Chuck
    Jul 5, 2003
  4. Noozer
    Replies:
    1
    Views:
    141
    Bob Barrows [MVP]
    Jul 21, 2005
  5. ITHELP

    like statement in SQL query in Access

    ITHELP, Jul 21, 2005, in forum: ASP General
    Replies:
    1
    Views:
    141
    Aaron Bertrand [SQL Server MVP]
    Jul 21, 2005
Loading...

Share This Page