Writing query statement to Access Database from ASP page

G

George Stout

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

Brynn

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










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!
 
B

Brynn

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




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












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

George Stout

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
 

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

Forum statistics

Threads
473,731
Messages
2,569,432
Members
44,832
Latest member
GlennSmall

Latest Threads

Top