Anybody know of any issues with using the SQL Server money data type? I can't seem to return a value from a stored proc using money or decimal for some reason. I've tried just about every combination I can think of, on the BO side and the SQL side.
Any insight or direction is greatly appreciated.
Kelly.
Yeah, it's hard to offer anything without knowing the specific problem, but I'll take a shot in the dark and offer this help document I've found very useful:
The following is an excerpt from the code and from the SP:
dr.NextResult();
//_currentbucket = dr.GetDouble("@CurrentBucket");
// ** The BO Part that errors ** //
_currentbucket = (float)dr.GetDecimal("CurrentBucket");
// ** The SP that provides the data ** //
// ** with different attempts at ** //
ALTER PROCEDURE [dbo].[getCarrierDisplayData]
(
@ID uniqueidentifier
/* @CurrentBucket decimal(18,2) OUTPUT */
)
AS
SELECT CarrierID,
CarrierNumber,
[Name],
Address1,
City,
State,
Zipcode,
ZipExtension,
WebAddress,
EFT,
Void,
VoidCode,
Bucket
FROM tblCarriers
WHERE CarrierID = @ID
/* SELECT @CurrentBucket = SUM(Amount)
FROM tblCarrierBuckets
WHERE CarrierID = @ID */
SELECT SUM(Amount) as 'CurrentBucket'
FROM tblCarrierBuckets
WHERE CarrierID = @ID
RETURN
Attached is the above information and the StackTrace in a text document. Just kind of a weird exception. All the other data comes across just fine; however, when I try to assign the @CurrentBucket to the _currentbucket field, the exception gets thrown.
The Amount field in the database was originally Money. And has been changed to Decimal in an effort to get something working. But neither work.
Thanks,
Kelly.
Sorry - Left off the text file.
Kelly.
put the db back to type money.
then check your code that fetches the data should be someting like:
foo = dr.GetDecimal("PayAmt")
where "foo" is declared as Decimal
works for me in my generated code via CodeSmith.
if you mix up double / decimal / money then you have issues and have to use ConvertTo.Decimal() and other stuff...
Thanks for your help. I will get everything back in line and give that a try.
Kelly.
Okay, I got everything lined up using decimal on the object side and money on the database side. I am getting a "Invalid attempt to read when no data is present". However, when I drill-down into the safedatareader, all the data is there in both resultsets from the stored proc.
I can include the class code if needed; but this is driving me nuts.
Thanks for any help offered.
Kelly.
jkellywilkerson:
Okay, I got everything lined up using decimal on the object side and money on the database side. I am getting a "Invalid attempt to read when no data is present". However, when I drill-down into the safedatareader, all the data is there in both resultsets from the stored proc.
I can include the class code if needed; but this is driving me nuts.
Thanks for any help offered.
Kelly.
I have had that when a .Read() or a .NextResults() was missing.
is it a proc ?
does it return 1 table or data from 2 or more selects ?
make sure if it's going from parent to child that nextresults is done at the right spot.
Thanks for your help. Attached is the entire object code. It is a simple ROBase object that I finally removed all the authorization stuff; thinking that was causing the read problem, but that had no effect. Surely it's something simple that I'm just missing. Anyway, if you see something, please let me know.
Kelly.
Hey Andy,
Thanks. In the attached image you can see that I made the change; but I still get the same error, even though the correct data is shown in the SDR resultset parameter value. I have run extensive checks on the stored proc and it works as expected - two resultsets, one with the data needed for the first set of object fields and the other resultset with the CurrentBucket sum. Not sure what else to try.
Thanks,
Kelly.
if ( dr.NextResult() && dr.Read() ) {
_currentbucket = dr.GetDecimal("@CurrentBucket");
}
else {
throw new Exception( "Couldn't find data" );
}
Are you sure you're getting a non-empty result set back?
You're right. The exception gets thrown. But what I don't get is how can the parameter get assigned the value in the stored proc., but it not transfer back to the SDR? I can put break point on the throw and drill down into the "dr" and look at the parameter with the correct value (as shown in the attached screenshot). When I execute the procdure using sql mgmt I get the resultset containing Id, Address, etc., a resultset containing the bucket sum, and a resultset containing the procedure return value. Do you see anything wrong with the sp listing in the previous attachment?
Thanks for your help.
Kelly
I don't think the GetDecimal on the datareader is the way you retrieve values from an output parameter. According to the documentation you use the Value property of the parameter object itself to read the value. The screenshot you posted shows the correct value in the parameter object, not in a resultset, so dr.GetWhatever() won't ever work.
Thanks Andy, I figured it out. I don't know why I was trying to use an output parameter to return the value; I guess because that is the way the LastChanged timestamp is returned. Since that works so well, I set this stored proc. to work the same. I guess returning a single value is not the same as returning a resultset. So I took the parameter out and just did a select sum(amount) as currentbucket and let that return the value in a single field.
Thanks for pushing me in the right direction; I would have continued to think I was returning something.
Kelly.
![Smile [:)]](/emoticons/emotion-1.gif)
Hi,
Using the datareader I tried to loose the @-sign before CurrentBucket. Unless your database columnames start with "@".
Copyright (c) Marimer LLC