Wrox Programmer Forums

Need to download code?

View our list of code downloads.

| FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read
Classic ASP Basics For beginner programmers starting with "classic" ASP 3, pre-".NET." NOT for ASP.NET 1.0, 1.1, or 2.0
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Classic ASP Basics section of the Wrox Programmer to Programmer discussions. This is a community of tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developers’ questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Reply
 
Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old May 27th, 2008, 10:43 AM
Authorized User
Points: 483, Level: 7
Points: 483, Level: 7 Points: 483, Level: 7 Points: 483, Level: 7
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Apr 2007
Location: Edgewood, NY, USA.
Posts: 92
Thanks: 0
Thanked 0 Times in 0 Posts
Default Search a date range help

Hi. I am still new here and learning well, but I just can not locate info or get started on how to set up my query to search a single date range. I have a field called "Date_Paid" hitting a MySQL database. My table is called "unsolved".

Can anyone get me started here? I assume I will give a text field to enter the date or can I use a pulldown field to select a month and year?

Thank you.

Reply With Quote
  #2 (permalink)  
Old May 27th, 2008, 03:08 PM
Friend of Wrox
 
Join Date: May 2006
Location: San Diego, CA, USA.
Posts: 643
Thanks: 0
Thanked 0 Times in 0 Posts
Default

You can use any UI elements that work for you. For starters, using drop-downs to limit user input sounds like a good idea. Later on you can use a calendar or any UI that works for you users.

You will want to validate the input to verify that it is as you expect it to be. Using the drop-downs will make it easier to keep the user from making mistakes, but you still want to verify that the values you receive are within the ranges that you require. For example, you don't want to allow processing for February 31st, right?

So, once you get the input from the user, you'll need to construct some SQL that will find dates within the range specified. You'll want to make sure that your query will actually find dates that are as you suspect. When you get that far, let me know if you need help.

Woody Z
http://www.learntoprogramnow.com
How to use a forum to help solve problems
My blog... please visit
Reply With Quote
  #3 (permalink)  
Old May 27th, 2008, 03:11 PM
Authorized User
Points: 483, Level: 7
Points: 483, Level: 7 Points: 483, Level: 7 Points: 483, Level: 7
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Apr 2007
Location: Edgewood, NY, USA.
Posts: 92
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Hi woody.

I am that far now. I have the form. I understand the basics of asp. I am having trouble with the query.

Reply With Quote
  #4 (permalink)  
Old May 27th, 2008, 03:22 PM
Authorized User
Points: 483, Level: 7
Points: 483, Level: 7 Points: 483, Level: 7 Points: 483, Level: 7
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Apr 2007
Location: Edgewood, NY, USA.
Posts: 92
Thanks: 0
Thanked 0 Times in 0 Posts
Default

I tried this:
SQL ="SELECT *, DATE_FORMAT(Date_Paid, '%c-%e-%Y') AS Date_Paid FROM unsolved WHERE Date_Paid Like '%"&monthdate&"%' and Date_Paid Like '"&Yeardate&"%' "

it does return results, BUT... My querystring shows the results based on the day! I need the results to be based on month and year. when I run a querystring, it calls up all records that match by the DAY and YEAR! why is this??? I need the records to show by MONTH and year!

I only have one date field, and I simply want to be able to call up just a particular month.

Reply With Quote
  #5 (permalink)  
Old May 28th, 2008, 01:08 AM
Friend of Wrox
 
Join Date: May 2006
Location: San Diego, CA, USA.
Posts: 643
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Maybe I misunderstood... but you mentioned a "single date range". Please clarify. Do you mean anything on a specific date, or all dates between two dates?

