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);
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#

  • 3
    Links can break over time. Please add relevant information from the blog. – Amal Hashim May 12 '15 at 12:20
  • @AmalHashim this is not be a relevant info, how? – Mirza Danish Baig May 12 '15 at 12:21
  • You should quote the relevant text from the link you provided, in case the link goes dead someday. – Silvermind May 12 '15 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 '15 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);
  • 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 '15 at 12:01
  • CAML query added. – Submits May 12 '15 at 12:05
  • Are those look up fields or calculated fields? – Amal Hashim May 12 '15 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 '15 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. – Amal Hashim May 12 '15 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, privacy policy and cookie policy

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