ADO and ASP Bizarre Problem

[sheykc]

I have an interesting problem. The data on our Sybase server has $10 or $90 (even numbers of 10, below $100) in a money field. But, when ADO returns it--it's result is $1, or $9 (or whatever the number is).

I've never seen this before. Does anyone know why this is happening? It doesn't happen on any other amounts, just even numbers of 10, below $100. It doesn't happen on any other money fields either.

I'm using ADO in an ASP page.

Here is a sample of the code:

set oFSCN = Server.CreateObject("ADODB.Connection&quot
set oFS1 = Server.CreateObject("ADODB.Recordset&quot

With oFSCN
.Provider = "MSDASQL.1"
.Properties("User ID&quot = sUserID
.Properties("Password&quot = sPassword
.Properties("Data Source&quot = sDataSource
.CursorLocation = 3
.Open
End With

With oFS1
.Source = sHnobjectsDB & "..hnsp_ecp_fsprol01_s '" & cStr(sClaimID) & "'"
.ActiveConnection = oFSCN
.Open
End With

If oFS1.RecordCount > 0 then
For y = 1 to oFS1.RecordCount
Response.Write oFS1.Fields.Item("cdml_chg_amt&quot.Value
Next
oFS1.MoveNext
End If

Returns:
1
21
25
25
13

If I use Query Analyzer and execute the stored procedure, the values in the column are:

10.00
21.00
25.00
25.00
13.00

Any suggestions would be helpful.

Thanks in advance,

Shey

 

[DrMaggie]

stobeck:

I've never heard of such a problem either - but hey, computers are always worth a laugh, aren't they...

Are you storing these prices (or whatever) in the "money" data type format of Sybase? Could it be that there is a bug with this particular data type that would be avoided if you stored the values as "normal" numbers?

Perhaps you can create a test database and try different solutions!?

DrMaggie ---------
"Those who don't know the mistakes of the past won't be able to enjoy it when they make them again in the future."
— Leonard McCoy, MD (in Diane Duane's Doctor's Orders)

 

[sheykc]

I was thinking along the same idea. I'm thinking of changing the stored procedure so it creates a numeric instead of money type. I was just trying to avoid that, because this stored procedure is being used by another client/server based app too (where the problem doesn't occur).

Just so strange...

Thanks for the help.

 

[DrMaggie]

stobeck:

Have you tried contacting the tech support of your vendor or of Sybase? Perhaps there is some FAQ available, or a list of known issues - it seems odd that you (or rather your application) should be the only one experiencing this problem!

Anyway, good luck - and if you figure it out, I'm sure plenty of people would appreciate it, if you post back to this forum with the solution!

Peace and long life...

DrMaggie ---------
"Those who don't know the mistakes of the past won't be able to enjoy it when they make them again in the future."
— Leonard McCoy, MD (in Diane Duane's Doctor's Orders)

 

[sheykc]

The problem was a noted Sybase issue in the 11.1.1 client. So, I created a workaround for now, by converting the money value to a numeric value in the stored procedure.

After more testing, we also discovered that this issue was occuring on other values higher than $100. Basically, any number ending in 0, the 0's to the left of the decimal were being truncated.

As it turns out, I really didn't need to do all of this--because we are sending this stored procedure to web hosting facility--where they run a newer version of the Sybase client and this issue has been resolved.