Displaying and updating date formats in SQL (ASP)
Copy link to clipboard
Copied
Hi,
I'm using MS SQL server 2005 with ASP VB. I have a table in a database with a date column, that is set to datetime type.
I have a page to insert a record into the table, and in the page form, one of the fields is a date field which defaults to tdays date using <%= date %> which works fine. When called, the page displays todays date, in the format DD/MM/YYYY which is great. But the form inserts the record with the date format MM/DD/YYYY.
But I also have an edit page for those records. And if I edit the record, it switches the date back to DD/MM/YYYY format. How can I ensure consistent date format in DD/MM/YYYY?
Many thanks
Copy link to clipboard
Copied
Did you ever find a solution to this?
I have been running a number of sites with date entry over the past few years. I changed hosts from Fasthosts to HeartInternet in the past month and now I have what sounds like the same problem as you report.
The sites all use Classic ASP and standard Dreamweaver constructs for form entry, database update, etc. They write to an Access database and the 'culture' for all parts is en-GB giving dd/mm/yyyy input and writing that in the database, too.
These sites have all run faultlessly for years but the change to the new hosts has them flipping the data format back and forth as you describe (I have a text page on one of my sites showin the effect www.stocksfieldgolfclub.co.uk/datetest-1.asp )
With the best will in the world, the hosts are unable to see what settings are causing this.
Anyone with any ideas?
Copy link to clipboard
Copied
It's many years since I dumped ASP in favour of PHP, but I remember that certain things, such as date formats are dependent on the locale of the server. A quick Google search turned up the following page, which I think probably provides the answer to your problem: http://www.webwizguide.com/kb/asp_knowledgebase/date_time_settings.asp.
Copy link to clipboard
Copied
Thanks for the response, David.
I'm afraid its not as easy as that. I have tried all sorts of options for setting the locale, the 'culture' as they call it. There is a web.config file setting it for the whole domain and we've tried individual Session settings on the pages themselves, too.
The problem is, the server apparently IS in the right locale. Using <%=Date%> for instance returns a UK formatted reply! Everything works fine except at the point of transferring the input through the server to the database, at which point the date is flipped. try my www.stocksfieldgolfclub.co.uk/datetest-1.asp to see it in action. As long as the day number is 12 or less (if it is higher, access automatically assumes the format!!) then repeated submits flip the date back and forth.
It seems possible that it is down to the connection method of the database to the server. The new hosts use a different string to make the connection and it has been suggetsed that the ADO setup may be doing some translating in the process of making the connection.
At the moment I'm faced with trying to find the most elegant way of retro-progamming several existing sites which all exhibit the 'feature'.
My current best attempt is http://www.stocksfieldgolfclub.co.uk/transpose-date-parts-trap-null.asp which uses a simple function call in the update code for each time a date is used.
Thanks again.... Chris
Copy link to clipboard
Copied
Chris-Designart wrote:
I'm afraid its not as easy as that. I have tried all sorts of options for setting the locale, the 'culture' as they call it.
Sorry it didn't fix it. It was problems like this that persuaded me to move away from ASP about eight or nine years ago. Microsoft abandoned development of ASP in favour of ASP.NET round about that time, but ASP.NET wasn't ready for production use, so I switched to PHP. It's a decision I've never regretted.
I realize that's not much help or consolation to you, but ASP is very much a dying technology. Changing to another server-side solution isn't going to be easy, but you'll need to do it at some stage. If you can't find a solution to your problem soon, this might be the time to start thinking of an alternative.
Copy link to clipboard
Copied
Already on it, thanks. I'm going to switch over to MySql/PHP.
As you say, a bit of a learning curve, but all I hear about ASP.NET suggests it has even more problems!
Anyway, Access is too flakey and the alternative of SQL Server, too expensive to use!
Shame, 'cos I'm very comfortable with VBScript, but I guess I'll manage. My hosts offer full MySql/PHP support on Linux and it obviously their first love. All their tech guys know what they are talking about with PHP but seem fairly lost with ASP
Hardest thing is to find the time to learn - too many clients wanting it now!
Thanks again.... Chris
Copy link to clipboard
Copied
Chris-Designart wrote:
Hardest thing is to find the time to learn - too many clients wanting it now!
If you're already familiar with Dreamweaver server behaviors, you might find my book, "The Essential Guide to Dreamweaver CS4 with CSS, Ajax, and PHP", gives you a quick leg-up. It's not a formal PHP/MySQL textbook, but requires no prior knowledge of either technology. It shows how to set up a database in MySQL, and not only shows the use of the server behaviors, but also explains the code so you can customize it to your own requirements.
You'll also find that your familiarity with ASP helps a great deal. Although VBScript and PHP don't look very much like each other, you'll find concepts such as variables, arrays, and loops, reduce the learning curve considerably.
Copy link to clipboard
Copied
Great! - I'll take a look at that - sounds just the thing.
I used a book of 'recipes' to get me started with my first data driven sites and that has the recipes in ASP, Cold Fusion and PHP, so I'll have exact comparisons to some of my existing structures too, which I'm sure will also help.
Thanks for all your time and help.... Chris
Copy link to clipboard
Copied
Hi,
yes I did fix the problem, and yes it was to do with the regional settings of the server. I'm running a Win 2003 Small Business Server and so if I go into the Control Panel and check the Regional & Language Options, under the Advanced tabe there's a checkbox saying "Apply all settings to the current user account and to the default user profile" - if I tick that then the local regional settings (including date formats) are applied to all accounts, and it worked without a problem after that.
Hope that helps - though not sure if it will with a commercial ISP...good luck!
Copy link to clipboard
Copied
Thanks for the response, Ian.
I don't think it can be as simple as this as I've had their whole tech team working on it since new year, but I will pass it on for them to consider. As you no doubt guessed, I have relatively little direct control over the server itself - its a shared resource, so lots of sites run from one machine - but I do have basid configuration via a web.config file. We have everything we can think of set to UK format but there is a US one getting in in the middle of sending data to the database and that is what is causing the problem.
http://www.stocksfieldgolfclub.co.uk/datetest-1.asp will illustrate the problem
http://www.stocksfieldgolfclub.co.uk/transpose-date-parts-trap-null.asp is my current best solution for an easy change to all of the pages in existing sites suffering from this problem (The function is simple to apply and re-usable within a page)
Thanks again.... Chris
Copy link to clipboard
Copied
I found a solution for date access issue i
n combination with insert/update behaviours
http://www.codingforums.com/showthread.php?t=117190
And still is ASP a good working language. I use it for several years now.
Copy link to clipboard
Copied
Thanks for taking the time to respond!
The solution at the bottom of the thread is pretty much the way I have been forced to go. It doesn't actually solve the root problem (ie. 'fix' the problem for the future) but it does give a useable workaround that fixes it site by site. Does mean I'm faced with inserting a lot of code for the several sites I have with this problem, but it does at least fix these sites.
Seems likely that the problem (in my case) occurs with the change of connection method with the new hosts and something in the ADO routines doing the unwanted switch. Can't be fixed as far as the hosts are concerned so I havwe had to change mny sites instead.
Thanks again..... Chris
Copy link to clipboard
Copied
Hi Chris,
I was facing the same problem. I used the the Microsoft Access Driver connectionstring but had big performance problems with websites. So I decided to use the Microsoft Jet OLEDB connectionstring.
So I faced the date problem 01-02-2010 [1 february 2010] becomes 02-01-2010 [2 january 2010] while inserting/updating database with the Dreamweaver insert/update behaviour. In the earlier days of Dreamweaver i use a script found here:
http://www.dmxzone.com/go?4828
This was also a good workaround. But when Dreamweaver CS3 was released. They rebuild the insert/update behaviour. So I searched for another solution and found:
http://www.codingforums.com/showthread.php?t=117190
But at the moment you have rewrite all the ASP pages containing date field in combination with the DW insert/update behaviour. I don't know of Adobe is aware of this problem. I think they are but it is an United States company and think that everyone using the same date:).
I hope they wil find a workaround when using a insert/update behaviour.There are a lot of rumours that they will abandon ASP in the future DW releases and go for PHP. I hope in future release they will continue support ASP despite that Microsoft stopped develop ASP several years ago. Despite it remain a simple and good language with a lot of opportunities.
By the way you can use a server-side include file for the script.But you still have to rewrite the insert/update behaviours code
Cheers
Copy link to clipboard
Copied
Blogsport wrote:
There are a lot of rumours that they will abandon ASP in the future DW releases and go for PHP. I hope in future release they will continue support ASP despite that Microsoft stopped develop ASP several years ago. Despite it remain a simple and good language with a lot of opportunities.
Rumours are just that: rumours.
However, I think it would be unwise to expect Adobe to devote much attention to increased support for a technology that Microsoft abandoned ten years ago. I was doing some testing the other day, and discovered that the Microsoft Web Platform Installer does not install classic ASP by default in IIS7. It's now just an option.
Interestingly, the Web Platform Installer makes it very easy to install PHP in IIS. Maybe Microsoft is sending a message.

