Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 5 of 5
  1. #1
    Regular Coder
    Join Date
    Aug 2006
    Location
    Cardiff, UK
    Posts
    141
    Thanks
    15
    Thanked 2 Times in 2 Posts

    Date Format problem when using a DSN less connection

    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?
    If anyone asks my boss, this counts as work, okay?

  • #2
    Regular Coder
    Join Date
    Mar 2007
    Posts
    505
    Thanks
    1
    Thanked 19 Times in 19 Posts
    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.
    To say my fate is not tied to your fate is like saying, 'Your end of the boat is sinking.' -- Hugh Downs
    Please, if you found my post helpful, pay it forward. Go and help someone else today.

  • #3
    New Coder
    Join Date
    Jun 2007
    Posts
    32
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Thumbs up

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

  • #4
    Regular Coder
    Join Date
    Aug 2006
    Location
    Cardiff, UK
    Posts
    141
    Thanks
    15
    Thanked 2 Times in 2 Posts
    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.
    If anyone asks my boss, this counts as work, okay?

  • #5
    Regular Coder
    Join Date
    Aug 2006
    Location
    Cardiff, UK
    Posts
    141
    Thanks
    15
    Thanked 2 Times in 2 Posts
    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:
    Code:
    <%
    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:
    Code:
    <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:
    Code:
    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:
    Code:
    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.
    If anyone asks my boss, this counts as work, okay?


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •