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

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.