Solved

Number Fields with ceros to the right truncated

Posted on 2003-10-28
23
494 Views
Last Modified: 2007-12-19
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!!
 
0
Comment
Question by:ecantu
  • 8
  • 7
  • 5
  • +2
23 Comments
 
LVL 8

Expert Comment

by:fozylet
Comment Utility
Tried changing it to currency type?
CCur (?)
0
 

Author Comment

by:ecantu
Comment Utility
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
 
LVL 29

Expert Comment

by:David H.H.Lee
Comment Utility
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
 
LVL 29

Expert Comment

by:David H.H.Lee
Comment Utility
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
 
LVL 3

Expert Comment

by:barryfandango
Comment Utility
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
 

Author Comment

by:ecantu
Comment Utility
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
 
LVL 3

Expert Comment

by:barryfandango
Comment Utility
try this for the typename test:  response.write(TypeName(rectel.Fields("price").Value))
0
 
LVL 29

Assisted Solution

by:David H.H.Lee
David H.H.Lee earned 500 total points
Comment Utility
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
 
LVL 29

Expert Comment

by:Göran Andersson
Comment Utility
>> 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
 
LVL 29

Expert Comment

by:David H.H.Lee
Comment Utility
0
 

Author Comment

by:ecantu
Comment Utility
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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 29

Expert Comment

by:David H.H.Lee
Comment Utility
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
 
LVL 29

Assisted Solution

by:David H.H.Lee
David H.H.Lee earned 500 total points
Comment Utility
0
 

Author Comment

by:ecantu
Comment Utility
Nop, I already installed the latest version of the MDAC (MDAC 2.7 SP1) on the server machine and didnt work
0
 
LVL 29

Expert Comment

by:Göran Andersson
Comment Utility
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
 

Author Comment

by:ecantu
Comment Utility
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
 
LVL 29

Expert Comment

by:Göran Andersson
Comment Utility
You can convert it to a double precision floating point number. That will become a Double in VBScript:

convert(float,telephony.price)
0
 

Author Comment

by:ecantu
Comment Utility
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
 
LVL 29

Expert Comment

by:Göran Andersson
Comment Utility
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
 
LVL 29

Accepted Solution

by:
David H.H.Lee earned 500 total points
Comment Utility
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
 

Author Comment

by:ecantu
Comment Utility
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
 
LVL 29

Expert Comment

by:Göran Andersson
Comment Utility
So... You split the points between x_com, x_com and x_com? ;)
0
 

Author Comment

by:ecantu
Comment Utility
: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

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

I have helped a lot of people on EE with their coding sources and have enjoyed near about every minute of it. Sometimes it can get a little tedious but it is always a challenge and the one thing that I always say is:  The Exchange of information …
I would like to start this tip/trick by saying Thank You, to all who said that this could not be done, as it forced me to make sure that it could be accomplished. :) To start, I want to make sure everyone understands the importance of utilizing p…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
This video demonstrates how to create an example email signature rule for a department in a company using CodeTwo Exchange Rules. The signature will be inserted beneath users' latest emails in conversations and will be displayed in users' Sent Items…

728 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now