Known Participant

Question

Displaying and updating date formats in SQL (ASP)

Forum|Forum|16 years ago
September 9, 2009
4 replies
4455 views

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

Server side applications

This topic has been closed for replies.

B

Blogsport

New Participant

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

David_Powers

Inspiring

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.

B

Blogsport

New Participant

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.

C

Chris-Designart

Participating Frequently

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

I

iandobieAuthor

Known Participant

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!

C

Chris-Designart

Participating Frequently

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

C

Chris-Designart

Participating Frequently

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?

David_Powers

Inspiring

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.

David_Powers

Inspiring

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

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.

Sign up

To post, reply, or follow discussions, please sign in with your Adobe ID.

Sign in to Adobe Community

To post, reply, or follow discussions, please sign in with your Adobe ID.

Scanning file for viruses.

This file cannot be downloaded