Either way, one inportant thing to consider is how the dates are stored in the database. If you are using a Date datatype in MySql, I believe it is formatted as YYYY-MM-DD. So to match a specific date you can do something like this (I haven't tested this code - it is just from memory, so test it out and see what happens):

SELECT * FROM unsolved WHERE Date_Paid = '2008-5-27';

You'll just have to build your date string (the 2008-5-27 part) dynamically depending on your input.

You can also use the between clause.
Hopefully this helps, if not, I'll try to look at it again tomorrow or sometime soon.





Woody Z
http://www.learntoprogramnow.com
How to use a forum to help solve problems
My blog... please visit
Reply With Quote
  #6 (permalink)  
Old May 28th, 2008, 01:24 AM
Friend of Wrox
Points: 6,664, Level: 34
Points: 6,664, Level: 34 Points: 6,664, Level: 34 Points: 6,664, Level: 34
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jan 2004
Location: Sydney, NSW, Australia.
Posts: 1,870
Thanks: 12
Thanked 20 Times in 20 Posts
Send a message via AIM to mat41
Default

I was and are still not sure if I know exactly what you are after either. You say:

;;;I only have one date field, and I simply want to be able to call up just a particular month

Which would lead me to think something like this is wat you are after:

sql = "SELECT * FROM unsolved WHERE MONTH(Date_Paid) = MONTH(yourDateValue);"

Wind is your friend
Matt
www.elitemarquees.com.au
Reply With Quote
  #7 (permalink)  
Old May 28th, 2008, 07:52 AM
Authorized User
Points: 483, Level: 7
Points: 483, Level: 7 Points: 483, Level: 7 Points: 483, Level: 7
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Apr 2007
Location: Edgewood, NY, USA.
Posts: 92
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Ok, thanks guys... but I am not sure now how to set up my pulldowns to apss the data string so the MONTH(yourDateValue) can grab it....
I have one pulldown that passes "monthdate". This has numbers 1 thru 12. The other pulldown passes "yeardate". 2006 thru 2008.
The string shows example: "date_test.asp?monthdate=1&yeardate=2008"

so how do I set up the syntax for query to accept and search this string?

if I were to use "SELECT * FROM unsolved WHERE Date_Paid = '2008-5-27';" how do i change this 2008-5-27 dynamically??

Thank again! I am probably making more out of this than what it is.

Reply With Quote
  #8 (permalink)  
Old May 28th, 2008, 10:27 AM
Authorized User
Points: 483, Level: 7
Points: 483, Level: 7 Points: 483, Level: 7 Points: 483, Level: 7
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Apr 2007
Location: Edgewood, NY, USA.
Posts: 92
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Ok i thought i was real close for a solution here....

my query:
SQL ="SELECT *, MONTH(Date_Paid) AS NewDate FROM unsolved WHERE Date_Paid Like '%" & monthdate & "' and Date_Paid Like '"&yeardate&"%' "

BUT... The returned results queries the DAY and year, NOT the MONTH and year!

I just cant get the string to only search the MONTH and year!!
Help!!!!

Reply With Quote
  #9 (permalink)  
Old May 28th, 2008, 03:05 PM
Friend of Wrox
 
Join Date: May 2006
Location: San Diego, CA, USA.
Posts: 643
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Please clarify. Are you trying to find all the records with Date_Paid being for a specific month?



Woody Z
http://www.learntoprogramnow.com
How to use a forum to help solve problems
My blog... please visit
Reply With Quote
  #10 (permalink)  
Old May 28th, 2008, 03:08 PM
Authorized User
Points: 483, Level: 7
Points: 483, Level: 7 Points: 483, Level: 7 Points: 483, Level: 7
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Apr 2007
Location: Edgewood, NY, USA.
Posts: 92
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Yes,
I am looking to show ALL records of a single month only (i.e., march or may).

Reply With Quote
Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
How to find a date range between another date rang tayvonne Access 2 August 3rd, 2006 09:50 AM
Date Range Flipflop SQL Language 6 November 25th, 2005 08:36 AM
Need Help With Date Range rpainter Crystal Reports 0 June 20th, 2005 01:01 PM
Help with Date Range PacMed Access 1 February 1st, 2005 12:30 PM
Date Range mrideout BOOK: Beginning ASP.NET 1.0 0 January 2nd, 2005 07:18 PM



All times are GMT -4. The time now is 04:31 PM.


Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.