Writing query statement to Access Database from ASP page

**Ray at** · Jul 19 '05, 10:16 AM

Re: Writing query statement to Access Database from ASP page

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

Ray at work

"George Stout" <gstout@uscs.ed u> wrote in message
news:c2e89310.0 401201012.39ae1 7f3@posting.goo gle.com...[color=blue]
> 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.[/color]

**Brynn** · Jul 19 '05, 10:16 AM

Re: Writing query statement to Access Database from ASP page

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(eventsAr ray,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 ...



-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.Redire ct("thisPageRew riteUtility.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

https://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, gstout@uscs.edu (George Stout) wrote:
[color=blue]
>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.[/color]

Brynn

https://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** · Jul 19 '05, 10:17 AM

Re: Writing query statement to Access Database from ASP page

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.c om

On Tue, 20 Jan 2004 19:00:30 GMT, z@z.com (Brynn) wrote:
[color=blue]
>
>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(eventsAr ray,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 ...
>
>
>
>-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.Redire ct("thisPageRew riteUtility.asp ")
>End If
>'// Else ... no action and it will simply display the html below this
>%>
><tr><td>Colleg e</td><td>Event</td></tr>
><tr><td>Colleg e</td><td>Event</td></tr>
><tr><td>Colleg e</td><td>Event</td></tr>
><tr><td>Colleg e</td><td>Event</td></tr>
><tr><td>Colleg e</td><td>Event</td></tr>
><tr><td>Colleg e</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, gstout@uscs.edu (George Stout) wrote:
>[color=green]
>>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.[/color]
>
>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![/color]

Brynn

https://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** · Jul 19 '05, 10:19 AM

Re: Writing query statement to Access Database from ASP page

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

z@z.com (Brynn) wrote in message news:<400d73c8. 2810831@news.co mcast.giganews. com>...[color=blue]
> 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(eventsAr ray,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 ...
>
> 
>
> -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.Redire ct("thisPageRew riteUtility.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, gstout@uscs.edu (George Stout) wrote:
>[color=green]
> >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.[/color]
>
> 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![/color]

Writing query statement to Access Database from ASP page

Writing query statement to Access Database from ASP page

Comment

Comment

Comment

Comment