Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 
>>> >> > Make the most of SQL Server Integration Services Script Components < << <<<
 

Programmatically Setting SharePoint Lookup and People Picker


By:   |   Read Comments   |   Related Tips: > Sharepoint

Problem

Sometimes we need to programmatically set the value of a Lookup column field or People Picker / Person & Group column field of a SharePoint List / Library. But this is a problem because these columns are initially set to be in a read-only mode by default.

Solution

So when you need to change (or set) the value for any one of them, you need to follow the following solution.

Assume we have a SharePoint custom list called "Employee" which has the following columns or fields:

- "Title " => Single line of text data type default initial column.

- "Username" => People Picker (Person & Group) data type column.

- "Unit" => Lookup filed column (Single Line of text data type column from a "Department" custom list)

click for larger image
click for larger image
click for larger image

Let's open Visual Studio.NET 2005 /2008 or even 2010 IDE and create a new Windows application sample project.

(You can use the best practice snippet code in your web parts or other desired solutions) which will contain the initial wrong solution and the best practice snippet code for adding & setting "Username" column value with the current logged in authorized user and also "Unit" with another single text value :

1. File > New > Project > Visual C# (Language) : Windows : Windows Application.

2. Add a button in order to fire the setting event handler for "Username" & "Unit" : Open "Toolbox" > Drag and drop "Button" control to the form :

click for larger image

3. Add "Microsoft. SharePoint DLL" as a reference : Open "Solution Explorer" > Right click "References" > Add reference.

4. Browse (Tab) > Go to the ISAPI 12 hive directory destination "<Drive>:\Program Files\Common Files\Microsoft Shared\web server extensions\12\ISAPI" > Microsoft. SharePoint.dll >

Ok :

add reference

5. Right click on the empty white form > View Code :

VS3

6. Add / Register namespace reference "Using Microsoft. SharePoint" :

visual studio

7. Double click on the "Fire" button to go to the code behind "Click" event handler.

8. Let's try the initial wrong solution snippet code and see the result then we'll see the best practice :

Wrong solution snippet code

private void button1_Click(object sender, EventArgs e)
{
SPSecurity.RunWithElevatedPrivileges(delegate()
{
// In order to execute the code as an administrator privileges use "RunWithElevatedPrivileges" in order to
// avoid any "Access denied" error which may be raised during executing SharePoint object model code.
// Create an instance (object) from SPSite to access your Site Collection.
try
{

using (SPSite mySite = new SPSite("http://heroking/en/"))
// We use "Using" close for disposing the objects (instances) after executing the code to save the memory and 
// to avoid any memory leak problems.
// Best Practice is to use SPContext.Current.Site.Url instead of the URL address in our solutions but as for the
// windows application sample we'll use the URL.
// Create an instance (object) from SPWeb to open your web under your site collection.
{
using (SPWeb myWeb = mySite.OpenWeb())
{
SPList empList = myWeb.Lists["Employee"];
// Access our custom list "Employee"
SPListItem empItem = empList.Items.Add();
// For Adding / Updating any item in the Employee list items.
string currentUser = WindowsIdentity.GetCurrent().Name;
// Get current authorized user for windows application if you'll use web applications use:
// SPContext.Current.Web.CurrentUser.LoginName instead
empItem["Title"] = "New Title";
// Set "Title" single line of text field column with new text value
empItem["Username"] = myWeb.AllUsers[currentUser].ID;
// Trying to set "UserName" people picker (person & group) column field with the current logged in user.
empItem["Unit"] = "New Unit";
// Trying to set "Unit" lookup field column with a new single line of text value.
empItem.Update(); // Breakpoint inserted here !
empList.Update();
}
}
}
catch (Exception ex)
{
string errMsg = ex.Message.ToString();
}
});
}

Then let's build our solution "CRTL + Shift + B" and wait until we get the "Build Successes" message - - then let's insert some breakpoints...

Go to the targeted line of code and click "F9" ... at certain lines of code, for example: "empItem.Update()" , then press "F5" for changing to the debugging mode and then click on the "Fire" button to see what are the results :

visual studio

You'll get the following error message:

"Invalid data has been used to update the list item . The field you are trying to update may be read only".

That's what we are talking about. So we need to remove the read only setting from both field before update and then rollback.

Best practice snippet code

Now we'll follow this snippet code which is the best practice to add & set the value for both "Username" with the current logged in authorized user and "Unit" with another single text value:

private void button1_Click(object sender, EventArgs e)
{
SPSecurity.RunWithElevatedPrivileges(delegate()
{
// In order to execute the code as an administrator privileges use "RunWithElevatedPrivileges" in order to
// avoid any "Access denied" error which may be raised during executing SharePoint object model code.
// Create an instance (object) from SPSite to access your Site Collection.
try
{

using (SPSite mySite = new SPSite("http://heroking/en/"))
// We use "Using" close for disposing the objects (instances) after executing the code to save the memory and 
// to avoid any memory leak problems.
// Best Practice is to use SPContext.Current.Site.Url instead of the URl address in our solutions but as for
// windows application sample we'll use the URL.
// Create an instance (object) from SPWeb to open your web under your site colection.
{
using (SPWeb myWeb = mySite.OpenWeb())
{
SPList empList = myWeb.Lists["Employee"];
// Access our custom list "Employee"
SPListItem empItem = empList.Items.Add();
// For Adding / Updating any item in the Employee list items.
myWeb.AllowUnsafeUpdates = true;
// To do any updates to lists, webs or any SharePoint objects that require an SPSite to be created first,
// and if you do not set AllowUnsafeUpdates to true you will get this exception
// "The security validation for this page is invalid." 
string currentUser = WindowsIdentity.GetCurrent().Name;
// Get current authorized user for windows application if you'll use web applications use :
// SPContext.Current.Web.CurrentUser.LoginName instead
empItem["Title"] = "New Title";
// Set "Title" single line of text field column with new text value
empItem.Fields["Username"].ReadOnlyField = false;
// Remove the default Read Only mode for the "Username" people picker (person & group) column field.
empItem["Username"] = myWeb.AllUsers[currentUser].ID;
// Set "UserName" people picker (person & group) column field with the current logged in user.
empItem.Fields["Unit"].ReadOnlyField = false;
// Remove also the default Read Only mode for the "Unit" lookup column field.
empItem["Unit"] = new SPFieldLookupValue(2, "HR");
// Set "Unit" lookup field column with a new single line of text value using the SPFieldLookupValue where 2 referes to the
// the new single line of text value 'LookUpID' and the "HR" the LookUpValue . We use this class with the above form 
// shape because any lookup displayed with some special characters like '#' and ';' in the debugging mode.
empItem.Update();
empList.Update();
empItem.Fields["Username"].ReadOnlyField = true;
empItem.Fields["Unit"].ReadOnlyField = true;
// Roolback the Read only mode settings for "Username" & "Unit" to the initial default (ReadOnlyFiled = true) settings.
empItem.Update();
empList.Update();
}
}
}
catch (Exception ex)
{
string errMsg = ex.Message.ToString();
}
});

}

And here's the result :

employee
Next Steps


Last Update:


next webcast button


next tip button



About the author





Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



Learn more about SQL Server tools