1

I'm running a CAML query to retrieve list items. Some of the columns retrieved contain decimal numbers. The problem is the values coming back from the query look like float;#22.0000000000000. I just need 22.0.

I've tried using ToString(), Decimal.Parse, casting with (float), (double), etc. and they all give me exceptions.

How can I resolve this? Why is this even happening?

CAML query (Approved, Pending and Remaining contain decimal numbers):

SPQuery qry = new SPQuery();
qry.Query = String.Format(
@"   <Where>
    <And>
        <Eq>
            <FieldRef Name='Person' />
            <Value Type='Integer'>
                <UserID />
            </Value>
        </Eq>
        <Or>
            <Eq>
                <FieldRef Name='Year' />
                <Value Type='Choice'>{0}</Value>
            </Eq>
            <Eq>
                <FieldRef Name='Year' />
                <Value Type='Choice'>{1}</Value>
            </Eq>
        </Or>
    </And>
</Where>", currentYear.ToString(), (currentYear + 1).ToString());
qry.ViewFields = @"<FieldRef Name='Approved' /><FieldRef Name='Pending' /><FieldRef Name='Remaining' /><FieldRef Name='Year' />";
SPListItemCollection items = leaveEntList.GetItems(qry);

3 Answers 3

1

The easiest way to get a string for conversion is to use SPListItem. the item.GetFormattedValue("ColumnName")

Go though the mentioned post, it could be work for you.

How to convert a decimal number field to C#

4
  • 3
    Links can break over time. Please add relevant information from the blog. May 12, 2015 at 12:20
  • @AmalHashim this is not be a relevant info, how? May 12, 2015 at 12:21
  • You should quote the relevant text from the link you provided, in case the link goes dead someday.
    – Silvermind
    May 12, 2015 at 12:22
  • Amal was absolutely correct in saying this is how SharePoint stores calculated fields internally. The solution, however, is in the link you provided, thanks. item.GetFormattedValue("Remaining") did the trick.
    – Submits
    May 12, 2015 at 12:25
1

This is how SharePoint stores the data internally. You need to use String manipulation for extracting the data.

var data = "float;#22.0000000000000";
//Option 1 - Replace float;# with empty string
string result = data.Replace("float;#", string.Empty);
//Now convert to decimal
decimal d = decimal.Parse(result);

//Option 2 - Using index of #
string result = data.SubString(0, data.IndexOf("#"));
//Now convert to decimal
decimal d = decimal.Parse(result);
5
  • Amal, why do I not have this problem if I don't use a CAML query and retrieve the item just by iterating through the list items instead?
    – Submits
    May 12, 2015 at 12:01
  • CAML query added.
    – Submits
    May 12, 2015 at 12:05
  • Are those look up fields or calculated fields? May 12, 2015 at 12:08
  • Approved and Pending are number fields. Remaining is calculated. Also should point out that I've only been playing with the Remaining field up until now - not checked the output of Approved and Pending. I wonder if it's because it's a calculated field?
    – Submits
    May 12, 2015 at 12:12
  • 1
    Yes exactly. When you iterate I believe SharePoint does the Job of converting it back to float. While in CAML query we need to do that of properly converting. The same applies to UserField/LookupField etc. May 12, 2015 at 12:14
1

If your field type is CALCULATED, it is a known behaviour to solve this you can use SPFieldCalculated object example:

SPFieldCalculated cf = (SPFieldCalculated)item.Fields["AverageFeedBackRating"];

string cfVal = cf.GetFieldValueForEidit(item["AverageFeedBackRating"]);

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.