Wrox Programmer Forums
|
Classic ASP Professional For advanced coder questions in ASP 3. 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 Professional section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old October 21st, 2004, 01:22 AM
Registered User
 
Join Date: Oct 2004
Posts: 7
Thanks: 0
Thanked 0 Times in 0 Posts
Default ASP - Drop Down Box with Date

Hi ladies and gentlemen,

I have encountered a problem regarding Drop-Down Box on Date (Month). For example, "Jan - 2004", "Feb - 2004", "Mar - 2004", etc...

When the user click on a particular Month, a series of data will be retrieve from the access database (according to the Month selected) and show up in asp page...

I have everything in place, except for the coding/implementation on the Drop-Down box... The dates in the database is in daily event, but my Drop-down box is in Monthly selection... So, how can I call up the particular dates (and gather them nicely in Month format) that I want from the database?

Sorry for my poor English, really appreciate bros who can help me here... Urgent case... Thanks in advance...
 
Old October 21st, 2004, 02:42 AM
Friend of Wrox
 
Join Date: Oct 2003
Posts: 463
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via MSN to madhukp
Default

AFAICU, your requirement is to get those events whose month is chosen month. You can use the Month function of access for this. Here is a sample query.

SELECT EventName, EventDate FROM tblEvents WHERE Month(EventDate)=" & Request.Form("cboEventMonth")

Where cboEventMonth is the name of the combo where you place months.

Hope this would help.
 
Old October 21st, 2004, 05:17 AM
Registered User
 
Join Date: Oct 2004
Posts: 7
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Hi madhukp,

May I know whether I need to add any ASP coding on the drop down box? How shall I go about in the option value("??")? What shall I put?

<select name="ViewDate" size="1">
<%
if ViewDate <> "" then %>
<option selected value="<%= ViewDate %>"><%= ViewDate %></option>
<% end if %>
<option value="">Please choose one</option>
<option value="????????????">Oct 2004</option>
</select>
<input type="submit" value="Submit">



 
Old October 21st, 2004, 05:30 AM
Friend of Wrox
 
Join Date: Oct 2003
Posts: 463
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via MSN to madhukp
Default

You may put

01-2004 for Jan 2004
02-2004 for feb 2004
03-2004 for mar 2004
.....
.....
01-2005 for Jan 2005

etc. etc.

Then at the processing page you can split the value of Request.Form("ViewDate") at the character - (hyphen) to get month value and year value. Please refer the std. function Split.
 
Old October 21st, 2004, 10:37 AM
Friend of Wrox
 
Join Date: Jun 2003
Posts: 2,480
Thanks: 0
Thanked 1 Time in 1 Post
Default

Hi HelpMe, (Trying to help you;))

Is that dropdown box a static or dynamic? How many values are you looking to show there in that( min and max limit)?

If static, I would say you better split that dropdown into 2 (Month and Year)Check out this code. Just copy paste this in an empty asp file and browse that to see how it behaves.
Code:
<% 
    MM=Request.Form("MM")
    yyyy=Request.Form("yyyy")

    mon = Month(date())
    If len(MM)=0 then MM=mon

    Yr = Year(date())
    MinYr = Yr - 2
    MaxYr = Yr  + 2
    If len(yyyy)=0 then yyyy=Yr
%>

<Form name="frm" method="Post" action="">


Month : <Select name="MM">
<%
    For i = 1 to 12
%>
        <Option Value="<%=i%>" <%If cint(i) = cint(MM) then%> Selected <%End If%>><%=MonthName(i)%></option>
<%
    Next
%>
</Select>


Year : <Select name="yyyy">
<%
    For i = MinYr to MaxYr
%>
        <Option Value="<%=i%>" <%If cint(i) = cint(yyyy) then%> Selected <%End If%>><%=i%></option>
<%
    Next
%>
</Select>

<input type="Submit" value="Submit">
</Form>

<% Response.write "Select * from TABLENAME where Year(DATECOLUMN) = " & yyyy & " and Month(DATECOLUMN) = " & MM %>
Hope that helps.
Cheers!

_________________________
- Vijay G
Strive for Perfection
 
Old October 21st, 2004, 09:36 PM
Registered User
 
Join Date: Oct 2004
Posts: 7
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Thanks madhukp and happygv for the prompted reply :)

So happygv, does that mean that I need to change my Month, Day and Year to 3 seperate Field Names (on my Access Database) if I apply the above coding? As currently I am using Date() format, and the database recorded is on the MM-DD-YYYY format (one Field Name)...

