We help IT Professionals succeed at work.

LCID: your experiences and explanations

sybe
sybe asked
on
Medium Priority
2,085 Views
Last Modified: 2012-06-21
Lately I have been looking to the "Locale" settings of ASP and databases. I got a bit confused, and I would like some expert input in the issue, and also create a nice PAQ.

I have many points to give, and will give points to anyone who has a usefull contribution to this discussion.

There are 3 "Locale" setting in an ASP-application (that is I found 3 untill now).

1. Session.LCID
2. GetLocale (and SetLocale)
3. connection.Properties("Locale Identifier")

The first two can be changed in ASP script, the last can not be changed (that one is read-only and it generates an error when trying to change it)

The two script LCID are not per definition identical. I have them natively different on my machine. And I can change them independently.
But there are some exceptions: after Session.LCID = 1033 (US setting), then the GetLocale also changes to 1033.

Also I noticed that some locale setting are "not available". Trying to set my LCID to German (1049) gives me an error - apparently because the german setting are not installed.

For seeing what it does, I displaced Now() after each change of settings.

Most of the time, the last change sets the datetimeformat. But not always.
After:
<%
SetLocale(1043)      ' set to Ducth
Session.LCID = 1033  ' set to US
%>
The date is still displayed in Dutch format.

Ok, so here is my test code:

===================================

<%
Option Explicit

Dim sDSN, oConn, iLCID_Locale, iLCID_Db, iLCID_Session
Dim iOriginalLocaleLCID, iOriginalSessionLCID, iOriginalDBLCID
sDSN = "provider=Microsoft.jet.oledb.4.0; data source = " & Server.MapPath("/sybe")  & "\_db\test_all.mdb;"
Set oConn = Server.CreateObject("ADODB.Connection")
oConn.Open sDSN

' getting starting values

iLCID_Locale = GetLocale
iLCID_Session = Session.LCID
iLCID_Db = oConn.Properties("Locale Identifier")

iOriginalLocaleLCID = iLCID_Locale
iOriginalSessionLCID = iLCID_Session
iOriginalDBLCID = iLCID_Db

Response.write "<hr>original settings<p>"
Response.write "Locale LCID = " & iLCID_Locale & "<br>"
Response.write "Session LCID = " & iLCID_Session & "<br>"
Response.write "Database LCID = " & iLCID_Db & "<br>"
Response.write Now() & "<br>"

' changing Session.LCID
Response.write "<hr>after setting Session.LCID = 1043<p>"
Session.LCID = 1043

iLCID_Locale = GetLocale
iLCID_Session = Session.LCID
iLCID_Db = oConn.Properties("Locale Identifier")

Response.write "Locale LCID = " & iLCID_Locale & "<br>"
Response.write "Session LCID = " & iLCID_Session & "<br>"
Response.write "Database LCID = " & iLCID_Db & "<br>"
Response.write Now() & "<br>"

SetToOriginal


Response.write "<hr>after setting Session.LCID = 1033<p>"
Session.LCID = 1033

iLCID_Locale = GetLocale
iLCID_Session = Session.LCID
iLCID_Db = oConn.Properties("Locale Identifier")

Response.write "Locale LCID = " & iLCID_Locale & "<br>"
Response.write "Session LCID = " & iLCID_Session & "<br>"
Response.write "Database LCID = " & iLCID_Db & "<br>"
Response.write Now() & "<br>"

SetToOriginal


Response.write "<hr>after setting Session.LCID = 2048<p>"
Session.LCID = 2048

iLCID_Locale = GetLocale
iLCID_Session = Session.LCID
iLCID_Db = oConn.Properties("Locale Identifier")

Response.write "Locale LCID = " & iLCID_Locale & "<br>"
Response.write "Session LCID = " & iLCID_Session & "<br>"
Response.write "Database LCID = " & iLCID_Db & "<br>"
Response.write Now() & "<br>"

SetToOriginal



' changing with SetLocale
Response.write "<hr>after setting SetLocale(1043)<p>"
SetLocale(1043)

iLCID_Locale = GetLocale
iLCID_Session = Session.LCID
iLCID_Db = oConn.Properties("Locale Identifier")

Response.write "Locale LCID = " & iLCID_Locale & "<br>"
Response.write "Session LCID = " & iLCID_Session & "<br>"
Response.write "Database LCID = " & iLCID_Db & "<br>"
Response.write Now() & "<br>"

SetToOriginal



Response.write "<hr>after setting SetLocale(2048)<p>"

SetLocale(2048)

iLCID_Locale = GetLocale
iLCID_Session = Session.LCID
iLCID_Db = oConn.Properties("Locale Identifier")

Response.write "Locale LCID = " & iLCID_Locale & "<br>"
Response.write "Session LCID = " & iLCID_Session & "<br>"
Response.write "Database LCID = " & iLCID_Db & "<br>"
Response.write Now() & "<br>"

SetToOriginal


Response.write "<hr>after setting SetLocale(1033)<p>"
SetLocale(1033)

