adding year,month,date & day to database

[lagc]

Hi all,

I have this simple cms for a client, who wants to add an event to the database using year, month, date & day.

He then wants these events to order themselves when displayed so that 2009 dates fall before 2010, then the month & date, so all nicely in order.

So this is how i thought about going about it:

<select name="dayDate" id="dayDate">
<option value = "1">Monday</option>
<option value = "2">Tuesday</option>
<option value = "3">Wednesday</option>
<option value = "4">Thursday</option>
<option value = "5">Friday</option>
<option value = "6">Saturday</option>
<option value = "7">Sunday</option>
</select>

The others work in exactly the same way, as in the value.

So once submitted the values go into the database as numbers, then what i am unsure of is how to change that value back day, and the same goes for the others, and also to have it all nicely ordered down the page.

As below:

http://www.scallywagscastle.co.uk/earlyCloseing2.asp

The example above is using the same principle, but the values are the actualy days, months, dates and years, and thats why it isnt ordering properly.

Any help, will be good, I know its basic stuff, but I need some help please.

Cheers

 

[guitarzan]

Why not simply add an "eventdate" field in your database (date datatype, not string), rather than separate Year, Month, Day etc fields. When it comes to displaying, use the various VBScript (if that's what you are using) Date functions to get the dates to display exactly as you want.

http://www.w3schools.com/VBscript/vbscript_ref_functions.asp#date

 

[vicvirk]

so are you basically trying to convert the number "1" to Monday / "2" to Tuesday ...etc when you post the results back to the screen?

Similarily with months - it's stored as "1" but you want to display "Jan"...

The year is a number anyway, unless you have:

Day: 2
Date: 1
M: 12
Y: 2008

and you want to display it as Tuesday, December 1, two thousand and eight (which I doubt)






--------

GOOGLE is a great resource to find answers to questions like "how do i..."

http://www.google.com/webhp?complete=1&hl=en

--------

 

[lagc]

Hi yes,

As you can see from the link, I have the events being displayed as day, month, year, and yes as you said because I couldnt get them to order properly, I thought id try the numbers instead, then convert them back, as I thought numbers would order better than text. But obviosuly on display the number would have to change to its corresponding value. 1=monday, 2=tuesday etc.

I have posted the code i am currently using.

<%
'initialise objects and variables

dim conn, strsql, evuser
set conn=server.createobject("ADODB.Connection")
set evuser=server.createobject("ADODB.Recordset")
strMDBpath = Server.MapPath("users2.mdb")

'direct connection
conn.open "PROVIDER=MICROSOFT.JET.OLEDB.4.0;DATA SOURCE=" & strMDBPath

strsql = "SELECT date_Id, day_date, date_date, month_date, year_date, time_start " &_
"FROM Close_Date " &_
"ORDER BY year_date DESC, month_date DESC, date_date DESC"

evuser.open strsql,conn

%>

I originally tried:

strsql = "select date_Id, day_date, CDate(day_date&'/'&date_date&'/'&month_date&'/'&year_date) " & _
"from Close_Date ORDER BY CDate(day_date&'/'&date_date&'/'&month_date&'/'&year_date) DESC"

But I couldnt get it to work without getting an error, so i reverted to the easier way.

Then to display the event, its simply:

<p><%=evuser( "day_date" )%> <%=evuser( "date_date" )%> <%=evuser( "month_date" )%> - Closing at <%=evuser( "time_start" )%></p>

I thought this would be a lot more simpler than it is.

Cheers

 

[vicvirk]

All you should have to do is grab the "date_date", "month_date" and "year_date" - the day of week is not needed, as "formatDateTime" will take care of that for you.

Order your record set by year desc, month asc, date asc - this will give you the events for the most recent year ordered correctly.

<%
strsql = "select date_Id, date_date, month_date, year_date from close_date orer by year_date desc, month_date asc, date_date asc";
set rs = myConn.execute(strsql)
	month_date = rs("month_date")
	year_date = rs("year_date")
	date_date = rs("date_date")
	event_date = cdate(month_date & "/" & date_date & "/" & year_date)
	displayDate = formatDateTime(event_date,vblongdate)
	response.write displayDate
	rs.close
set rs  = nothing
%>

--------

GOOGLE is a great resource to find answers to questions like "how do i..."

http://www.google.com/webhp?complete=1&hl=en

--------

 

[guitarzan]

In the sample you originally tried, your CDate(...) includes the day of the week (Monday, Tuesday, etc) which is not correct. Try without it:

CDate(date_date&'/'&month_date&'/'&year_date)

 

[lagc]

Hi all,

guitarzan i tried yours after trying vicvirk's and neither work. So if you dont mind I will post the code below, and the error.

<%
'initialise objects and variables

dim strsql, conn, evuser
set conn=server.createobject("ADODB.Connection")
set evuser=server.createobject("ADODB.Recordset")
strMDBpath = Server.MapPath("users.mdb")

'direct connection
conn.open "PROVIDER=MICROSOFT.JET.OLEDB.4.0;DATA SOURCE=" & strMDBPath

strsql = "select date_Id, day_date, CDate(date_date&'/'&month_date&'/'&year_date) "&_
"from Close_Date ORDER BY CDate(date_date&'/'&month_date&'/'&year_date)"

evuser.open strsql,conn

%>

And the error I'm getting for that one is;

Microsoft JET Database Engine error '80040e07' 

Data type mismatch in criteria expression. 

/earlyCloseing2.asp, line 16

All your code should work guys, i dont get it.

 

[lagc]

All those fields are text fields if that is anything useful

 

[lagc]

Ok the line of code on line 16 is below:

evuser.open strsql, conn

so i commented it out and the page appeared, but with a different error this time.

ADODB.Recordset error '800a0e78' 

Operation is not allowed when the object is closed. 

/earlyCloseing2.asp, line 121

which is referring to this code below, which is displaying the data eventually.

<%
WHILE NOT evuser.EOF
%>	
  <%=evuser( "day_date" )%>&nbsp;<%=evuser( "date_date" )%>&nbsp;<%=evuser( "month_date" )%> - Closing at <%=evuser( "time_start" )%>
<%
evuser.MoveNext
Wend
%>
<%
'close resources used
evuser.close
'conn.close

'clean up
set evuser=nothing
'set conn=nothing
%>

And that error is referring to this piece of code

<%
WHILE NOT evuser.EOF
%>

So by commenting out that line of code, i dont get the errors in the cdata code, so there must be something wrong on line 16 which i dont know im sorry

 

[lagc]

Ok ive worked out whats causing the problem, and its this bit:

date_date &'/'&

I took it away and it worked, but then I had the issue of what do I use to display that data as its inside cdata.

is the use of the word date causing the problem?

 

[guitarzan]

> ... its this bit: "date_date &'/'&" ...I took it away and it worked

So your CDate() only has month/year now? That's not going to work if you are trying to get day (and day-of-week) from it.

My advice is, I would try to figure out why you are getting the Data Type mismatch. I would add the error trapping code in blue:

strsql = "select date_Id, day_date, CDate(date_date&'/'&month_date&'/'&year_date) "&_
"from Close_Date ORDER BY CDate(date_date&'/'&month_date&'/'&year_date)"
[COLOR=blue]On Error Resume Next[/color]
evuser.open strsql,conn
[COLOR=blue]If Err.Number <> 0 Then
   response.write "Got Err#" & Err.Number & :" & Err.Description
   response.write strsql
   response.end
End If
On Error Goto 0[/color]

Now, when the error occurs, you should see the exact SQL statement that was being executed. Pay attention to the CDate() part, and see if it is a valid date inside it or not.

Regarding "what do I use to display that data as its inside cdata", you retrieve the value and use any of the date formatting functions I pointed you to in my first post (such as FormatDateTime as vicvirk mentioned).

For example, add an alias in your sql statement:

strsql = "select date_Id, day_date, CDate(date_date&'/'&month_date&'/'&year_date) [b]AS EventDate[/b] "&_
"from Close_Date ORDER BY CDate(date_date&'/'&month_date&'/'&year_date)"
...
event_date = evuser("EventDate")
response.write FormatDateTime(event_date,vblongdate)

 

[lagc]

Hi,

Ok Ive sorted the problem out and I dont know get any errors on the page, but only the last entry in the database is displayed on the website.

I'm hoping I havent annoyed anybody too much, but i have the code below in full that im using below, so could somebody have a look to see whay only the last entry is being displayed.

Thanks

<%
'initialise objects and variables

dim strsql, conn, evuser
set conn=server.createobject("ADODB.Connection")
set evuser=server.createobject("ADODB.Recordset")
strMDBpath = Server.MapPath("users.mdb")

'direct connection
conn.open "PROVIDER=MICROSOFT.JET.OLEDB.4.0;DATA SOURCE=" & strMDBPath
%>

<%
strsql = "select date_Id, day_date, date_date, month_date, year_date from Close_Date order by date_date asc, month_date asc, year_date desc "
set evuser = conn.execute(strsql)
date_date = evuser("date_date")   
month_date = evuser("month_date")    
year_date = evuser("year_date")       
event_date = cdate(date_date & "/" & month_date & "/" & year_date)    
displayDate = formatDateTime(event_date,vblongdate)    
response.write displayDate    
evuser.close
set evuser = nothing
%>

 

[lagc]

Im sorry, I got it slightly wrong.

The only date its pulling out is the the event with the lowest ID, so at present its the oldest date in the database, and so first in and not the last one in.

So Im guessing its not reading through the rest of the events and displaying them.

Lee

 

[lagc]

Sorry to keep posting, but I'm actually making some headway now.

But there still seems to be an ordering issue:

http://www.scallywagscastle.co.uk/earlyCloseing2.asp

Here is all my code:

<%
'initialise objects and variables

dim strsql, conn, evuser
set conn=server.createobject("ADODB.Connection")
set evuser=server.createobject("ADODB.Recordset")
strMDBpath = Server.MapPath("users.mdb")

'direct connection
conn.open "PROVIDER=MICROSOFT.JET.OLEDB.4.0;DATA SOURCE=" & strMDBPath
%>
<%
strsql = "select date_Id, day_date, date_date, month_date, year_date, time_start "&_ 
"from Close_Date ORDER BY date_date, month_date, year_date "
'set evuser = conn.execute(strsql)
evuser.open strsql, conn
%>
<%
WHILE NOT evuser.EOF
%>
<%
date_date = evuser("date_date")   
month_date = evuser("month_date")    
year_date = evuser("year_date")
time_start = evuser("time_start")      
event_date = cdate(date_date & "/" & month_date & "/" & year_date)    
displayDate = formatDateTime(event_date,vblongdate)    
response.write (displayDate & " - " & time_start & " <br/> ")   
'evuser.close
'set evuser = nothing
%>

<%
evuser.MoveNext
Wend
%>

<%
'close resources used
evuser.close
'conn.close

'clean up
set evuser=nothing
'set conn=nothing
%>

Its probably a complete botxh job my additions, but i got it to diaplsy at least, and the ordering starts off well, then about 6/7 dates down the line, it goes wrong a bit.

Cheers

 

[guitarzan]

> then about 6/7 dates down the line, it goes wrong a bit.

Looks correct to me, unless you fixed after posting

 

[vicvirk]

looks correct to me as well...

I would personally (unless your client has requested otherwise) remove any dates that have already passed...

It's Dec 7 today and as a visitor, letting me know that on Nov 30th (a week ago) the centre was closed at 3:45 is not important to me.

The best way to acheive this is to adjust your query so only dates that have not yet occured get passed to the recordset, or you could keep your query as is and add the following if/end if clause

event_date = cdate(date_date & "/" & month_date & "/" & year_date)    
[COLOR=red]if event_date >= date() then[/color]
displayDate = formatDateTime(event_date,vblongdate)    
response.write (displayDate & " - " & time_start & " <br/> ") 
[COLOR=red]end if[/color]

--------

GOOGLE is a great resource to find answers to questions like "how do i..."

http://www.google.com/webhp?complete=1&hl=en

--------

 

[lagc]

Yes thanks guys, as I was potching with the code, it worked and its working brilliantly.

Thank you very much for all your help.

That last bit is useful too, but in this case the client is using a basic CMS, so she can add, edit and delete dates, so I will leave them to it.

Thanks again everyone

Cheers

 

[lagc]

I will post the code, just in case anybody needs it too.

It works perfectly.

<%
'initialise objects and variables

dim strsql, conn, evuser
set conn=server.createobject("ADODB.Connection")
set evuser=server.createobject("ADODB.Recordset")
strMDBpath = Server.MapPath("users.mdb")

'direct connection
conn.open "PROVIDER=MICROSOFT.JET.OLEDB.4.0;DATA SOURCE=" & strMDBPath
%>

<%
strsql = "select date_Id, CDate(date_date&'/'&month_date&'/'&year_date) AS EventDate, time_start "&_ 
"from Close_Date ORDER BY CDate(date_date&'/'&month_date&'/'&year_date) "
evuser.open strsql, conn
%>

<%
WHILE NOT evuser.EOF
%>

<%
time_start = evuser("time_start")
event_date = evuser("EventDate")
time_start = (time_start)    
displayDate = formatDateTime(event_date,vblongdate)    
response.write ("<div style=color:#ff000E>" & displayDate & " - " & "Closed from " & time_start & " </div> <br/> ")   
%>

<%
evuser.MoveNext
Wend
%>

<%
'close resources used
evuser.close

'clean up
set evuser=nothing
%>

Cheers