Sybase Date Format problem when editing
I am receiving the following error when applying updates in APS-DB
to a table with a field of type timestamp on a Sybase 6.0.2.2188 database.
I am using the EP eval.
Error # = 80004005(-2147467259)
Description = [Sybase][ODBC Driver][Adaptive Server Anywhere]
Restricted data type attribute violation: cannot convert
08/18/1999 10:55:08 AM to a timestamp
Source = Microsoft OLE DB Provider for ODBC Drivers
SQL State = 07006
Native Error = -157
MY CODE IS AS FOLLOWS:
<%@ Language=VBScript %>
<% Response.Buffer=True %>
<HTML>
<HEAD>
<BODY>
<CENTER>
<%
Set MyDb = Server.CreateObject("ASPdb.EP")
MyDb.dbDBType="SYBASE"
MyDb.dbDSN="DSN=etrack6; UID=dba; PWD=sql"
MyDb.dbColor = "4,auto"
MyDb.dbMode = "form"
s="Select * from CSC.UnitDescr where UnitIdx="&Session("UnitIdx")
MyDb.dbSQL = s
MyDb.dbImageDir="images/"
MyDb.dbNavigationIcon = "std"
MyDb.dbNavigation="Top"
MyDb.dbNavigationItem="formcol,update"
Mydb.dbEditParams = "TableName=CSC.UnitDescr, BookMarkFlds=UnitIdx, TableTag=Border=2, RecordScope=single, CriteriaSize=4x25"
Mydb.dbEditHideFlds="UnitIdx"
MyDb.ASPdbEP
%>
</CENTER>
</BODY>
</HTML>
The problem does not occur if, before I push the Apply Updates button,
I change the data value from 08/18/1999 10:55:08 AM to 1999/08/18 10:55:08 AM.
So I thought I might be able to use dbMagicCell to change the data format. It turns
out that dbMagicCell changes the format for the FORM, but not for the edit box; the
same error still occurs.
I hope I am not the only Sybase user using ASP-DB! Thanks for any help.
Sybase Date Format problem when editing (reply)
Gary,
We have some but not a whole lot of Sybase users. Haven't heard much problems from them. But you would not be left behind just becasue you are using Sybase even though we have no Sybase in our lab.
OK, let see -
1. What type is your timestamp field - Is it 135. Find out by usng the datatype utility.
2.Can you write to your timestamp field ?
3. Use your Enterprise manager or equivalent to add a timestamp field and then show me the format of your date string upon a success ADD/UPDATE and also the one that fails (FROM ASPDB). If you need a Magic Conversion in between then I'll provide you with one....
Frank Kwong
------------
Gary Chisholm at 9/27/99 2:01:40 PM
I am receiving the following error when applying updates in APS-DB
to a table with a field of type timestamp on a Sybase 6.0.2.2188 database.
I am using the EP eval.
Error # = 80004005(-2147467259)
Description = [Sybase][ODBC Driver][Adaptive Server Anywhere]
Restricted data type attribute violation: cannot convert
08/18/1999 10:55:08 AM to a timestamp
Source = Microsoft OLE DB Provider for ODBC Drivers
SQL State = 07006
Native Error = -157
MY CODE IS AS FOLLOWS:
<%@ Language=VBScript %>
<% Response.Buffer=True %>
<HTML>
<HEAD>
<BODY>
<CENTER>
<%
Set MyDb = Server.CreateObject("ASPdb.EP")
MyDb.dbDBType="SYBASE"
MyDb.dbDSN="DSN=etrack6; UID=dba; PWD=sql"
MyDb.dbColor = "4,auto"
MyDb.dbMode = "form"
s="Select * from CSC.UnitDescr where UnitIdx="&Session("UnitIdx")
MyDb.dbSQL = s
MyDb.dbImageDir="images/"
MyDb.dbNavigationIcon = "std"
MyDb.dbNavigation="Top"
MyDb.dbNavigationItem="formcol,update"
Mydb.dbEditParams = "TableName=CSC.UnitDescr, BookMarkFlds=UnitIdx, TableTag=Border=2, RecordScope=single, CriteriaSize=4x25"
Mydb.dbEditHideFlds="UnitIdx"
MyDb.ASPdbEP
%>
</CENTER>
</BODY>
</HTML>
The problem does not occur if, before I push the Apply Updates button,
I change the data value from 08/18/1999 10:55:08 AM to 1999/08/18 10:55:08 AM.
So I thought I might be able to use dbMagicCell to change the data format. It turns
out that dbMagicCell changes the format for the FORM, but not for the edit box; the
same error still occurs.
I hope I am not the only Sybase user using ASP-DB! Thanks for any help.
Sybase Date Format problem when editing (reply)
1. What type is your timestamp field - Is it 135.
Find out by usng the datatype utility.
WHAT IS THE DATATYPE UTILITY? WHERE DO I GET IT?
ANYWAY, I GET THIS SAME ERROR USING 'DATE' FIELD TYPE
ALSO, I DID SOME TESTING; THIS ERROR IS AN ODBC PROBLEM.
UNFORTUNATELY, I KNOW OF NO ODBC SIDE FIX, SO IT'S UP TO
ASP-DB TO PICK UP WHERE ODBC LEFT OFF!
2.Can you write to your timestamp field ?
YES. SO LONG AS I SPECIFY THE DATE PART AS 'YYYY/MM/DD'
3. Use your Enterprise manager or equivalent to add
a timestamp field and then show me the format of your
date string upon a success ADD/UPDATE and also the
one that fails (FROM ASPDB). If you need a Magic Conversion
in between then I'll provide you with one....
'MM/DD/YYYY' FAILS IN ASP-DB
'MM/DD/YYYY' FAILS IN ODBC
'YYYY/MM/DD' WORKS IN ASP-DB
'YYYY/MM/DD' WORKS IN ODBC
SYBASE CENTRAL (EQUIVALENT OF ENTERPRISE MANAGER) DISPLAYS IN THE
FOLLOWING FORMAT AFTER A SUCCESSFUL ADD/UPDATE : 'MM/DD/YYYY'
Sybase Date Format problem when editing (reply)
Hi Gary,
I can help you with the first one - you can find DataTypes.zip at:
http://www.aspdb.com/DataTypes.zip
Frank will probably respond to the other questions.
Thanks,
John
------------
at 9/27/99 4:42:17 PM
1. What type is your timestamp field - Is it 135.
Find out by usng the datatype utility.
WHAT IS THE DATATYPE UTILITY? WHERE DO I GET IT?
ANYWAY, I GET THIS SAME ERROR USING 'DATE' FIELD TYPE
ALSO, I DID SOME TESTING; THIS ERROR IS AN ODBC PROBLEM.
UNFORTUNATELY, I KNOW OF NO ODBC SIDE FIX, SO IT'S UP TO
ASP-DB TO PICK UP WHERE ODBC LEFT OFF!
2.Can you write to your timestamp field ?
YES. SO LONG AS I SPECIFY THE DATE PART AS 'YYYY/MM/DD'
3. Use your Enterprise manager or equivalent to add
a timestamp field and then show me the format of your
date string upon a success ADD/UPDATE and also the
one that fails (FROM ASPDB). If you need a Magic Conversion
in between then I'll provide you with one....
'MM/DD/YYYY' FAILS IN ASP-DB
'MM/DD/YYYY' FAILS IN ODBC
'YYYY/MM/DD' WORKS IN ASP-DB
'YYYY/MM/DD' WORKS IN ODBC
SYBASE CENTRAL (EQUIVALENT OF ENTERPRISE MANAGER) DISPLAYS IN THE
FOLLOWING FORMAT AFTER A SUCCESSFUL ADD/UPDATE : 'MM/DD/YYYY'
Sybase Date Format problem when editing (reply)
Gary,
Look at the following standard Update statement ->
UPDATE orders SET EmployeeID = 5 , OrderDate = '1999/01/01' WHERE OrderID = 10248
See I purposely put in 1999/01/01 and it works in MS-SQL. I can also put in 1/1/99 and it'll also work. You are saying that in Sybase, you MUST put in 1999/08/18 in order to work for a date type and when you put in 8/8/99 then it'll fail. Is this correct ?
Frank
------------
Gary Chisholm at 9/27/99 2:01:40 PM
I am receiving the following error when applying updates in APS-DB
to a table with a field of type timestamp on a Sybase 6.0.2.2188 database.
I am using the EP eval.
Error # = 80004005(-2147467259)
Description = [Sybase][ODBC Driver][Adaptive Server Anywhere]
Restricted data type attribute violation: cannot convert
08/18/1999 10:55:08 AM to a timestamp
Source = Microsoft OLE DB Provider for ODBC Drivers
SQL State = 07006
Native Error = -157
MY CODE IS AS FOLLOWS:
<%@ Language=VBScript %>
<% Response.Buffer=True %>
<HTML>
<HEAD>
<BODY>
<CENTER>
<%
Set MyDb = Server.CreateObject("ASPdb.EP")
MyDb.dbDBType="SYBASE"
MyDb.dbDSN="DSN=etrack6; UID=dba; PWD=sql"
MyDb.dbColor = "4,auto"
MyDb.dbMode = "form"
s="Select * from CSC.UnitDescr where UnitIdx="&Session("UnitIdx")
MyDb.dbSQL = s
MyDb.dbImageDir="images/"
MyDb.dbNavigationIcon = "std"
MyDb.dbNavigation="Top"
MyDb.dbNavigationItem="formcol,update"
Mydb.dbEditParams = "TableName=CSC.UnitDescr, BookMarkFlds=UnitIdx, TableTag=Border=2, RecordScope=single, CriteriaSize=4x25"
Mydb.dbEditHideFlds="UnitIdx"
MyDb.ASPdbEP
%>
</CENTER>
</BODY>
</HTML>
The problem does not occur if, before I push the Apply Updates button,
I change the data value from 08/18/1999 10:55:08 AM to 1999/08/18 10:55:08 AM.
So I thought I might be able to use dbMagicCell to change the data format. It turns
out that dbMagicCell changes the format for the FORM, but not for the edit box; the
same error still occurs.
I hope I am not the only Sybase user using ASP-DB! Thanks for any help.
Sybase Date Format problem when editing (reply)
Your analysis is impeccable! : )
P.S. My company is VAR for Sybase. Perhaps we could help you acquire a Evaluation version of Sybase?
------------
Frank at 9/27/99 6:30:14 PM
Gary,
Look at the following standard Update statement ->
UPDATE orders SET EmployeeID = 5 , OrderDate = '1999/01/01' WHERE OrderID = 10248
See I purposely put in 1999/01/01 and it works in MS-SQL. I can also put in 1/1/99 and it'll also work. You are saying that in Sybase, you MUST put in 1999/08/18 in order to work for a date type and when you put in 8/8/99 then it'll fail. Is this correct ?
Frank
------------
Gary Chisholm at 9/27/99 2:01:40 PM
I am receiving the following error when applying updates in APS-DB
to a table with a field of type timestamp on a Sybase 6.0.2.2188 database.
I am using the EP eval.
Error # = 80004005(-2147467259)
Description = [Sybase][ODBC Driver][Adaptive Server Anywhere]
Restricted data type attribute violation: cannot convert
08/18/1999 10:55:08 AM to a timestamp
Source = Microsoft OLE DB Provider for ODBC Drivers
SQL State = 07006
Native Error = -157
MY CODE IS AS FOLLOWS:
<%@ Language=VBScript %>
<% Response.Buffer=True %>
<HTML>
<HEAD>
<BODY>
<CENTER>
<%
Set MyDb = Server.CreateObject("ASPdb.EP")
MyDb.dbDBType="SYBASE"
MyDb.dbDSN="DSN=etrack6; UID=dba; PWD=sql"
MyDb.dbColor = "4,auto"
MyDb.dbMode = "form"
s="Select * from CSC.UnitDescr where UnitIdx="&Session("UnitIdx")
MyDb.dbSQL = s
MyDb.dbImageDir="images/"
MyDb.dbNavigationIcon = "std"
MyDb.dbNavigation="Top"
MyDb.dbNavigationItem="formcol,update"
Mydb.dbEditParams = "TableName=CSC.UnitDescr, BookMarkFlds=UnitIdx, TableTag=Border=2, RecordScope=single, CriteriaSize=4x25"
Mydb.dbEditHideFlds="UnitIdx"
MyDb.ASPdbEP
%>
</CENTER>
</BODY>
</HTML>
The problem does not occur if, before I push the Apply Updates button,
I change the data value from 08/18/1999 10:55:08 AM to 1999/08/18 10:55:08 AM.
So I thought I might be able to use dbMagicCell to change the data format. It turns
out that dbMagicCell changes the format for the FORM, but not for the edit box; the
same error still occurs.
I hope I am not the only Sybase user using ASP-DB! Thanks for any help.
Sybase Date Format problem when editing (reply)
Gary,
How's the timestamp problem. Are we getting close to solve this mystery as it is unbelievable that Sybase do not support freeform date entries. After all MS-SQL is devrived from Sybase. Actually all others work (ACCESS and ORACLE) please update me with your findings. It is easy enough to porvide Date format control in the input boxes but this task is usually done by the DB.
As for getting an eval version of SYbase, that sounds good. I usually only go as far as the examples. If I can install teh DB in NT and use ASP to read the examples (Table, view and Stored Procedures) that comes with the package then I have a common ground to debug customer's problem. Also I would need an ODBC drive for Sybase.
BTW, if you know Sybase well. What is the syntax of the Count(*) fucntion in Sybase to return the record count of the recordset returned from the query ?
Frank Kwong
------------
at 9/27/99 7:37:57 PM
Your analysis is impeccable! : )
P.S. My company is VAR for Sybase. Perhaps we could help you acquire a Evaluation version of Sybase?
------------
Frank at 9/27/99 6:30:14 PM
Gary,
Look at the following standard Update statement ->
UPDATE orders SET EmployeeID = 5 , OrderDate = '1999/01/01' WHERE OrderID = 10248
See I purposely put in 1999/01/01 and it works in MS-SQL. I can also put in 1/1/99 and it'll also work. You are saying that in Sybase, you MUST put in 1999/08/18 in order to work for a date type and when you put in 8/8/99 then it'll fail. Is this correct ?
Frank
------------
Gary Chisholm at 9/27/99 2:01:40 PM
I am receiving the following error when applying updates in APS-DB
to a table with a field of type timestamp on a Sybase 6.0.2.2188 database.
I am using the EP eval.
Error # = 80004005(-2147467259)
Description = [Sybase][ODBC Driver][Adaptive Server Anywhere]
Restricted data type attribute violation: cannot convert
08/18/1999 10:55:08 AM to a timestamp
Source = Microsoft OLE DB Provider for ODBC Drivers
SQL State = 07006
Native Error = -157
MY CODE IS AS FOLLOWS:
<%@ Language=VBScript %>
<% Response.Buffer=True %>
<HTML>
<HEAD>
<BODY>
<CENTER>
<%
Set MyDb = Server.CreateObject("ASPdb.EP")
MyDb.dbDBType="SYBASE"
MyDb.dbDSN="DSN=etrack6; UID=dba; PWD=sql"
MyDb.dbColor = "4,auto"
MyDb.dbMode = "form"
s="Select * from CSC.UnitDescr where UnitIdx="&Session("UnitIdx")
MyDb.dbSQL = s
MyDb.dbImageDir="images/"
MyDb.dbNavigationIcon = "std"
MyDb.dbNavigation="Top"
MyDb.dbNavigationItem="formcol,update"
Mydb.dbEditParams = "TableName=CSC.UnitDescr, BookMarkFlds=UnitIdx, TableTag=Border=2, RecordScope=single, CriteriaSize=4x25"
Mydb.dbEditHideFlds="UnitIdx"
MyDb.ASPdbEP
%>
</CENTER>
</BODY>
</HTML>
The problem does not occur if, before I push the Apply Updates button,
I change the data value from 08/18/1999 10:55:08 AM to 1999/08/18 10:55:08 AM.
So I thought I might be able to use dbMagicCell to change the data format. It turns
out that dbMagicCell changes the format for the FORM, but not for the edit box; the
same error still occurs.
I hope I am not the only Sybase user using ASP-DB! Thanks for any help.
Sybase Date Format problem when editing (reply)
Gary,
Another user reported similar problem with ORACLE. Their format has to be DD-MM-YY. I tested that and I witness the problem. Since I have no Sybase, I would no really test that. Now I provided a solution by a new keyword in dbEditParama="EditDateFormat="YYYY/MM/DD" for you to get it going. Wait for the next patch release for that....
Frank
------------
at 9/27/99 4:42:17 PM
1. What type is your timestamp field - Is it 135.
Find out by usng the datatype utility.
WHAT IS THE DATATYPE UTILITY? WHERE DO I GET IT?
ANYWAY, I GET THIS SAME ERROR USING 'DATE' FIELD TYPE
ALSO, I DID SOME TESTING; THIS ERROR IS AN ODBC PROBLEM.
UNFORTUNATELY, I KNOW OF NO ODBC SIDE FIX, SO IT'S UP TO
ASP-DB TO PICK UP WHERE ODBC LEFT OFF!
2.Can you write to your timestamp field ?
YES. SO LONG AS I SPECIFY THE DATE PART AS 'YYYY/MM/DD'
3. Use your Enterprise manager or equivalent to add
a timestamp field and then show me the format of your
date string upon a success ADD/UPDATE and also the
one that fails (FROM ASPDB). If you need a Magic Conversion
in between then I'll provide you with one....
'MM/DD/YYYY' FAILS IN ASP-DB
'MM/DD/YYYY' FAILS IN ODBC
'YYYY/MM/DD' WORKS IN ASP-DB
'YYYY/MM/DD' WORKS IN ODBC
SYBASE CENTRAL (EQUIVALENT OF ENTERPRISE MANAGER) DISPLAYS IN THE
FOLLOWING FORMAT AFTER A SUCCESSFUL ADD/UPDATE : 'MM/DD/YYYY'
Sybase Date Format problem when editing (reply)
OH... once more thing. IS Sybase's date default = 'YYYY/MM/DD'. If so then I can make it Sybase default. That means id dbType="SYBASE" the the date format will be enforced unless specified !!
Frank
------------
at 9/27/99 4:42:17 PM
1. What type is your timestamp field - Is it 135.
Find out by usng the datatype utility.
WHAT IS THE DATATYPE UTILITY? WHERE DO I GET IT?
ANYWAY, I GET THIS SAME ERROR USING 'DATE' FIELD TYPE
ALSO, I DID SOME TESTING; THIS ERROR IS AN ODBC PROBLEM.
UNFORTUNATELY, I KNOW OF NO ODBC SIDE FIX, SO IT'S UP TO
ASP-DB TO PICK UP WHERE ODBC LEFT OFF!
2.Can you write to your timestamp field ?
YES. SO LONG AS I SPECIFY THE DATE PART AS 'YYYY/MM/DD'
3. Use your Enterprise manager or equivalent to add
a timestamp field and then show me the format of your
date string upon a success ADD/UPDATE and also the
one that fails (FROM ASPDB). If you need a Magic Conversion
in between then I'll provide you with one....
'MM/DD/YYYY' FAILS IN ASP-DB
'MM/DD/YYYY' FAILS IN ODBC
'YYYY/MM/DD' WORKS IN ASP-DB
'YYYY/MM/DD' WORKS IN ODBC
SYBASE CENTRAL (EQUIVALENT OF ENTERPRISE MANAGER) DISPLAYS IN THE
FOLLOWING FORMAT AFTER A SUCCESSFUL ADD/UPDATE : 'MM/DD/YYYY'
Sybase Date Format problem when editing (reply)
OH... once more thing. IS Sybase's date default = 'YYYY/MM/DD'. If so then I can make it Sybase default. That means id dbType="SYBASE" the the date format will be enforced unless specified !!
Frank
------------
at 9/27/99 4:42:17 PM
1. What type is your timestamp field - Is it 135.
Find out by usng the datatype utility.
WHAT IS THE DATATYPE UTILITY? WHERE DO I GET IT?
ANYWAY, I GET THIS SAME ERROR USING 'DATE' FIELD TYPE
ALSO, I DID SOME TESTING; THIS ERROR IS AN ODBC PROBLEM.
UNFORTUNATELY, I KNOW OF NO ODBC SIDE FIX, SO IT'S UP TO
ASP-DB TO PICK UP WHERE ODBC LEFT OFF!
2.Can you write to your timestamp field ?
YES. SO LONG AS I SPECIFY THE DATE PART AS 'YYYY/MM/DD'
3. Use your Enterprise manager or equivalent to add
a timestamp field and then show me the format of your
date string upon a success ADD/UPDATE and also the
one that fails (FROM ASPDB). If you need a Magic Conversion
in between then I'll provide you with one....
'MM/DD/YYYY' FAILS IN ASP-DB
'MM/DD/YYYY' FAILS IN ODBC
'YYYY/MM/DD' WORKS IN ASP-DB
'YYYY/MM/DD' WORKS IN ODBC
SYBASE CENTRAL (EQUIVALENT OF ENTERPRISE MANAGER) DISPLAYS IN THE
FOLLOWING FORMAT AFTER A SUCCESSFUL ADD/UPDATE : 'MM/DD/YYYY'