iLCID_Locale = GetLocale
iLCID_Session = Session.LCID
iLCID_Db = oConn.Properties("Locale Identifier")

Response.write "Locale LCID = " & iLCID_Locale & "<br>"
Response.write "Session LCID = " & iLCID_Session & "<br>"
Response.write "Database LCID = " & iLCID_Db & "<br>"
Response.write Now() & "<br>"

SetToOriginal


Response.write "<hr>after setting Session.LCID = 1033 and SetLocale(1043) <p>"
Session.LCID = 1033
SetLocale(1043)

iLCID_Locale = GetLocale
iLCID_Session = Session.LCID
iLCID_Db = oConn.Properties("Locale Identifier")

Response.write "Locale LCID = " & iLCID_Locale & "<br>"
Response.write "Session LCID = " & iLCID_Session & "<br>"
Response.write "Database LCID = " & iLCID_Db & "<br>"
Response.write Now() & "<br>"

SetToOriginal


Response.write "<hr>after setting SetLocale(1043) and Session.LCID = 1033  <p>"
SetLocale(1033)
Session.LCID = 1043

iLCID_Locale = GetLocale
iLCID_Session = Session.LCID
iLCID_Db = oConn.Properties("Locale Identifier")

Response.write "Locale LCID = " & iLCID_Locale & "<br>"
Response.write "Session LCID = " & iLCID_Session & "<br>"
Response.write "Database LCID = " & iLCID_Db & "<br>"
Response.write Now() & "<br>"

SetToOriginal


Response.write "<hr>after setting SetLocale(1033) and Session.LCID = 1043  <p>"
SetLocale(1033)
Session.LCID = 1043

iLCID_Locale = GetLocale
iLCID_Session = Session.LCID
iLCID_Db = oConn.Properties("Locale Identifier")

Response.write "Locale LCID = " & iLCID_Locale & "<br>"
Response.write "Session LCID = " & iLCID_Session & "<br>"
Response.write "Database LCID = " & iLCID_Db & "<br>"
Response.write Now() & "<br>"

SetToOriginal




' setting back to original
SetToOriginal

Response.write "<hr>after resetting to original<p>"

iLCID_Locale = GetLocale
iLCID_Session = Session.LCID
iLCID_Db = oConn.Properties("Locale Identifier")

Response.write "Locale LCID = " & iLCID_Locale & "<br>"
Response.write "Session LCID = " & iLCID_Session & "<br>"
Response.write "Database LCID = " & iLCID_Db & "<br>"
Response.write Now() & "<br>"


oConn.Close
Set oConn = Nothing

Sub SetToOriginal()
    Session.LCID = iOriginalSessionLCID
    SetLocale(iOriginalLocaleLCID)
End Sub

%>


===================================

and here is my result
===================================

----------------------------------------------------------original settings
Locale LCID = 1043
Session LCID = 2048
Database LCID = 1033
13-12-2002 11:08:02



----------------------------------------------------------after setting Session.LCID = 1043
Locale LCID = 1043
Session LCID = 1043
Database LCID = 1033
13-12-2002 11:08:02


----------------------------------------------------------after setting Session.LCID = 1033
Locale LCID = 1033
Session LCID = 1033
Database LCID = 1033
12/13/2002 11:08:02 AM



----------------------------------------------------------after setting Session.LCID = 2048
Locale LCID = 1043
Session LCID = 2048
Database LCID = 1033
13-12-2002 11:08:02



----------------------------------------------------------after setting SetLocale(1043)
Locale LCID = 1043
Session LCID = 2048
Database LCID = 1033
13-12-2002 11:08:02



----------------------------------------------------------after setting SetLocale(2048)
Locale LCID = 1043
Session LCID = 2048
Database LCID = 1033
13-12-2002 11:08:02



----------------------------------------------------------after setting SetLocale(1033)
Locale LCID = 1033
Session LCID = 2048
Database LCID = 1033
12/13/2002 11:08:02 AM



----------------------------------------------------------after setting Session.LCID = 1033 and SetLocale(1043)
Locale LCID = 1043
Session LCID = 1033
Database LCID = 1033
13-12-2002 11:08:02



----------------------------------------------------------after setting SetLocale(1043) and Session.LCID = 1033
Locale LCID = 1043
Session LCID = 1043
Database LCID = 1033
13-12-2002 11:08:02



----------------------------------------------------------after setting SetLocale(1033) and Session.LCID = 1043
Locale LCID = 1043
Session LCID = 1043
Database LCID = 1033
13-12-2002 11:08:02

----------------------------------------------------------after resetting to original
Locale LCID = 1043
Session LCID = 2048
Database LCID = 1033
13-12-2002 11:08:02




Comment
Watch Question

Author

Commented:
I am interested in things like:

Which (registry-) settings control the different LCID's.

Which LCID determines what.

Why are Get/SetLocale and Session.LCID sometimes independent and sometimes not.

