Dude, whats my column type?

Some weird behaviour with SharePoint column types, and the data you get by looking at the SPListItem.properties hash.

So, I’ve got a document library, and I’ve added 3 extra columns to it. For simplicity, I shall call them ‘Text’, ‘Number’ and ‘CheckBox’. I filled in some data for them – a number in the Text field, the same number in the number field, and I check the checkbox for all of the items. I end up with a list looking like below…

So, for now, let’s ignore the fact that the value for some of the longer numbers has been rounded in one of the fields – and not ask why MOSS didn’t just complain that the numbers were too big. Instead, let’s ask what the column types being used are…

Consider the following code:
StreamWriter sw = new StreamWriter("output.txt",false);

foreach (SPListItem i in myCollection)
{
sw.Write(i.Name);
foreach (DictionaryEntry h in i.Properties)
{
if (h.Key.ToString().StartsWith("Title") ||
h.Key.ToString().StartsWith("Tex") ||
h.Key.ToString().StartsWith("Yes") ||
h.Key.ToString().StartsWith("Num"))
{
sw.Write(" {0} = {1} ({2})",
h.Key.ToString(),
h.Value.ToString(),
h.Value.GetType().ToString());
}
}
sw.WriteLine();
}
sw.Close();

So, what did this produce? Well, I’ve changed the output to align into columns…

b2b2b2 01.xls Text = 65534             (Int32)  YesNo = 1    (Int32)  Number = 65534.0000000000  (String)
b2b2b2 03.xls Text = 65540             (Int32)  YesNo = 1    (Int32)  Number = 65540.0000000000  (String)
b2b2b2 05.doc Text = 131070            (Int32)  YesNo = 1    (Int32)  Number = 131070.000000000  (String)
b2b2b2 06.xls Text = 131080            (Int32)  YesNo = 1    (Int32)  Number = 131080.000000000  (String)
b2b2b2 07.xls Text = 9999999999999998  (String) YesNo = 1    (Int32)  Number = 9999999999999990  (String)
b2b2b2 09.doc Text = 9999999999999999  (String) YesNo = 1    (Int32)  Number = 9999999999999990  (String)
b2b2b2 12.TIF Text = 10000000000000000 (String) YesNo = true (String) Number = 10000000000000000 (String)
b2b2b2 17.doc Text = 2342345245        (String) YesNo = 1    (Int32)  Number = 2342345245.00000  (String)
b2b2b2 20.doc Text = 4353455656563     (String) YesNo = 1    (Int32)  Number = 4353455656563.00  (String)
c3c3c3 03.jpg Text = 2147483646        (String) YesNo = true (String) Number = 2147483646.00000  (String)
c3c3c3 10.pdf Text = 2147483650        (String) YesNo = true (String) Number = 2147483650.00000  (String)
c3c3c3 17.txt Text = 4294967294        (String) YesNo = true (String) Number = 4294967294.00000  (String)
c3c3c3 18.txt Text = 4294967298        (String) YesNo = true (String) Number = 4294967298.00000  (String)

Hello? The checkbox is sometimes ‘true’, and 1? String or Int32? WTF? Or that the Text column stores 65534 as an Int32, and the Number field as a String? Okay, I can see why the last one might make sense, but why is the TEXT column a number? And what if I now want to assign a String to it? I tried…

i.properties['Text'] = "Hello World";

Guess what – exception (SPInvalidPropertyException, to be precise). But maybe, just maybe, I knew I’d be putting text in there later and that was why I made it an effing text column. And why is the Boolean column an Int or a string? I mean, either is a fine choice, but not BOTH.

Don’t get this. Pissed off. Am going home.

Advertisements
Dude, whats my column type?

3 thoughts on “Dude, whats my column type?

  1. Hello Andy,

    I’m facing the same issue. I can store value inside a custom property but sometimes, when I want to retrieve it, it raises an exception with the dreaded SPInvalidPropertyException).

    Digging deeper, my “false” string is stored as “0” for reasons behond me.

    I really want to throw my laptop in the window, so if you found a fix or a solution to this old problem, I would be really glad to hear it.

    Thank you very much.

    François

    Like

  2. In the end, I assumed that all of my properties were strings (or got them as strings – e.g. item.properties[“someInt”].ToString()) and then parsed them to the correct type. I know, it sucks, but I never did find a satisfactory answer.

    Interestingly, it isn’t something I have many problems with now – not sure why.

    Like

  3. Thank you for your reply.

    I never had that issue with SPWeb.Properties or .AllProperties where I’m quite used to store, update and retrieve properties (doing like you, storing as string and retrieving to the correct type) but with SPListItem, that’s a much different story

    Creating and retrieving is not an issue, but updating is a nightmare, after several hours of googling / testing / exploring I finally did

    mySPListItem.Properties.Remove(“myKey”);
    mySPListItem.SystemUpdate();
    mySPListItem.Properties.Add(“myKey”,”myValue”);
    mySPListItem.SystemUpdate();

    At least it’s cleaner than converting my value to a possible int (or boolean, I got that type as well) just in case for some reason SharePoint thinks that my string value is in fact an int…

    Anyway, thanks for your feedback 🙂

    Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s