Exit
  • Global community
    • Language:
      • Deutsch
      • English
      • Español
      • Français
      • Português
  • 日本語コミュニティ
  • 한국 커뮤니티
0

Displaying and updating date formats in SQL (ASP)

New Here ,
Sep 09, 2009 Sep 09, 2009

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

TOPICS
Server side applications
4.4K
Translate
Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
New Here ,
Jan 07, 2010 Jan 07, 2010

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?

Translate
Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
LEGEND ,
Jan 07, 2010 Jan 07, 2010

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.

Translate
Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
New Here ,
Jan 07, 2010 Jan 07, 2010

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

Translate
Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
LEGEND ,
Jan 07, 2010 Jan 07, 2010

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.

Translate
Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
New Here ,
Jan 07, 2010 Jan 07, 2010

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

Translate
Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
LEGEND ,
Jan 07, 2010 Jan 07, 2010

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.

Translate
Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
New Here ,
Jan 07, 2010 Jan 07, 2010

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

Translate
Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
New Here ,
Jan 07, 2010 Jan 07, 2010

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!

Translate
Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
New Here ,
Jan 07, 2010 Jan 07, 2010

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

Translate
Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
New Here ,
Feb 04, 2010 Feb 04, 2010

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.

Translate
Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
New Here ,
Feb 05, 2010 Feb 05, 2010

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

Translate
Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
New Here ,
Feb 05, 2010 Feb 05, 2010

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

Translate
Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
LEGEND ,
Feb 05, 2010 Feb 05, 2010
LATEST

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.

Translate
Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines