SharePoint 2010 - The data source control failed to execute the insert command

I wanted to take a moment to highlight an extremely helpful post, answered by Siraj regarding the intimidating error "The data source failed to execute the insert command" error in SharePoint 2010. The issue surfaces when customizing the ...\newform.aspx of any list and attempting to submit the completed form. Per his writing, this is the diabolical aftershock of implementing Service Pack 2 on the perspective SharePoint 2010 farm. This was absolutely the crux of the trouble in my case, although I had to take one or two more steps after updating the SharePoint:SPDataSource as he notes (I won't spoil it for you by copying his answer - you can visit his notes for the specific change!).

To fully conquer this trouble, I enacted the following:

1. Updated the SharePoint:SPDataSource as noted in Siraj's answer
2. Included ALL required fields in the custom newform.aspx

This second item may not cause as much consternation, but for some forms, I didn't want to populate the "title" field with user input, but rather later with system calculated or concatenated data. For initial releases of SharePoint 2010 up through Service Pack 1, this worked fine to omit "title" from my custom forms. Going forward, this is sadly no longer the case. If you find yourself in similar state of woe, the following will work!

1. Set a default text value for the affected field in the affected List Settings (This MUST be done via the web GUI, as no one seemingly thought it important enough to include in the Designer column edit tools)
2. Add the field to the custom newform.aspx
3. Embed the field within a <div> tag using the following CSS styling to hide it (The exact ID is not important and could be virtually anything, but if left to the system generated Id, it will be something like "ff[Number]{$Pos}"):
• <div style="display:none">
• <SharePoint:FormField runat="server" id="ff6{$Pos}" controlmode="New" fieldname="Title" __designer:bind="{ddwrt:DataBind('i',concat('ff6',$Pos),'Value','ValueChanged','ID',ddwrt:EscapeDelims(string(@ID)),'@Title')}" />
• </div>
4. I tested adding the field as an "ASP:TextBox" to enact code-enforced default text rather than setting the default in the List Settings web GUI, but didn't seem to satisfy the mean warnings

That should do it!

Programmatically Upload to SharePoint Document Library

Programmatically uploading to a SharePoint Document Library seems like a simple thing, given that it's one of the more well-advertised features of SharePoint, but as it turns out, trying to craft your own from scratch is actually a bit of a challenge. I found quite a bit of documentation on how to design a method (C#), but none of them actually worked in the way I needed. All make the assumption that a static file will be uploaded, and none are designed to allow for dynamic selection of a file (Any file that will safely upload into a SharePoint document library). My intended use was to create a custom upload for use within a SharePoint form. I couldn't rely on the built in "list attachments" feature, as this breaks (And still fizzles into a spectacular wreck, I dare say - Not sure why Microsoft hasn't taken the hint from every previous version that has this flaw, which is all of them, but I'm not bitter; much) when you customize the list form.

Regardless, in the end, I had to detach the custom uploader control and method to a separate page, as I have yet been unable to determine the wrapper enveloping the control when used inside a webpart (Using the method and control inside a DataForm Web Part caused the control to be "lost" - no amount of wrapping, based on the Page, form, or webpart ID seemed to isolate the control). 

Here's my method and control that accomplished the goals (ASP.NET, Framework 3.5, C#):

<script runat="server">

public void documentUpload_OnClick(object sender, System.EventArgs e)

{       

String fileToUpload = FileUpload1.PostedFile.FileName;

String sharePointSite = "http://sharepointsite/";

String documentLibraryName = "Library Name";

using (SPSite oSite = new SPSite(sharePointSite))

{

    using (SPWeb oWeb = oSite.OpenWeb())

    {

    if(FileUpload1.HasFile)

    {

    status.Text = "Uploading...";

    FileUpload1.SaveAs(FileUpload1.PostedFile.FileName);

    SPFolder myLibrary = oWeb.Folders[documentLibraryName];

      

        Boolean replaceExistingFiles = true;

        String fileName = System.IO.Path.GetFileName(FileUpload1.FileName);

        FileStream fileStream = File.OpenRead(FileUpload1.PostedFile.FileName);

        SPFile spfile = myLibrary.Files.Add(fileName, fileStream, replaceExistingFiles);

        myLibrary.Update();

        status.Text = "Upload Complete";

        }

    }

  }

}

</script>

<asp:FileUpload runat="server" id="FileUpload1"/>

<asp:Button runat="server" OnClick="documentUpload_OnClick" Text="Upload PDF" id="Button1"/>

<asp:Label runat="server" ID="status" Text="Ready"/>

Connection Timeout vs. Command Timeout

I was doing some work with gridviews and mssql and encountered a very difficult issue that took pointed, dedicated research to resolve. The issue was that the page with the gridview would time out after thirty seconds, even though the timeout property was set in the SQLDATASOURCE connection string to "0", or infinite. Remote queries were set on the SQL server instance to run until complete (Again, as "0"). IIS was set to break the connection at 600 seconds. Plenty of time all around for a query that completes at worst in ~40 seconds.

I dug through forum after forum that insisted that the timeout issue was due to the Connection Timeout property, but no amount of tweaking made any difference; the query still timed out at exactly thirty seconds. I tested setting a two minute threshold of the httpRuntime in web.config, which made no difference. After some creative Googling, I discovered the existance of the CommandTimeout property, which cannot be set from the gridview. The difference between ConnectionTimeout and CommandTimeout is this:

• ConnectionTimeout = maximum time in seconds allowed to make a connection to a datasource
• CommandTimeout = maximum time in seconds allowed to complete a command (Say, a query for example)

See for details -http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlcommand.commandtimeout.aspx

I created the following function, hooked to the OnSelecting handler of the gridview, which finally did the trick and allowed the query to complete (Granted, 5000 seconds is a lot of time and is used as an example - roughly 83 minutes - use your discretion for this value!)

<script runat="server">

protected void SqlDataSourceSelectingEventHandler(object sender, SqlDataSourceSelectingEventArgs e)

{

e.Command.CommandTimeout = 5000; 

}

</script>