Commented:
Is this a post to discuss the problems with locale or to identify a way to format dates? :-)

I try to avoid LCID and use the date format in the database of dd-mmm-yy (or dd-mmm-yyyy) e.g 18-DEC-2002

Absolutely no ambiguity. You can then format this however you want.. I use a custom formatDate function:

Function DateConvert(MyDate)

     Dim strDay
     Dim intDate
     Dim strMonth
     Dim intyear
     Dim WDay
     Dim MMonth
     Dim int4digyear

     WDay = WeekDay(MyDate)
     MMonth = Month(MyDate)
     strDay = WeekDayName(Wday)
     intDate = Day(MyDate)
     strMonth = MonthName(MMonth)
     intYear = Year(MyDate)
     int4digyear = Right(intYear,4)

     DateConvert = " " & strDay & ", " & _
                       intDate & " " & strMonth & " " & int4digyear
  End Function


Quiet day at work?!

mark

Author

Commented:
Yes, dateformat has to do with it.

>> I try to avoid LCID and use the date format in the database of dd-mmm-yy (or dd-mmm-yyyy) e.g 18-DEC-2002

This does not work always. For example a Dutch-LCID database won't accept 18-MAY-2002. This goes only wrong with May and October, where the Ducth and US month abbreviations differ. But a dateformat solution should work always, not most of the time :)


Author

Commented:
I use a similar function, but using date-format
"Dec 13, 2002", and handling time and invalid datestrings.

But as said, it fails when ASP-LCID and Database LCID differ.

Function DBDate(ByVal var)
    Dim aMonthNames, dtTemp, iTotalSeconds, dtTime

    aMonthNames = Array(MonthName(1,True),MonthName(2,True),MonthName(3,True),MonthName(4,True),MonthName(5,True),MonthName(6,True),MonthName(7,True),MonthName(8,True),MonthName(9,True),MonthName(10,True),MonthName(11,True),MonthName(12,True))
    On Error Resume Next
    dtTemp = CDate(Cstr("" & var))
    If Err Then Exit Function
    DBDate = aMonthNames(Month(dtTemp)-1) & " " & Day(dtTemp) & ", " & Year(dtTemp)
    If Err Then Exit Function
    iTotalSeconds = DateDiff("s",CDate(DBDate),dtTemp)
    If iTotalSeconds > 0 Then
        dtTime = CDate(CDate(DBDate)-dtTemp)
        DBDate = DBDate & " " & dtTime
    End If
End Function

Commented:
I personally would stick to the normal database month convention and use a function to replace the month abbreviation when used on screen. That way if you port your database to another country, server, wherever the data will be OK. Like a lookup table I guess. Set up a application variable  in the global.asa depending upon country implementation and then use that on each page to set the display as you want.. Does that make sense? Haven't been to the pub yet (7 hrs to go :-)) and I've already got a headache...

Author

Commented:
Well, the problem is not with displaying date variables. As soon as you have a variable of type "Date", then it is no problem to display it in any desired format.

The problem is the other way around: how to get dates, that are formatted as a string, into the database as a date, in a way that the code will work on any server, no matter what the regional setting are.

So far I have only found ways in which is somewhere hardcoded what the date-format of the database is.
And I don't like hardcoding stuff like date-format.

Asking the database what its internal monthnames are, seems possible, but fails with ADBODB.

SELECT Monthname(1)+","+Monthname(2)+","+Monthname(3)+","+Monthname(4)+","+Monthname(5)+","+Monthname(6)+","+Monthname(7)+","+Monthname(8)+","+Monthname(9)+","+Monthname(10)+","+Monthname(11)+","+Monthname(12) AS monthnames

works directly in ACCESS, but when run from ASP, JET Driver does not recognize the Monthname-functions and gives an error.

My 2 cents

I have never used it. but suggested lots of people and all (or at least 80%) of them came back saying "Not working".
So, I had to suggest them to change regional setting to the specific country

Commented:
If you are needing a way to standardize the format in which the date is submitted to the server, you can use the following function that will convert a date into ISO format, which is identical (at least for Access) no matter what country you live in.

Function IsoDate(dteDate)
   If IsDate(dteDate) = True Then
      DIM dteDay, dteMonth, dteYear
      dteDay = Day(dteDate)
      dteMonth = Month(dteDate)
      dteYear   = Year(dteDate)
      IsoDate = dteYear & _
         "-" & Right(Cstr(dteMonth + 100),2) & _
         "-" & Right(Cstr(dteDay + 100),2)
   Else
      IsoDate = Null
   End If
End Function
CERTIFIED EXPERT

Commented:
No comment has been added lately, so it's time to clean up this TA.
I will leave a recommendation in the Cleanup topic area that this question is:
[PAQ with REFUND]

Please leave any comments here within the next seven days.

PLEASE DO NOT ACCEPT THIS COMMENT AS AN ANSWER!

hongjun
EE Cleanup Volunteer
Question is PAQ and points refunded.

YensidMod
Community Support Moderator @Experts Exchange