Format issues for ASP/SQL code retrieving numbers from MS-Access Database

Question

I have a local/development website, running on a Windows 2003 server with Office 2003 installed. I have several ASP pages which extract data from a local MS Access database using pretty normal ASP code:

Site = "filedsn=" & Server.MapPath("/" & WebName & "/reffiles/accessdsn.dsn") & ";DBQ=" & Server.MapPath("/" & WebName & "/reffiles/MyDatabase.mdb") & ";DefaultDir="Server.MapPath("/" & WebName & "/") & ";"

set Database = server.createobject("ADODB.Connection")

Database.open(Site) 

strSQLMax = "SELECT Format(Max(GetDataRange.Date_Reading),'dd/mm/yy') AS MaxOfDate_Reading, Format(Max(GetDataRange.Speed), '#,###') AS MaxOfSpeed FROM GetDataRange;"

set WeekRecMax = PiDatabase.Execute(strSQLMax)

response.write("<P>Date of Maximum Speed:" & weekRecMax.fields(0).value & " " &  weekRecMax.fields(1).value & " f/hr </P>")

When I test code here (I live in France, but the local server is configured with Regional and Language settings for the USA), the results for the above code are:

Date of Maximum Speed: 14 décembre 2016 - 16:03 1 025 f/hr

When I publish my code to our production server in the USA (also Server 2003 with Office 2003) the same page gives this result:

Date of Maximum Speed: 14 December 2016 - 16:03 1,025 f/hr

Of course, the result "1 025" causes other parts of my code to throw an error as it can not be used in calculations. The space in the "1 025" is actually a "non-breaking space", hex A0.

So my question is: why is this happening and what can I do to this local server to produce output like our USA based production server?

Note, if I change the '#,###' to '####' then the calculations proceed without issues. Thus, this is not a "show stopper", but it makes me wonder what other surprises may be lurking just around the corner.

Thanks

Answer 1

Move the formatting of DateTime and Numeric values from the database to asp.net.

strSQLMax = "SELECT Max(GetDataRange.Date_Reading) AS MaxOfDate_Reading, Max(GetDataRange.Speed) AS MaxOfSpeed FROM GetDataRange;"

response.write("<P>Date of Maximum Speed:" & Convert.ToDateTime(weekRecMax.fields(0).value).ToString("dd MMMM yyyy") & " " &  Convert.ToDecimal(weekRecMax.fields(1).value).ToString("N0") & " f/hr </P>")

The formatting of the values will now be based on the localization, so the code won't break when you use it in the USA.

See more on formatting numbers here.