Number Fields with ceros to the right truncated

Tried changing it to currency type?
CCur (?)

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

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")

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

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.

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.
 

try this for the typename test:  response.write(TypeName(rectel.Fields("price").Value))

>> 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)

More details about FormatNumber
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/vblr7/html/vafctformatnumber.asp

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'

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.

Nop, I already installed the latest version of the MDAC (MDAC 2.7 SP1) on the server machine and didnt work

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"

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

You can convert it to a double precision floating point number. That will become a Double in VBScript:

convert(float,telephony.price)

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.

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.

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!!

So... You split the points between x_com, x_com and x_com? ;)

: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