Localization issues VB6 SP6 vs SQL Server 2000 SP SP3a

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Brad Ashforth - 14 Nov 2007 21:17 GMT

Please note that there could be a possible cross post as I originally posted
this elsewhere and was advised by an MVP there to post the question elsewhere.
We have the environment mentioned above. The Server OS is Windows Server 2003
SP2 and the client machine is XPPro SP 2. Several years ago we coded the
application to "support localization". At that time we supported british and
italian only and were not aware of any issues. A new client is coming on
board who will also use british settings. The new client of course required
some customization and during test we found several places where our existing
localization code (dates seem to be the only issue) reports errors but the
date format being sent appears correct. We also see differnces between using
the ado.command object vs sending a correctly formatted sql statement via the
ado.connection.execute method.

As I am unsure what code samples may be necessary for assistance I have not
posted anything here yet ... if anyone is able to respond please let me know
what information and code samples you may need to help. Thank you in advance

Reply to this Message

Andrea Montanari - 15 Nov 2007 11:26 GMT

hi Brad,

> Please note that there could be a possible cross post as I originally
> posted this elsewhere and was advised by an MVP there to post the
[quoted text clipped - 15 lines]
> please let me know what information and code samples you may need to
> help. Thank you in advance

using ado commands, and related parameters, you should avoid "dates"
problem, as these are later converted by ado it self to the correct
representation..
as regards "literals", where your code executes dynmic sql you formatted,
you should provide date values in ISO YYYYMMDD format, which can be
interpreted by SQL Server without recurring to current logged user setting
regarding his/her relative locale.. so code like
"SELECT ... WHERE [Date] = '20071115'" for november 15th..

number with decimals could be another issue.. always verify the decimal
separator is "."..
"SELECT ... WHERE [Decimal] = 12.123"
regards

Signature

Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz http://italy.mvps.org
DbaMgr2k ver 0.21.0 - DbaMgr ver 0.65.0 and further SQL Tools
--------- remove DMO to reply

Reply to this Message

Brad Ashforth - 15 Nov 2007 17:02 GMT

Thank you Andrea ... I will try this to see if it solves the problem. As far
as I know the dates being sent to the stored procedure via the
ado.command.parameters are sent as format(<<datevalue>>, "Short Date"). The
dates being sent via direct SQL execution are similar as the SQL statment is
dynamically created and the date value is set in the string the same way as
in:

strSQL = "Update table set DateValue = '" & format(datevalue, "Short Date")
& "' where <criteria>"

> hi Brad,
> > Please note that there could be a possible cross post as I originally
[quoted text clipped - 30 lines]
> "SELECT ... WHERE [Decimal] = 12.123"
> regards

Reply to this Message

Brad Ashforth - 15 Nov 2007 20:52 GMT

Hi Andrea ... I wanted to follow up and let you know that changing code
from
format(<datecontrol>, "Short Date")
to
format(<datecontrol>, "yyyymmdd")
worked as you suggested.

My concern now is "why"? The reason I ask is that we have several "forms"
that save in a similar manner (e.g. set ado.command object params and
execute). Each used format as "Short Date". Only ONE of the forms required
this change ... the others work using "Short Date". The only thing we can see
that is different is that in the one that failed for some reason the original
developer had defined the params as varchar(20) in the failing routine and
varchar(10) in the ones that don't fail. We had tried making the same change
by changing varchar(20) to varchar(10) but the err still happened. So we are
still wondering "why" is there a difference ... obviously concerned that it
will come back to haunt us some day ... :)

> hi Brad,
> > Please note that there could be a possible cross post as I originally
[quoted text clipped - 30 lines]
> "SELECT ... WHERE [Decimal] = 12.123"
> regards

Reply to this Message

Andrea Montanari - 16 Nov 2007 11:02 GMT

> My concern now is "why"? The reason I ask is that we have several
> "forms" that save in a similar manner (e.g. set ado.command object
[quoted text clipped - 7 lines]
> still wondering "why" is there a difference ... obviously concerned
> that it will come back to haunt us some day ... :)

actually I do not have an answer to your specific question regarding this
Ado "problem"...

but, again, regarding dates and their "management" in SQL Server in input
and output I storngly recommend you to have a look at
http://www.karaszi.com/SQLServer/info_datetime.asp by MVP fellow Tibor
Karaszi and, if possible (available to subscriber only)
http://www.sqlmag.com/Articles/ArticleID/9147/9147.html?Ad=1 by Our Lady SQL
Server Kalen Delaney...
regards

Signature

Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz http://italy.mvps.org
DbaMgr2k ver 0.21.0 - DbaMgr ver 0.65.0 and further SQL Tools

Reply to this Message

VB Forum / Database Access / November 2007

Localization issues VB6 SP6 vs SQL Server 2000 SP SP3a