Sharepoint OLE DB - cannot insert records? "Field not updateable" error

Question

I need to write a simple C# .NET application to retrieve, update, and insert some data in a Sharepoint list.

I am NOT a Sharepoint developer, and I don't have control over our Sharepoint server. I would prefer not to have to develop this in a proper sharepoint development environment simply because I don't want to have to deploy my application on the Sharepoint server -- I'd rather just access data externally.

Anyway, I found out that you can access Sharepoint data using OLE DB, and I tried it successfully using some ADO.NET:

var db = DatabaseFactory.CreateDatabase();
DataSet ds = new DataSet();
using (var command = db.GetSqlStringCommand("SELECT * FROM List"))
{
    db.LoadDataSet(command, ds, "List");
}

The above works.

However, when I try to insert:

using (var command = db.GetSqlStringCommand("INSERT INTO List ([HeaderName],
    [Description], [Number]) VALUES ('Blah', 'Blah', 100)"))
{
    db.ExecuteNonQuery(command);
}

I get this error:

Cannot update 'HeaderName'; field not updateable.

I did some Googling and apparently you cannot insert data through OLE DB!

Does anyone know if there are some possible workarounds?

I could try using Sharepoint Web Services, but I tried that initially and was having a heck of a time authenticating. Is that my only option?

Answer 1

OLEDB support is very much news to me, I guess because it's not commonly used due to it's read only nature. What I tend to do is use the object model on the server itself. It's not too much hassle - you only really need to include the one reference and it's significantly easier that resorting to web services. If you want to run externally however web services are your only choice. Either that or combine the two approaches and develop your own web service that runs on the server under IIS and contains object model code.

Answer 2

Due to licensing for SharePoint, I would not be inserting records directly into the back end database !, At any rate, use the native Sharepoint Web Services, i.e. http://server/site/_vti_bin/Lists.asmx

SharePoint has MANY Web Services you can call to do almost anything SharePoint does natively.

Each Site Collection can use their own Web Service, no code needs to go on the Server, no Assemblies, no direct Database changes... they are pretty easy to use and there are many articles on them both from Microsoft and others.

You need to attach a normal NetworkCredentials class with correct user details and bind this to the instantiated Web Service class.

If your SharePoint uses a SQL Server you could try using the SqlClient Namespace rather than OLEDB, the beauty of the Web Services is you don't have to worry so much about which Content Database your Site Collection is on, especially if SharePoint creates them on the fly rather than an Administrator actually specifying it.

Hope this Helps.

Cheers.

Answer 3

The solution to use OLEDB for writes to a SharePoint list is to specify IMEX=0 in the connection string.

http://www.connectionstrings.com/sharepoint

Answer 4

The same thing is true using ADO and INSERT INTO statements

If you link to the list in Access, you can use it as a native access table.

Dim db as Database
Set db = CurrentDb
db.Execute "INSERT INTO [List Name]..."

Answer 5

I also use Impersonation if needed:

    using System.Security.Principal;

using System.Runtime.InteropServices;

 #region Constants
    public const int LOGON32_LOGON_INTERACTIVE = 2;
    public const int LOGON32_PROVIDER_DEFAULT = 0;
 #endregion

public WindowsImpersonationContext impersonationContext;

#region Win32 API
    [DllImport("advapi32.dll", CharSet = CharSet.Ansi, SetLastError = true, ExactSpelling = true)]
    public static extern int LogonUserA(string lpszUsername, string lpszDomain, string lpszPassword, int dwLogonType, int dwLogonProvider, ref IntPtr phToken);
    [DllImport("advapi32.dll", CharSet = CharSet.Auto, SetLastError = true, ExactSpelling = true)]
    public static extern int DuplicateToken(IntPtr ExistingTokenHandle, int ImpersonationLevel, ref IntPtr DuplicateTokenHandle);
    [DllImport("advapi32.dll", CharSet = CharSet.Auto, SetLastError = true, ExactSpelling = true)]
    public static extern bool RevertToSelf();
    [DllImport("kernel32.dll", CharSet = CharSet.Auto, SetLastError = true, ExactSpelling = true)]
    public static extern long CloseHandle(IntPtr handle);
    #endregion

public bool Impersonate(string userName, string domain, string password)
    {
        try
        {
            bool functionReturnValue = false;
            WindowsIdentity tempWindowsIdentity = default(WindowsIdentity);
            IntPtr token = IntPtr.Zero;
            IntPtr tokenDuplicate = IntPtr.Zero;
            functionReturnValue = false;
            if (RevertToSelf())
            {
                if (LogonUserA(userName, domain, password, LOGON32_LOGON_INTERACTIVE, LOGON32_PROVIDER_DEFAULT, ref token) != 0)
                {
                    if (DuplicateToken(token, 2, ref tokenDuplicate) != 0)
                    {
                        tempWindowsIdentity = new WindowsIdentity(tokenDuplicate);
                        impersonationContext = tempWindowsIdentity.Impersonate();
                        if ((impersonationContext != null))
                        {
                            functionReturnValue = true;
                        }
                    }
                }
            }
            if (!tokenDuplicate.Equals(IntPtr.Zero))
            {
                CloseHandle(tokenDuplicate);
            }
            if (!token.Equals(IntPtr.Zero))
            {
                CloseHandle(token);
            }

            return functionReturnValue;
        }
        catch (Exception ex)
        {
            SQMSLog.WriteLogEntry(ex.Message, "SYSTEM");
            return false;
        }
    }

public void UndoImpersonate()
    {
        impersonationContext.Undo();
    }

Call the Impersonate Function First then:

  //Add Miscellaneous Data Details
            data.ListNameEx = Settings.Default.SPListLoader_List_Name;
            //Instantiate the Web Service
            SPListService.Lists lists = new SPListLoaderService.SPListService.Lists();
            //Bind Appropriate Credentials to the Web Service
            lists.Credentials = new NetworkCredential(data.UserName, data.Password, data.Domain);
            //Set the List Object URI
            lists.Url = data.URI.TrimEnd('/') + "/_vti_bin/lists.asmx";

Once complete YOU NEED TO RELEASE THE CONTEXT:

UndoImpersonate();

The above is very important, what ever you execute once you have called Impersonate(); will run under that users credentials.

Hope this Helps.

Cheers.

Answer 6

Is it true that no data is updateable via OLE DB? I think the article you link to is referring specifically to multi-valued ("complex") data types, which are a feature of the Access Data Engine from version Access2007 onwards (i.e. SharePoint uses an Access database). Take a look at this article, which uses DAO (ACEDAO library) to manipulate multi-valued data.