Number Fields with ceros to the right truncated

Hi!

I'm working with ASP, ADO and Sybase via ODBC. I'm having troubles with numeric fields in my tables, the first and most critic is that every time I get a numeric value from my recordset the ceros are truncated. For exmaple, If I have 8,80,800, 800.00 all the values are passed as 8!! Here is an example of my code:

Dim conn
Dim rectel
Dim strsql

Set conn = Server.CreateObject("ADODB.Connection")
set rectel = Server.CreateObject("ADODB.Recordset")

conn.Open "MyDBSybase"

strsql="SELECT telephony.price" _
& " FROM telephony.dbo.call_type call_type"

rectel.Open strsql, conn

and when I try:

<%=rectel.Fields("price")%>

The resul is truncated.
furthermore if I try any kind of mathematical operation I have alway to use Cdbl(rectel.Fields("price"))  or it wont work.

And also in the operation the value is truncated!!!

Is there any configuration or something to avoid this (I already validated the regional settings in the server and the are right)?? and how can I check for the data type of the fields the recordset is giving me??

Thanks in advance!!
 
ecantuAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

fozyletCommented:
Tried changing it to currency type?
CCur (?)
0
ecantuAuthor Commented:
Where? inside the sql string? the problem is that the Recordset is delivering the data truncated so if I try any formatting after the recordset the data is already truncated
0
David H.H.LeeCommented:
ecantu ,
>>how can I check for the data type of the fields the recordset is giving me??
-You always can use TypeName() to see the result:
eg:
<%
 Response.Write(rectel.Fields("price"))
%>

-Try to use Val() build in function in this case before store into DB.
Eg:
 Val("8,80,800, 800.00")

0
Cloud Class® Course: Microsoft Office 2010

This course will introduce you to the interfaces and features of Microsoft Office 2010 Word, Excel, PowerPoint, Outlook, and Access. You will learn about the features that are shared between all products in the Office suite, as well as the new features that are product specific.

David H.H.LeeCommented:
ecantu ,
>>how can I check for the data type of the fields the recordset is giving me??
-You always can use TypeName() to see the result:
eg:
<%
Response.Write(TypeName(rectel.Fields("price")))
%>

-Try to use Val() build in function in this case before store into DB.
Eg:
Val("8,80,800, 800.00") <-----This is the value will store into DB
0
barryfandangoCommented:
ecantu,

The query should return whatever type was specified in the table design.  Open up the table in edit mode and see what type it is set to - perhaps it was accidentally entered as a string, hence the truncation.
0
ecantuAuthor Commented:
I already checked the data type in the DB, and it's numeric 18,2. I have no problems in storing data, when I store 8.00 or 800 they are stored correctly, the problem is when I make a query and try to use the values the recodrset returns 8 in both cases.

The strange thing is that I try the same SQL in Sybase SQL Advantage and works fine, and the same query using Microsoft Query and also works fine. So I'm thinking there is a problem with the ASP engine rather than the DB or the ODBC.

By the way I try the TypeName function over the field and returns "Field" instead of numeric, Double, long, integer or something like that.

response.Write (TypeName(rectel.Fields("price"))) = Field

I also try to use any built in function like Ccur or Val or CDbl inside the query and I got the following message:

strsql="SELECT val(telephony.price) as price" _
& " FROM telephony.dbo.call_type call_type"


Microsoft OLE DB Provider for ODBC Drivers error '80004005'

[INTERSOLV][ODBC SQL Server driver][SQL Server]'Val' is not a recognized built-in function name.
 

0
barryfandangoCommented:
try this for the typename test:  response.write(TypeName(rectel.Fields("price").Value))
0
David H.H.LeeCommented:
ecantu ,
you cant use val() inside query because that is vbscript build in function.Use it before you store the value. However, since you said no problems store with data, then i think what you need it display problems only.
try this:
<%
 Response.write(FormatNumber(rectel.Fields("price"),2)) <---2 digit format
