Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Number Fields with ceros to the right truncated

Posted on 2003-10-28
23
Medium Priority
?
519 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 8
  • 7
  • 5
  • +2
23 Comments
 
LVL 8

Expert Comment

by:fozylet
ID: 9638786
Tried changing it to currency type?
CCur (?)
0
 

Author Comment

by:ecantu
ID: 9638799
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
ID: 9638848
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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
LVL 29

Expert Comment

by:David H.H.Lee
ID: 9638855
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
ID: 9638885
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
ID: 9643636
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
ID: 9643671
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 1500 total points
ID: 9643754
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
ID: 9643768
>> 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
ID: 9643790
0
 

Author Comment

by:ecantu
ID: 9644070
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
 
LVL 29

Expert Comment

by:David H.H.Lee
ID: 9644401
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 1500 total points
ID: 9644502
0
 

Author Comment

by:ecantu
ID: 9645690
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
ID: 9646693
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
ID: 9646775
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
ID: 9646812
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
ID: 9646868
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
ID: 9647140
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 1500 total points
ID: 9649928
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
ID: 9651723
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
ID: 9652110
So... You split the points between x_com, x_com and x_com? ;)
0
 

Author Comment

by:ecantu
ID: 9652441
: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

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I recently decide that I needed a way to make my pages scream on the net.   While searching around how I can accomplish this I stumbled across a great article that stated "minimize the server requests." I got to thinking, hey, I use more than one…
Hello, all! I just recently started using Microsoft's IIS 7.5 within Windows 7, as I just downloaded and installed the 90 day trial of Windows 7. (Got to love Microsoft for allowing 90 days) The main reason for downloading and testing Windows 7 is t…
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…

636 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