Date Format problem when using a DSN less connection

[butlins]

Hi,

I recently converted the connection on my ASP (with Access 2003 database) website from a DSN connection to a DSN-less connection (the connection string is now Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\inetpub\databases\xxx\data.mdb

Everything works fine and is noticeably quicker but I have a problem with dates. In a couple of places I have hidden form fields which use <%=Date()%> to insert a "created on" or "last updated" date. These now write into the database as mm/dd/yyyy format (instead of dd/mm/yyyy format). I've tried all manner of formatting the date in the hidden field but the Access database still converts it to US format. I've also tried setting the LCID to 2057 on each page but that doesn't make any difference either.

If the date is after the 12th of the month then it inserts the correct date (as I guess it figures that 22/08/2006 can't refer to the 22nd month).

I've tried manually setting the format option in the Access database field to dd/mm/yyyy format but it still writes as mm/dd/yyyy.

I've checked my Regional Settings in my control panel and they're set for UK formats.

I've also got a couple of date pickers on the site and even after you pick the correct date (i.e. 04/08/2006 (4th Aug)) it still writes the wrong format to the database (i.e. 08/04/2006 (8th April)).

I'm really stuck with this one, I've tried everything I know but I can't get it work. I've now had to revert back to a DSN connection (the dates work perfectly with the DSN connection).

Can anybody help?

[Daemonspyre]

Have your tried doing a date-based-function insert to see if it stores the data OK?

For example:

You have tried <%= Date %> and I am sure that you have tried <%= CurDate() %> and <%= Now() %>

But, have you tried <%= datepart("dd",date()) & "-" & datepart("mm",date()) & "-" & datepart("yyyy",date()) %>

Will return the same information, but as a string, not as a date type. (however, if you run IsDate() on it, it will return true).

Just another thought.

As to your original question, I can't help you there. I don't use Access, but I would think that it is possibly a setting in your Global.asa file.

[WilliamHolmes]

Try...
<%= day(date())&"/"&month(date())&"/"&year(date())%>

[butlins]

Thanks all - tried those but I get the same problem. I've done some searching, and Daemonspyre was right in that it looks like something to do with changing the Global.asa file on the server - see these articles - http://support.microsoft.com/default...b;en-us;264063 and http://p2p.wrox.com/topic.asp?TOPIC_ID=31067 - which, unfortunately the current hosting company won't do.

[butlins]

A (hopefully) final post, in case anyone ever searches through for this problem again.

Using Dreamweaver, I was having the same problem with the contents of input areas in forms updating the Access database. As above, a date like 11 July 2007 (11/07/2006) would flip from dd/mm/yyyy to mm/dd/yyyy and back again each time it was pulled from the database into an update page and when the form was submitted to amend the database.

One of the other links in my last post led to a couple of short functions (which look similar to Daemonspyre and WilliamHolmes) posted by Mat41 on the Wrox P2P forum:

<%
function ddmmyyyy(varDate) 
        ddmmyyyy =  Day(DateValue(varDate)) & "/" & Month(DateValue(varDate)) & "/" & Year(DateValue(varDate))
end function

  function mmddyyyy(varDate)
     mmddyyyy =  Month(DateValue(varDate)) & "/" & Day(DateValue(varDate)) & "/" & Year(DateValue(varDate))
  end function
%>

So ddmmyyyy(now) returns the date in dd/mm/yyyy format, and mmddyyyy(now) returns it in mm/dd/yyyy format.

On a hunch (well, less of a hunch and more because the software's default is almost certainly to be the US mm/dd/yyyy format), I though that if I could take a date that was in dd/mm/yyyy format and force it into mm/dd/yyyy format before insertion into the database, then there would be no reason for the date flip to ever occur.

First I applied the ddmmyyyy function to the text in the input field:

<input name="DateStart" type="text" id="DateStart" title="Enter the start date" value="<%=ddmmyyyy((rsCourse.Fields.Item("DateStart").Value))%>" />

(rsCourse is the recordset name and DateStart the name of the date field in the table)

Knowing that the date was being pulled out and displayed in a consistent way, I then modified the upload code. The line that updates the table in this particular case was:

MM_editCmd.Parameters.Append MM_editCmd.CreateParameter("param2", 135, 1, -1, MM_IIF (Request.Form("DateStart"), Request.Form("DateStart")), null) ' adDBTimeStamp

Which I modified to:

MM_editCmd.Parameters.Append MM_editCmd.CreateParameter("param2", 135, 1, -1, MM_IIF(mmddyyyy(Request.Form("DateStart")), mmddyyyy(Request.Form("DateStart")), null)) ' adDBTimeStamp

So that I know that the content of the input field would be uploaded to the server consistently in mm/dd/yyyy format.

Hope this helps anyone tearing their hair out who comes after me. Obviously, credit to mat41 from Wrox for the original VBScript function which is completely portable and now safely saved for use as an include file in the site I'm currently working on.