Can the ASP coding determine the "Year(DATECOLUMN)" and "Month(DATECOLUMN)" and pull up the required data I need, just on the Date() Field Name I've created?

Thanks
 
Old October 21st, 2004, 10:52 PM
Registered User
 
Join Date: Oct 2004
Posts: 7
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Sorry to trouble you, happygv...

I have received this error "Data type mismatch in criteria expression." when applying your coding to my ASP script...

<%
    MM=Request.Form("MM")
    yyyy=Request.Form("yyyy")

    mon = Month(date())
    If len(MM)=0 then MM=mon

    Yr = Year(date())
    MinYr = Yr - 2
    MaxYr = Yr + 2
    If len(yyyy)=0 then yyyy=Yr
%>

<Form name="frm" method="Post" action="">


<p align="center">Month : <Select name="MM">
<%
    For i = 1 to 12
%>
        <Option Value="<%=i%>" <%If cint(i) = cint(MM) then%> Selected <%End If%>><%=MonthName(i)%></option>
<%
    Next
%>
</Select>


Year : <Select name="yyyy">
<%
    For i = MinYr to MaxYr
%>
        <Option Value="<%=i%>" <%If cint(i) = cint(yyyy) then%> Selected <%End If%>><%=i%></option>
<%
    Next
%>
</Select>

<input type="Submit" value="Submit"> </p>
</Form>
<%
MM=Request.Form("MM")
yyyy=Request.Form("yyyy")
if MM<>"" then
accessdb="Green"
cn="DRIVER={Microsoft Access Driver (*.mdb)};"
cn=cn & "DBQ=" & server.mappath(accessdb)
Set rs = Server.CreateObject("ADODB.Recordset")
sql = "SELECT Field1,Field2,Field3,Field4 FROM Table1 WHERE MyMonth = " & MM & " and MyYear = " & yyyy
rs.Open sql, cn
%>

My Field Names in Access Database are in Text format... Not in Date/Time format... May I know where went wrong?

Thanks

 
Old October 21st, 2004, 11:47 PM
Registered User
 
Join Date: Oct 2004
Posts: 7
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Thanks, I got it myself... :-p

I forgotten to put "'" for my text format... :-D

A thousand apologies... And the end result works perfectly...

Thanks again.. madhukp and happygv :)

 
Old October 24th, 2004, 06:24 AM
Friend of Wrox
 
Join Date: Jun 2003
Posts: 2,480
Thanks: 0
Thanked 1 Time in 1 Post
Default

Quote:
quote:Originally posted by HelpMe
 Can the ASP coding determine the "Year(DATECOLUMN)" and "Month(DATECOLUMN)" and pull up the required data I need, just on the Date() Field Name I've created?
 Yes it can. But actually in that statement, it is the database that is going to parse the YEAR() and MONTH() functions, not the ASP code. It very well works with ASP code too as the same functions are available in VBScript.

_________________________
- Vijay G
Strive for Perfection
 
Old October 24th, 2004, 06:29 AM
Friend of Wrox
 
Join Date: Jun 2003
Posts: 2,480
Thanks: 0
Thanked 1 Time in 1 Post
Default

Hey HelpMe,

Glad that you got that solved (helped yourself;)). I was not able to find time for posting replies here yesterday.

Is that you have split the date values in the database into three columns? I wouldn't have suggested that. It is best that you have them as datetime type into a single column. As suggested in my other post which had the SELECT statement posted, it would very well pull data based on YEAR(DATECOLUMN) and MONTH(DATECOLUM). So you dont have to worry about that. Later on when you really wanted to do operations based on date, you wouldn't be feeling confortable if you have those values (month, day, year) all in separate columns. That would put you in trouble asking to look for work arounds. It is better you split them and operate only in the frontend(for convenience), but let that be the datetime type in your database.

Hope that helps.
Cheers!

Cheers!

_________________________
- Vijay G
Strive for Perfection





Similar Threads
Thread Thread Starter Forum Replies Last Post
ASP - drop down box issues mhodges Classic ASP Professional 2 November 28th, 2006 07:23 PM
drop down box selected value crmpicco HTML Code Clinic 1 March 31st, 2005 09:56 AM
ASP - Drop Down Box with Dates HelpMe Classic ASP Databases 1 October 21st, 2004 10:39 AM
Drop down Box NomAnor Classic ASP Basics 2 March 29th, 2004 12:21 PM
Search using drop down list box and a text box tcasp Classic ASP Basics 1 July 31st, 2003 02:58 PM





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