Translation of date, currency and decimal formats (add/edit)

[Aivar Otsing]

I have problem with currency and date transformation if I use add/edit/update.
I use MSSQL7 and NT4.0 server. SQL default language is "English" and NT4 locale, regional settings etc are "Estonian".
The testcode is here:
SET usr1=Server.CreateObject("ASP.DB&#34
usr1.dbUnit=40
usr1.dbDAT="testserver,testbase,testuser,testp wd"
usr1.dbSelectFrom=True
usr1.dbEditParams="TableName=test,BookMarkFlds =ID,RecordScope=Single"
usr1.dbEditHideFlds="ID"
usr1.dbFilterHideFlds="ID"
usr1.dbMode="Both"
usr1.dbNavigationItem="next,prev,add,update&#3 4;
usr1.dbSQL="SELECT ID,t_date,t_decimal,t_money FROM test"
usr1.ASPdb
If I try to update date (or money or decimal) value, it says:
Error # (Edit_Execute_SQL) = 80040E14(-2147217900)
Description = [Microsoft][ODBC SQL Server Driver][SQL Server]Line 1: Incorrect syntax near '.21'. ((21.07.01 is the value))
Source = Microsoft OLE DB Provider for ODBC Drivers
SQL State = 37000
Native Error = 170

Same thing happened by decimal values using ",". If I use ".", then it says "Error: Incorrect or unsupported data type - [t_money = 2.5]".
Estonian dateformat is: 21.07.2001 20:00 (dd.mm.yy)
and decimal/currency: 13,50 (US: 13.50)
(Seems like German)

So was this problem in old version (ver 1.xx or something). I tryed to set server language/locale/etc to "English", but nothing happened. MSSQL was installed after I set regional settings to "estonian".
If I write data directly to MSSQL Enterprise Manager grid, it accepts both dd.mm.yy and mm.dd.yy formats. mm.dd.yy autoconverts to dd.mm.yy if mm > 12. In numbers and currency (on the grid) I can use comma, not dot.
I tested with different regional settings, but there was no difference.
About 10.06.2001 I updated all the components (NT4, IIS4, SQL7, ADO, MDAC etc) to Microsoft latest.

regards,

Aivar

[Frank Kwong]

try to use dbOptions="LocalDecimal=","

FK

------------
Aivar Otsing at 7/2/01 12:19:47 PM

I have problem with currency and date transformation if I use add/edit/update.
I use MSSQL7 and NT4.0 server. SQL default language is "English" and NT4 locale, regional settings etc are "Estonian".
The testcode is here:
SET usr1=Server.CreateObject("ASP.DB&#34
usr1.dbUnit=40
usr1.dbDAT="testserver,testbase,testuser,testp wd"
usr1.dbSelectFrom=True
usr1.dbEditParams="TableName=test,BookMarkFlds =ID,RecordScope=Single"
usr1.dbEditHideFlds="ID"
usr1.dbFilterHideFlds="ID"
usr1.dbMode="Both"
usr1.dbNavigationItem="next,prev,add,update&#3 4;
usr1.dbSQL="SELECT ID,t_date,t_decimal,t_money FROM test"
usr1.ASPdb
If I try to update date (or money or decimal) value, it says:
Error # (Edit_Execute_SQL) = 80040E14(-2147217900)
Description = [Microsoft][ODBC SQL Server Driver][SQL Server]Line 1: Incorrect syntax near '.21'. ((21.07.01 is the value))
Source = Microsoft OLE DB Provider for ODBC Drivers
SQL State = 37000
Native Error = 170

Same thing happened by decimal values using ",". If I use ".", then it says "Error: Incorrect or unsupported data type - [t_money = 2.5]".
Estonian dateformat is: 21.07.2001 20:00 (dd.mm.yy)
and decimal/currency: 13,50 (US: 13.50)
(Seems like German)

So was this problem in old version (ver 1.xx or something). I tryed to set server language/locale/etc to "English", but nothing happened. MSSQL was installed after I set regional settings to "estonian".
If I write data directly to MSSQL Enterprise Manager grid, it accepts both dd.mm.yy and mm.dd.yy formats. mm.dd.yy autoconverts to dd.mm.yy if mm > 12. In numbers and currency (on the grid) I can use comma, not dot.
I tested with different regional settings, but there was no difference.
About 10.06.2001 I updated all the components (NT4, IIS4, SQL7, ADO, MDAC etc) to Microsoft latest.

regards,

Aivar

[Frank Kwong]

if you still have date input problem -

1. set to you normal locale
2. Tell me what you input in the date box
3. set dbDebug to dump the EditSQL an/or filteSQL. Tell me what it is
4. If error tell me what should the correct SQL be in correspond to the input.

FK


------------
Aivar Otsing at 7/2/01 12:19:47 PM

I have problem with currency and date transformation if I use add/edit/update.
I use MSSQL7 and NT4.0 server. SQL default language is "English" and NT4 locale, regional settings etc are "Estonian".
The testcode is here:
SET usr1=Server.CreateObject("ASP.DB&#34
usr1.dbUnit=40
usr1.dbDAT="testserver,testbase,testuser,testp wd"
usr1.dbSelectFrom=True
usr1.dbEditParams="TableName=test,BookMarkFlds =ID,RecordScope=Single"
usr1.dbEditHideFlds="ID"
usr1.dbFilterHideFlds="ID"
usr1.dbMode="Both"
usr1.dbNavigationItem="next,prev,add,update&#3 4;
usr1.dbSQL="SELECT ID,t_date,t_decimal,t_money FROM test"
usr1.ASPdb
If I try to update date (or money or decimal) value, it says:
Error # (Edit_Execute_SQL) = 80040E14(-2147217900)
Description = [Microsoft][ODBC SQL Server Driver][SQL Server]Line 1: Incorrect syntax near '.21'. ((21.07.01 is the value))
Source = Microsoft OLE DB Provider for ODBC Drivers
SQL State = 37000
Native Error = 170

Same thing happened by decimal values using ",". If I use ".", then it says "Error: Incorrect or unsupported data type - [t_money = 2.5]".
Estonian dateformat is: 21.07.2001 20:00 (dd.mm.yy)
and decimal/currency: 13,50 (US: 13.50)
(Seems like German)

So was this problem in old version (ver 1.xx or something). I tryed to set server language/locale/etc to "English", but nothing happened. MSSQL was installed after I set regional settings to "estonian".
If I write data directly to MSSQL Enterprise Manager grid, it accepts both dd.mm.yy and mm.dd.yy formats. mm.dd.yy autoconverts to dd.mm.yy if mm > 12. In numbers and currency (on the grid) I can use comma, not dot.
I tested with different regional settings, but there was no difference.
About 10.06.2001 I updated all the components (NT4, IIS4, SQL7, ADO, MDAC etc) to Microsoft latest.

regards,

Aivar

[Aivar Otsing]

Testfile: http://www.mailer.ee/test.asp, dbDebug="101" turned on.
NT4.0SP6, IIS4, MSSQL7.0SP3
1. To normal locale "Estonian" (as installed, client and server too)
2. Date input "01.01.2001"
3. SQL error:
Error # (Edit_Execute_SQL) = 80040E14(-2147217900)
Description = [Microsoft][ODBC SQL Server Driver][SQL Server]Line 1: Incorrect syntax near '.01'.
Source = Microsoft OLE DB Provider for ODBC Drivers
SQL State = 37000
Native Error = 170
3. dbDebug:
Edit SQL=INSERT INTO test (t_number,t_date) VALUES (111 ,#01.01.2001 00:00:00#)
IF I input "01/01/2001", it gives me exactly SAME error...
Edit SQL=INSERT INTO test (t_number,t_date) VALUES (111 ,#01.01.2001 00:00:00#)
Like "/" date separators are converted to "."?
4. In my eyes, as shown in dbDebug output, this IS the correct SQL...

I tested with different locale and regional settings. I got exactly same errors.
I have resources to build another system. Should I try to install Win2000 server with mssql2000 language/locale/etc set to US English and then try to connect from client with different locale settings? If it works I can set locale and regional to Estonian...
Anyway, I try to make new, US English based system for test.
It seems like MSSQL7 has some translation troubles, if installed to NT4 server with non-English locale/regional settings.
I let you know the result(s).

Aivar

------------
Frank Kwong at 7/2/01 12:50:17 PM

if you still have date input problem -

1. set to you normal locale
2. Tell me what you input in the date box
3. set dbDebug to dump the EditSQL an/or filteSQL. Tell me what it is
4. If error tell me what should the correct SQL be in correspond to the input.

FK


------------
Aivar Otsing at 7/2/01 12:19:47 PM

I have problem with currency and date transformation if I use add/edit/update.
I use MSSQL7 and NT4.0 server. SQL default language is "English" and NT4 locale, regional settings etc are "Estonian".
The testcode is here:
SET usr1=Server.CreateObject("ASP.DB&#34
usr1.dbUnit=40
usr1.dbDAT="testserver,testbase,testuser,testp wd"
usr1.dbSelectFrom=True
usr1.dbEditParams="TableName=test,BookMarkFlds =ID,RecordScope=Single"
usr1.dbEditHideFlds="ID"
usr1.dbFilterHideFlds="ID"
usr1.dbMode="Both"
usr1.dbNavigationItem="next,prev,add,update&#3 4;
usr1.dbSQL="SELECT ID,t_date,t_decimal,t_money FROM test"
usr1.ASPdb
If I try to update date (or money or decimal) value, it says:
Error # (Edit_Execute_SQL) = 80040E14(-2147217900)
Description = [Microsoft][ODBC SQL Server Driver][SQL Server]Line 1: Incorrect syntax near '.21'. ((21.07.01 is the value))
Source = Microsoft OLE DB Provider for ODBC Drivers
SQL State = 37000
Native Error = 170

Same thing happened by decimal values using ",". If I use ".", then it says "Error: Incorrect or unsupported data type - [t_money = 2.5]".
Estonian dateformat is: 21.07.2001 20:00 (dd.mm.yy)
and decimal/currency: 13,50 (US: 13.50)
(Seems like German)

So was this problem in old version (ver 1.xx or something). I tryed to set server language/locale/etc to "English", but nothing happened. MSSQL was installed after I set regional settings to "estonian".
If I write data directly to MSSQL Enterprise Manager grid, it accepts both dd.mm.yy and mm.dd.yy formats. mm.dd.yy autoconverts to dd.mm.yy if mm > 12. In numbers and currency (on the grid) I can use comma, not dot.
I tested with different regional settings, but there was no difference.
About 10.06.2001 I updated all the components (NT4, IIS4, SQL7, ADO, MDAC etc) to Microsoft latest.

regards,

Aivar

[Aivar Otsing]

If I test query with Query Analyzer, then:
"update test set t_date='10.02.2001',t_money=1.2 where ID=1" and
"update test set t_date='10/02/2001',t_money=1.2 where ID=1"
worked very well, but:
"update test set t_date=#10.02.2001#,t_money=1.2 where ID=1"
gives error : "Incorrect syntax near '.02'" and
"update test set t_date=#10/02/2001#,t_money=1.2 where ID=1"
gives error : "Incorrect syntax near '#'"
The field t_date is standard "datetime". System the same as described before and regional settings/locales tested are: US English, UK English, Estonian.
Seems like SQL don't like the datetime separators "#"?

Aivar
PS: I think I found the key:
ASPdb gives SQL clause not for MS-SQL ('[MM/DD/YY HH:MM:SS]&#39, but for Access (#[MM/DD/YY HH:MM:SS]#). Testfile works if I add additional options:
usr1.dbOptions="(DateFormat='[MM/DD/YY HH:MM:SS]',LocalDecimal=,"
Why? I use dbDAT, this must select MS-SQL as target database.

And the "," as decimal separator doesn't work with any local/regional settings:
I input "1,2" and dbDebug outputs "Edit SQL=UPDATE test SET t_number = 1 , t_date = Null, t_money = 1,2 WHERE ID = 1" with error:
"Error # (Edit_Execute_SQL) = 80040E14(-2147217900)
Description = [Microsoft][ODBC SQL Server Driver][SQL Server]Line 1: Incorrect syntax near '2'.
Source = Microsoft OLE DB Provider for ODBC Drivers
SQL State = 37000
Native Error = 170"
There must be translation: "... t_money=1.2 ..." if "LocalDecimal" is set to ",".
If I input "1.2" for field t_money (datatype as money), it says just "Error: Incorrect or unsupported data type - [t_money = 1.2]" nothing more.
Before this I just updated MDAC from 2.6RTM to 2.6SP1 and JET 4.0 SP1 (?) to JET 4.0 SP3.
OK. The comma/dot problem I test with new, completely US English based system later today.

Thanx for reading...

------------
Aivar Otsing at 7/4/01 7:39:13 AM

Testfile: http://www.mailer.ee/test.asp, dbDebug="101" turned on.
NT4.0SP6, IIS4, MSSQL7.0SP3
1. To normal locale "Estonian" (as installed, client and server too)
2. Date input "01.01.2001"
3. SQL error:
Error # (Edit_Execute_SQL) = 80040E14(-2147217900)
Description = [Microsoft][ODBC SQL Server Driver][SQL Server]Line 1: Incorrect syntax near '.01'.
Source = Microsoft OLE DB Provider for ODBC Drivers
SQL State = 37000
Native Error = 170
3. dbDebug:
Edit SQL=INSERT INTO test (t_number,t_date) VALUES (111 ,#01.01.2001 00:00:00#)
IF I input "01/01/2001", it gives me exactly SAME error...
Edit SQL=INSERT INTO test (t_number,t_date) VALUES (111 ,#01.01.2001 00:00:00#)
Like "/" date separators are converted to "."?
4. In my eyes, as shown in dbDebug output, this IS the correct SQL...

I tested with different locale and regional settings. I got exactly same errors.
I have resources to build another system. Should I try to install Win2000 server with mssql2000 language/locale/etc set to US English and then try to connect from client with different locale settings? If it works I can set locale and regional to Estonian...
Anyway, I try to make new, US English based system for test.
It seems like MSSQL7 has some translation troubles, if installed to NT4 server with non-English locale/regional settings.
I let you know the result(s).

Aivar

------------
Frank Kwong at 7/2/01 12:50:17 PM

if you still have date input problem -

1. set to you normal locale
2. Tell me what you input in the date box
3. set dbDebug to dump the EditSQL an/or filteSQL. Tell me what it is
4. If error tell me what should the correct SQL be in correspond to the input.

FK


------------
Aivar Otsing at 7/2/01 12:19:47 PM

I have problem with currency and date transformation if I use add/edit/update.
I use MSSQL7 and NT4.0 server. SQL default language is "English" and NT4 locale, regional settings etc are "Estonian".
The testcode is here:
SET usr1=Server.CreateObject("ASP.DB&#34
usr1.dbUnit=40
usr1.dbDAT="testserver,testbase,testuser,testp wd"
usr1.dbSelectFrom=True
usr1.dbEditParams="TableName=test,BookMarkFlds =ID,RecordScope=Single"
usr1.dbEditHideFlds="ID"
usr1.dbFilterHideFlds="ID"
usr1.dbMode="Both"
usr1.dbNavigationItem="next,prev,add,update&#3 4;
usr1.dbSQL="SELECT ID,t_date,t_decimal,t_money FROM test"
usr1.ASPdb
If I try to update date (or money or decimal) value, it says:
Error # (Edit_Execute_SQL) = 80040E14(-2147217900)
Description = [Microsoft][ODBC SQL Server Driver][SQL Server]Line 1: Incorrect syntax near '.21'. ((21.07.01 is the value))
Source = Microsoft OLE DB Provider for ODBC Drivers
SQL State = 37000
Native Error = 170

Same thing happened by decimal values using ",". If I use ".", then it says "Error: Incorrect or unsupported data type - [t_money = 2.5]".
Estonian dateformat is: 21.07.2001 20:00 (dd.mm.yy)
and decimal/currency: 13,50 (US: 13.50)
(Seems like German)

So was this problem in old version (ver 1.xx or something). I tryed to set server language/locale/etc to "English", but nothing happened. MSSQL was installed after I set regional settings to "estonian".
If I write data directly to MSSQL Enterprise Manager grid, it accepts both dd.mm.yy and mm.dd.yy formats. mm.dd.yy autoconverts to dd.mm.yy if mm > 12. In numbers and currency (on the grid) I can use comma, not dot.
I tested with different regional settings, but there was no difference.
About 10.06.2001 I updated all the components (NT4, IIS4, SQL7, ADO, MDAC etc) to Microsoft latest.

regards,

Aivar

[Donna]

did you remember to set dbDBTYPE="SQL" ?
that woudl be required for sql server.
Donna


------------
Aivar Otsing at 7/4/01 2:21:29 PM

If I test query with Query Analyzer, then:
"update test set t_date='10.02.2001',t_money=1.2 where ID=1" and
"update test set t_date='10/02/2001',t_money=1.2 where ID=1"
worked very well, but:
"update test set t_date=#10.02.2001#,t_money=1.2 where ID=1"
gives error : "Incorrect syntax near '.02'" and
"update test set t_date=#10/02/2001#,t_money=1.2 where ID=1"
gives error : "Incorrect syntax near '#'"
The field t_date is standard "datetime". System the same as described before and regional settings/locales tested are: US English, UK English, Estonian.
Seems like SQL don't like the datetime separators "#"?

Aivar
PS: I think I found the key:
ASPdb gives SQL clause not for MS-SQL ('[MM/DD/YY HH:MM:SS]&#39, but for Access (#[MM/DD/YY HH:MM:SS]#). Testfile works if I add additional options:
usr1.dbOptions="(DateFormat='[MM/DD/YY HH:MM:SS]',LocalDecimal=,"
Why? I use dbDAT, this must select MS-SQL as target database.

And the "," as decimal separator doesn't work with any local/regional settings:
I input "1,2" and dbDebug outputs "Edit SQL=UPDATE test SET t_number = 1 , t_date = Null, t_money = 1,2 WHERE ID = 1" with error:
"Error # (Edit_Execute_SQL) = 80040E14(-2147217900)
Description = [Microsoft][ODBC SQL Server Driver][SQL Server]Line 1: Incorrect syntax near '2'.
Source = Microsoft OLE DB Provider for ODBC Drivers
SQL State = 37000
Native Error = 170"
There must be translation: "... t_money=1.2 ..." if "LocalDecimal" is set to ",".
If I input "1.2" for field t_money (datatype as money), it says just "Error: Incorrect or unsupported data type - [t_money = 1.2]" nothing more.
Before this I just updated MDAC from 2.6RTM to 2.6SP1 and JET 4.0 SP1 (?) to JET 4.0 SP3.
OK. The comma/dot problem I test with new, completely US English based system later today.

Thanx for reading...

------------
Aivar Otsing at 7/4/01 7:39:13 AM

Testfile: http://www.mailer.ee/test.asp, dbDebug="101" turned on.
NT4.0SP6, IIS4, MSSQL7.0SP3
1. To normal locale "Estonian" (as installed, client and server too)
2. Date input "01.01.2001"
3. SQL error:
Error # (Edit_Execute_SQL) = 80040E14(-2147217900)
Description = [Microsoft][ODBC SQL Server Driver][SQL Server]Line 1: Incorrect syntax near '.01'.
Source = Microsoft OLE DB Provider for ODBC Drivers
SQL State = 37000
Native Error = 170
3. dbDebug:
Edit SQL=INSERT INTO test (t_number,t_date) VALUES (111 ,#01.01.2001 00:00:00#)
IF I input "01/01/2001", it gives me exactly SAME error...
Edit SQL=INSERT INTO test (t_number,t_date) VALUES (111 ,#01.01.2001 00:00:00#)
Like "/" date separators are converted to "."?
4. In my eyes, as shown in dbDebug output, this IS the correct SQL...

I tested with different locale and regional settings. I got exactly same errors.
I have resources to build another system. Should I try to install Win2000 server with mssql2000 language/locale/etc set to US English and then try to connect from client with different locale settings? If it works I can set locale and regional to Estonian...
Anyway, I try to make new, US English based system for test.
It seems like MSSQL7 has some translation troubles, if installed to NT4 server with non-English locale/regional settings.
I let you know the result(s).

Aivar

------------
Frank Kwong at 7/2/01 12:50:17 PM

if you still have date input problem -

1. set to you normal locale
2. Tell me what you input in the date box
3. set dbDebug to dump the EditSQL an/or filteSQL. Tell me what it is
4. If error tell me what should the correct SQL be in correspond to the input.

FK


------------
Aivar Otsing at 7/2/01 12:19:47 PM

I have problem with currency and date transformation if I use add/edit/update.
I use MSSQL7 and NT4.0 server. SQL default language is "English" and NT4 locale, regional settings etc are "Estonian".
The testcode is here:
SET usr1=Server.CreateObject("ASP.DB&#34
usr1.dbUnit=40
usr1.dbDAT="testserver,testbase,testuser,testp wd"
usr1.dbSelectFrom=True
usr1.dbEditParams="TableName=test,BookMarkFlds =ID,RecordScope=Single"
usr1.dbEditHideFlds="ID"
usr1.dbFilterHideFlds="ID"
usr1.dbMode="Both"
usr1.dbNavigationItem="next,prev,add,update&#3 4;
usr1.dbSQL="SELECT ID,t_date,t_decimal,t_money FROM test"
usr1.ASPdb
If I try to update date (or money or decimal) value, it says:
Error # (Edit_Execute_SQL) = 80040E14(-2147217900)
Description = [Microsoft][ODBC SQL Server Driver][SQL Server]Line 1: Incorrect syntax near '.21'. ((21.07.01 is the value))
Source = Microsoft OLE DB Provider for ODBC Drivers
SQL State = 37000
Native Error = 170

Same thing happened by decimal values using ",". If I use ".", then it says "Error: Incorrect or unsupported data type - [t_money = 2.5]".
Estonian dateformat is: 21.07.2001 20:00 (dd.mm.yy)
and decimal/currency: 13,50 (US: 13.50)
(Seems like German)

So was this problem in old version (ver 1.xx or something). I tryed to set server language/locale/etc to "English", but nothing happened. MSSQL was installed after I set regional settings to "estonian".
If I write data directly to MSSQL Enterprise Manager grid, it accepts both dd.mm.yy and mm.dd.yy formats. mm.dd.yy autoconverts to dd.mm.yy if mm > 12. In numbers and currency (on the grid) I can use comma, not dot.
I tested with different regional settings, but there was no difference.
About 10.06.2001 I updated all the components (NT4, IIS4, SQL7, ADO, MDAC etc) to Microsoft latest.

regards,

Aivar

[Frank Kwong]

Donna is right about the dbSQL

...go slower

- set X.dbSQL="SQL"
- Set the localdecimal

The decimal problem has gone through a long debug with euro and China users and it should function.

FK



------------
Donna at 7/4/01 9:42:37 PM

did you remember to set dbDBTYPE="SQL" ?
that woudl be required for sql server.
Donna


------------
Aivar Otsing at 7/4/01 2:21:29 PM

If I test query with Query Analyzer, then:
"update test set t_date='10.02.2001',t_money=1.2 where ID=1" and
"update test set t_date='10/02/2001',t_money=1.2 where ID=1"
worked very well, but:
"update test set t_date=#10.02.2001#,t_money=1.2 where ID=1"
gives error : "Incorrect syntax near '.02'" and
"update test set t_date=#10/02/2001#,t_money=1.2 where ID=1"
gives error : "Incorrect syntax near '#'"
The field t_date is standard "datetime". System the same as described before and regional settings/locales tested are: US English, UK English, Estonian.
Seems like SQL don't like the datetime separators "#"?

Aivar
PS: I think I found the key:
ASPdb gives SQL clause not for MS-SQL ('[MM/DD/YY HH:MM:SS]&#39, but for Access (#[MM/DD/YY HH:MM:SS]#). Testfile works if I add additional options:
usr1.dbOptions="(DateFormat='[MM/DD/YY HH:MM:SS]',LocalDecimal=,"
Why? I use dbDAT, this must select MS-SQL as target database.

And the "," as decimal separator doesn't work with any local/regional settings:
I input "1,2" and dbDebug outputs "Edit SQL=UPDATE test SET t_number = 1 , t_date = Null, t_money = 1,2 WHERE ID = 1" with error:
"Error # (Edit_Execute_SQL) = 80040E14(-2147217900)
Description = [Microsoft][ODBC SQL Server Driver][SQL Server]Line 1: Incorrect syntax near '2'.
Source = Microsoft OLE DB Provider for ODBC Drivers
SQL State = 37000
Native Error = 170"
There must be translation: "... t_money=1.2 ..." if "LocalDecimal" is set to ",".
If I input "1.2" for field t_money (datatype as money), it says just "Error: Incorrect or unsupported data type - [t_money = 1.2]" nothing more.
Before this I just updated MDAC from 2.6RTM to 2.6SP1 and JET 4.0 SP1 (?) to JET 4.0 SP3.
OK. The comma/dot problem I test with new, completely US English based system later today.

Thanx for reading...

------------
Aivar Otsing at 7/4/01 7:39:13 AM

Testfile: http://www.mailer.ee/test.asp, dbDebug="101" turned on.
NT4.0SP6, IIS4, MSSQL7.0SP3
1. To normal locale "Estonian" (as installed, client and server too)
2. Date input "01.01.2001"
3. SQL error:
Error # (Edit_Execute_SQL) = 80040E14(-2147217900)
Description = [Microsoft][ODBC SQL Server Driver][SQL Server]Line 1: Incorrect syntax near '.01'.
Source = Microsoft OLE DB Provider for ODBC Drivers
SQL State = 37000
Native Error = 170
3. dbDebug:
Edit SQL=INSERT INTO test (t_number,t_date) VALUES (111 ,#01.01.2001 00:00:00#)
IF I input "01/01/2001", it gives me exactly SAME error...
Edit SQL=INSERT INTO test (t_number,t_date) VALUES (111 ,#01.01.2001 00:00:00#)
Like "/" date separators are converted to "."?
4. In my eyes, as shown in dbDebug output, this IS the correct SQL...

I tested with different locale and regional settings. I got exactly same errors.
I have resources to build another system. Should I try to install Win2000 server with mssql2000 language/locale/etc set to US English and then try to connect from client with different locale settings? If it works I can set locale and regional to Estonian...
Anyway, I try to make new, US English based system for test.
It seems like MSSQL7 has some translation troubles, if installed to NT4 server with non-English locale/regional settings.
I let you know the result(s).

Aivar

------------
Frank Kwong at 7/2/01 12:50:17 PM

if you still have date input problem -

1. set to you normal locale
2. Tell me what you input in the date box
3. set dbDebug to dump the EditSQL an/or filteSQL. Tell me what it is
4. If error tell me what should the correct SQL be in correspond to the input.

FK


------------
Aivar Otsing at 7/2/01 12:19:47 PM

I have problem with currency and date transformation if I use add/edit/update.
I use MSSQL7 and NT4.0 server. SQL default language is "English" and NT4 locale, regional settings etc are "Estonian".
The testcode is here:
SET usr1=Server.CreateObject("ASP.DB&#34
usr1.dbUnit=40
usr1.dbDAT="testserver,testbase,testuser,testp wd"
usr1.dbSelectFrom=True
usr1.dbEditParams="TableName=test,BookMarkFlds =ID,RecordScope=Single"
usr1.dbEditHideFlds="ID"
usr1.dbFilterHideFlds="ID"
usr1.dbMode="Both"
usr1.dbNavigationItem="next,prev,add,update&#3 4;
usr1.dbSQL="SELECT ID,t_date,t_decimal,t_money FROM test"
usr1.ASPdb
If I try to update date (or money or decimal) value, it says:
Error # (Edit_Execute_SQL) = 80040E14(-2147217900)
Description = [Microsoft][ODBC SQL Server Driver][SQL Server]Line 1: Incorrect syntax near '.21'. ((21.07.01 is the value))
Source = Microsoft OLE DB Provider for ODBC Drivers
SQL State = 37000
Native Error = 170

Same thing happened by decimal values using ",". If I use ".", then it says "Error: Incorrect or unsupported data type - [t_money = 2.5]".
Estonian dateformat is: 21.07.2001 20:00 (dd.mm.yy)
and decimal/currency: 13,50 (US: 13.50)
(Seems like German)

So was this problem in old version (ver 1.xx or something). I tryed to set server language/locale/etc to "English", but nothing happened. MSSQL was installed after I set regional settings to "estonian".
If I write data directly to MSSQL Enterprise Manager grid, it accepts both dd.mm.yy and mm.dd.yy formats. mm.dd.yy autoconverts to dd.mm.yy if mm > 12. In numbers and currency (on the grid) I can use comma, not dot.
I tested with different regional settings, but there was no difference.
About 10.06.2001 I updated all the components (NT4, IIS4, SQL7, ADO, MDAC etc) to Microsoft latest.

regards,

Aivar