| Author |
 Topic  |
|
mykel_suthertun
Junior Member
USA
227 Posts |
 Posted - 01 March 2005 : 15:41:56
|
I'm using a variation of the Site News script from Web Wiz Guide. I got it to work great to display my shows (example here), but on my homepage, I would like it to display only the first upcoming show.
As I'm writing this, the correct show to display would be the March 11 show. It's currently displaying April 08. Here is the SQL statement I'm using to display the show. Can someone fix this for me? Thanks!
'Initalise the strShowsSQL variable with an SQL statement to query the database
strShowsSQL = "SELECT TOP 1 tblShows.* FROM tblShows WHERE CDate(Left(tblShows.Shows_Date,8)) >= #" & CDate(Left(NOW()+1,8)) & "# ORDER BY Shows_Date DESC;" |
Mike Southerton
Webmaster | Don'tSayDie.com | DriveThe.com |
|
|
Doug G
Support Moderator
USA
6493 Posts |
Posted - 02 March 2005 : 16:26:01
|
What is the datatype of show_date?
Are CDate and Left valid sql functions in your database?
|
======
Doug G
======
Computer history and help at www.dougscode.com |
 |
|
|
mykel_suthertun
Junior Member
USA
227 Posts |
Posted - 03 March 2005 : 13:21:16
|
Sorry, I only know enough about ASP and databases to poke around a change a few lines.
Would it help if I sent the database to you? |
Mike Southerton
Webmaster | Don'tSayDie.com | DriveThe.com |
|
|
|
Classicmotorcycling
Development Team Leader
Australia
2085 Posts |
Posted - 03 March 2005 : 16:41:19
|
Looking at the out put you are getting, it appears to want to go to the next month anyway with the query string. What if you take the +1 of the query string like so?:
Try this:'Initalise the strShowsSQL variable with an SQL statement to query the database
strShowsSQL = "SELECT TOP 1 tblShows.* FROM tblShows WHERE CDate(Left(tblShows.Shows_Date,8)) >= #" & CDate(Left(NOW()+0,8)) & "# ORDER BY Shows_Date DESC;" Notice that I have changed the +1 to a +0 so it doesn't go to the next month?
|
Cheers,
David Greening |
|
|
|
mykel_suthertun
Junior Member
USA
227 Posts |
Posted - 04 March 2005 : 00:01:46
|
| Hmmm...nope. That didn't do anything. No errors, but I still get the same date (at the moment, it's April 08). Any other ideas? |
Mike Southerton
Webmaster | Don'tSayDie.com | DriveThe.com |
|
|
|
Classicmotorcycling
Development Team Leader
Australia
2085 Posts |
Posted - 04 March 2005 : 00:48:30
|
Can you send me the bit of code on your front page to call up the:
April 08, 2005
with TBA
ages 21+ only, $3-5 cover
show stars @ 10:00pm
Boomerang's (a.k.a. The Up and Under)
711 W Michigan Avenue - Kalamazoo, MI.
I would like to look around it.
|
Cheers,
David Greening |
|
|
|
mykel_suthertun
Junior Member
USA
227 Posts |
Posted - 04 March 2005 : 01:09:09
|
I've included the file "shows_display_short.asp" on my front page to display the show. Here it is, in text format.
Thanks, |
Mike Southerton
Webmaster | Don'tSayDie.com | DriveThe.com |
|
|
|
Classicmotorcycling
Development Team Leader
Australia
2085 Posts |
Posted - 04 March 2005 : 02:10:41
|
My first thought of the code is that you have two (2) calls to the tables to grab the information.
'Initialise the SQL variable with an SQL statement to get the configuration details from the database
strShowsSQL = "SELECT tblConfiguration.* From tblConfiguration;"
and
'Initalise the strShowsSQL variable with an SQL statement to query the database
strShowsSQL = "SELECT TOP 1 tblShows.* FROM tblShows WHERE CDate(Left(tblShows.Shows_Date,8)) >= #" & CDate(Left(NOW() +0,8)) & "# ORDER BY Shows_Date DESC;"
The first one shows no sorting at all, so maybe that is grabing the data and showing the last, or first bit of data entered in to the DB. Just a thought, but I would also suspect that as the issue as it both queries are using the strShowSQL to grab a query from the DB and only one is required by the looks of the code.
Remove one of the lines an see what happens. |
Cheers,
David Greening |
Edited by - Classicmotorcycling on 04 March 2005 02:16:34 |
|
|
|
mykel_suthertun
Junior Member
USA
227 Posts |
Posted - 13 March 2005 : 23:37:33
|
Removing one or the other gives an error either way. I assume the first is required because it gets the configuration options from the database, and the latter gets the actual show from the list of shows.
Would I need to use a different variable for the shows data? I'm not sure I would, as roughly the same script works just fine on the shows page.
Also, is it possible that there's a problem with the database itself? I'm no expert, but it seems like if the data's good, then the query should work. |
Mike Southerton
Webmaster | Don'tSayDie.com | DriveThe.com |
|
|
|
mykel_suthertun
Junior Member
USA
227 Posts |
Posted - 14 March 2005 : 01:37:11
|
I cleaned up the code as much as I could (it's about half as big now), and I'm no longer calling the configuration data (since I don't need it for this page). It's still displaying the incorrect date though.
PLEASE look at my code once again to see if you can figure it out. I appreciate the help you've given me so far!
Here it is again. |
Mike Southerton
Webmaster | Don'tSayDie.com | DriveThe.com |
Edited by - mykel_suthertun on 14 March 2005 01:39:24 |
|
|
|
Classicmotorcycling
Development Team Leader
Australia
2085 Posts |
Posted - 14 March 2005 : 02:03:02
|
Sent you an e-mail Mike..
|
Cheers,
David Greening |
|
|
|
Classicmotorcycling
Development Team Leader
Australia
2085 Posts |
Posted - 14 March 2005 : 04:10:35
|
Mike,
Sent you back the file twice. Use the second one, forgot the sort in the first one, but it works for me.
Cheers,
David |
Cheers,
David Greening |
|
|
|
mykel_suthertun
Junior Member
USA
227 Posts |
Posted - 14 March 2005 : 14:50:23
|
| Thanks! But hmmmm...did you send it to the same e-mail address I replied to you from? E-mail servers might be a little slow today... |
Mike Southerton
Webmaster | Don'tSayDie.com | DriveThe.com |
|
|
|
Classicmotorcycling
Development Team Leader
Australia
2085 Posts |
Posted - 14 March 2005 : 15:55:32
|
OK, I just sent it to the e-mail address in the DB. See if that comes through. I have put a little test page at http://www.classicmotorcycling.com.au/cmc/ to show you it works.
|
Cheers,
David Greening |
|
|
|
mykel_suthertun
Junior Member
USA
227 Posts |
Posted - 14 March 2005 : 18:09:31
|
Thank you SOOOO much! That hasn't worked right for probably 6 months now.
Thanks again!!! |
Mike Southerton
Webmaster | Don'tSayDie.com | DriveThe.com |
|
|
|
Classicmotorcycling
Development Team Leader
Australia
2085 Posts |
Posted - 14 March 2005 : 18:49:50
|
Jut in case people would like to know what the solution is, well it is the following call from the DB:
strShowsSQL = "SELECT TOP 1 tblShows.* FROM tblShows WHERE Shows_Date > Now() ORDER BY Shows_Date ASC"
Basically I just told it to grab the first record it finds greater than the current viewing date after sorting the table from first date to the last date.
Glad to be of help. Anytime.
|
Cheers,
David Greening |
|
|
|
Topic |
|
Topic Locked