%>

Regards
x_com
0
Göran AnderssonCommented:
>> By the way I try the TypeName function over the field and returns "Field" instead of numeric, Double, long, integer or something like that.

That is because you are getting the data type of the field object. Put the value in a varaible, and check what data type the variable uses:

vntPrice=rectel.Fields("price")
Response.Write TypeName(vntPrice)
0
David H.H.LeeCommented:
0
ecantuAuthor Commented:
I already use the FormatNumber function but the proble is that if I have 8 or 80 and use the FormatNumber function in both cases I will get 8.00

supouse: rectel.Fields("price") = 8
Response.write(FormatNumber(rectel.Fields("price"),2)) = 8.00
supouse: rectel.Fields("price") = 80
Response.write(FormatNumber(rectel.Fields("price"),2)) = 8.00
supouse: rectel.Fields("price") = 800
Response.write(FormatNumber(rectel.Fields("price"),2)) = 8.00

I already try the convert function in Sybase inside the query which works like:

convert(numeric(18,2), telephony.price) As Price

and also get the same result, 8 in all the cases, I tried changing the structure of the table for another data types but I got the same error in all the cases. Each time I try to use a numeric Data type Field, the ceros to the right are truncated.  

I also tryed passing the value of the field to a variable and I got:

vntPrice=rectel.Fields("price")
Response.Write TypeName(vntPrice)


Microsoft VBScript runtime error '800a01ca'

Variable uses an Automation type not supported in VBScript: 'TypeName'

0
David H.H.LeeCommented:
ecantu ,
Try change your data type from numeric to float.
But, you can Use the Convert function to cast them to a new data type.

Syntax
CONVERT (data_type[(length)], expression [, style])

hope that helps.
0
David H.H.LeeCommented:
0
ecantuAuthor Commented:
Nop, I already installed the latest version of the MDAC (MDAC 2.7 SP1) on the server machine and didnt work
0
Göran AnderssonCommented:
The numeric field does not seem to convert to any VBScript data type. Try to convert it to a datatype that VBScript knows, like x_com suggested:

strsql="SELECT convert(telephony.price,float) FROM telephony.dbo.call_type call_type"
0
ecantuAuthor Commented:
Im solving it using the Convert like:

Convert(Varchar(20), telephony.price) as Price

and then:

var1 = Cdbl(rectel.fields("price"))

and works fine, even thoug I don't like the solution.


Please if you got a better solution let me know
0
Göran AnderssonCommented:
You can convert it to a double precision floating point number. That will become a Double in VBScript:

convert(float,telephony.price)
0
ecantuAuthor Commented:
If I try to convert the value to any numeric data type it won't work, I still have the problem of truncated ceros. I already try Float and Numeric, they work OK but the values are still truncated.
0
Göran AnderssonCommented:
The numeric data type has no equivalent type in VBScript, so you will probably always have to convert them in the same way. I've seen other having the same problem when reading numeric data from SQL server.

Strange that float doesn't work... You could try the sinlge precision floating point data type: real.

If you have a numeric field without decimal places, converting it to int should work. The equivalent data type in VBScript is Long.
0
David H.H.LeeCommented:
ecantu ,
>>
Convert(Varchar(20), telephony.price) as Price

and then:

var1 = Cdbl(rectel.fields("price"))

-I think this is the best solutions as i knew. I also need to see what else easier than this..hmmm.
 GreenGhost maybe had some good suggestions too.

Regards
x_com
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
ecantuAuthor Commented:
Well guys, I'm closing this case. thanks a lot for all the help.

Again if you got a better solution than Convert(varchar(),) let me know.


Thanks a Lot!!
0
Göran AnderssonCommented:
So... You split the points between x_com, x_com and x_com? ;)
0
ecantuAuthor Commented:
:O , #-o , :P

If I was more strict I should give me the points because I reach the Convert(varchar(),) solution.

But you're right I blow that up, excuse me GreenGhost
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
ASP

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.