One of the most common support issues we have is related to connection errors. Setting up connections correctly to SharePoint (or any .NET system for that matter) can be tricky and require some reading. Regardless of which there may be other things that create these errors.

the authentication didn’t succeed from a client that is on a different domain

Credential management

It is important that the parameters in the connectionstring are correct, if you have used authentication=default you may try using the authentication=ntlm instead and remember to check and recheck that your user have access rights to the specified SharePoint Server and Site

Allowing connections

There is something called “loopback errors” that occurs internally in Windows. There are a lot of articles on how to work with this; we prefer the simple approach to solve this by using PowerShell

Execute the following PowerShell command on each SharePoint frontend server.

The Camelot .NET Connector version 2 comes with COM+ support out of the box. This extends the Connector to be used in a large range of applications running on windows, including VB Script, ASP Classic and PHP for IIS. In this article I will give a very simple example that shows how to get started using VB Script. This adds many new possibilities to create maintenance scripts, automated tasks and integrations with enterprise applications with minimal effort.

This blog is based on the VB Script example that comes with the Connector version 2 located under “$installpath\Bendsoft\CamelotSharePointConnector_v2\Examples\VBScript”.

ExecuteScalar (execute any query and returns the first column of the first row)

GetUser (shows the user under which the query is executed)

UploadFile (upload file to document library)

Editing the VB Script file

Simply make a copy of the example script included with the Connector. Change the associated connection string and edit the SQL command to any SELECT command of your choice. I created a small list called “Customers” in our example that contains Name and Email of each customer. The example includes some basic fault handling. The output is by shown in a standard message box using the WScript.Echo method. Therefore, it can be a good idea to set a limit to the number of rows retrieved in this particular example.

We implemented NTLM support with personal or global (static) authentication. This provide safer integrations and personalized content from SharePoint.

Ensure that you use the latest version of the Camelot SharePoint Integration Toolkit when using this version of Joomla Tools.

Using NTLM Authentication

To use this features please ensure your Camelot SharePoint Integration Toolkit is version 2.0.4 or newer.

Static vs. Personal Credentials

Each connection holds which authentication to use. If you simply enable NTLM and leave the NTLM Username and Password fields’ blank personal authentication is used. This will trigger a login form to show whenever the connection is requested

We have gotten a few questions how to download attachments from SharePoint List Items using Camelot PHP Tools. Originally we planned to implement a separate library doing this but recently came to the conclusion that it wasn’t really necessary. Instead we will rely on Camelot XML and the original SQL syntax.

The Camelot SharePoint SQL Commander Web Part is a simple yet powerful query browser for Microsoft SharePoint 2007 and 2010.

Note! This web part does not query the tables in the SQL server; instead it queries the lists and libraries that are enclosed within SharePoint. Read more about this in the Camelot .NET Connector section.

Official web page

FAQ

What does this web part do?

It give you an interface in SharePoint where you can query your SharePoint lists and libraries with sql. You can experiment with queries of modify your list items directly with SQL. This tool is great for for anyone who quickly want to change large amounts of list items or simply learn to know how the lists works in depth.

How do I install it?

Since this is a highly custom built web part you need to install it manually, you will find everything you need to know about the installation here.

What else do I need?

Are there any costs involved?

The web part and the integration toolkit is free to use, they are also open source so you will be able to modify them if you want to. The Camelot .NET Connector is licensed but you can use the trial license to try it, you will find everything you need to know here.

I’m a developer, do I need it?

This web part is great for developers, you can quickly have a look at the stuff under the hood and see how columns are named, what datatype they are using and so on.

I’m an SharePoint administrator, will it improve my life?

Yes, most likely. If you ever tried to delete or rename 10000 records in a list at once you will benefit from using this. Or if you simply want an inline CLI to view and modify your SharePoint Lists and Libraries, it’s very powerful.

Bendsoft Corporation announced today that it will release version 2.0 of its core software product for accessing data in Microsoft SharePoint, the Camelot .NET Connector, in May of 2012. The Camelot .NET Connector 2.0 will include an extensive suite of new features including an updated SQL syntax with JOIN and UNION support. The Camelot .NET Connector 1.1 is the latest version of Bendsoft’s industry-leading ADO.NET driver for connecting to Microsoft SharePoint, designed and built on open standards, enabling developers to query data in Microsoft SharePoint using standard SQL language. In combination with open source toolkits provided by Bendsoft, such as the Camelot SharePoint Integration Toolkit and SharePoint PHP Tools, it is the best solution for integrating SharePoint from virtually any other platform or programming language like PHP, Java or Objective-C.

The Camelot .NET Connector 2.0 will dramatically extend the core capabilities of the connector. This release will offer the first implementation of JOIN and UNION SQL commands that will work between any lists and columns in your SharePoint site. Advantages of this feature will be simplified integration with external applications and extended possibilities for data synchronization, data reporting and analytics inside or outside SharePoint.

Eric Herlitz, Co-Founder and Sales Director commented, “We are very excited to make this announcement, introducing ground-breaking capabilities of the Camelot .NET Connector that will most definitely lower the costs of integration and maintenance in many of our customer’s SharePoint projects.”

Ulf Engstrand, Co-Founder and Systems Architect, adds, “The Camelot .NET Connector in combination with our open source toolkits provide a unique solution that developers need to efficiently integrate SharePoint with other applications. Many of our customers have specifically asked for JOIN support and we feel that this is a natural step to take.”

Existing customers will be offered a free upgrade to Camelot .NET Connector version 2.0 when it becomes available. The new version will not break feature compatibility with version 1.1, but recompilation may be required.

Personal NTLM Credentials

When using personal NTLM Credentials each user/client will have to handle their own authentication, this is automatically handled by the class CamelotSoapClient that outputs a login form when required.

Recently I discovered the SharePoint survey list type. It allows you to quickly create questions that you would like to have people answer and then view graphical summaries of the responses. It can for example be useful for companies to collect consumer opinions about their products or services.

In this article I will show how to integrate SharePoint surveys on your external sites using the Camelot .NET Connector. I will also try to briefly explain the structure of SharePoint survey lists. I have developed a simple survey control based on the ASP.NET Wizard control that walks the user through the questions. In the last step of the wizard, it completes the survey by inserting the results back into the SharePoint survey list. This is a great example of how to creatively use the Camelot .NET Connector and give you more time to work on what matters most – your business.

Requirements

The structure of SharePoint survey lists

When you see a survey list in SharePoint for the first time, it may not be obvious how to work with its content. Each question is represented as a column in the list. The column display name (data column DisplayName) contains the actual question. The column type (data column Type) indicates the type of the answer, e.g. Text, Choice, MultiChoice, Boolean, etc. Depending on the type, the question needs to be presented differently. In addition, we should validate that required questions are answered by the user (data column Required). For simplicity in this example, we will only implement some of the types.

Each inserted item in a survey list represents one participation in the survey. By default, the same SharePoint user can only participate in a survey once. If you want to publish your survey externally and allow non-sharepoint users to participate, you must allow multiple responses. This setting can be changed under survey options.

The order of the questions is another difficult issue when selecting. I found that every survey has a content type with the same name as the list itself. By querying the list with this content type, the columns always come out in the same order as arranged in SharePoint. For example, to select all the answers in correct order from the survey list “Customer Survey”, you should use “SELECT * FROM `Customer Survey`.`Customer Survey`“.

Preparation

In SharePoint, create a new survey list in one of your sites. Add a number of questions to the survey. Make sure to enable multiple responses under survey options as shown below. In this example, I have created a silly “Cats” survey, that evaluates what type of cat person you are.

Building a web user control for presenting surveys

My aim was to create a common control that can be used on several sites so I decided to go with a ASP.NET web user control. This control takes two properties; ListName and ConnectionString. The first gives the name of a survey list that should be displayed and the second the connection string used for connecting to SharePoint.

In Visual Studio 2010, add a new Web User Control (language C#) to your web site and call it SurveyControl.ascx. Ensure that “Place code in separate file” is checked.

SurveyControl.ascx

Replace the content of the markup file (SurveyControl.ascx) with the following lines. It shows a standard ASP.NET Wizard control with a start step that should present the survey and an end step that completes the survey. All the questions will be added as steps in between the start and the end step. The OnFinishButtonClick and OnNextButtonClick events are handled by us. We have also added some css styling and graphics to the control to improve the visual impression. You can of course make the control more general by adding all static texts as inputs to the control.

SurveyControl.ascx.cs

Now its time to implement the code behind our control. In Page_Init we will add the questions to the survey. To get all the questions, we simply execute a SHOW FIELDS query on our list. The columns returned are listed here. We have added a public property named ListName, that should give us the name of the survey list. Using some additional LINQ on our results, we sort out all the questions in the list and iterate over the result set. Each question will be added as new wizard step in our wizard control. The Type column is used to determine in what way the question will be presented. For example, Boolean is represented by a RadioButton control showing YES/NO. For Choice and MultiChoice fields we can use the EnumValues column to retrieve the alternatives. The question itself is shown at the top of the page.

The Camelot .NET Connector requires a connection string for connecting to SharePoint so we need to provide a property for that as well. The connection string is assumed to be stored in the web.config. The following connection string template can be used in most cases but all options are descibed in the connector documentation.

We also need to implement event handlers for the OnFinishButtonClick and OnNextButtonClick events. In NextButtonClicked method we will do some validation of the answers before going to the next question. In this example, we will only validate required questions. However, it would be possible to also validate the actual data entered by the user, such as text length or number range. If a required question is not answered by the user, this method will return a cancel notification to the wizard control.

The FinishButtonClicked method is called when the user finishes the survey. It will gather all the answers and form the INSERT query. Again, each type of question needs to be handled differently. The answers are first added to a dictionary and then the insert statement is built using LINQ.

Creating a sample page

Add a new web page to your project (we are using default.aspx in this example) and replaced the content with the following markup code. On the top of the page, we register our new survey control and later down we add an instance of this control to the page. Remember that we need to set the ListName and ConnectionString properties to initialize the control. The named ConnectionString must also be added to the web.config.

Since we simplified the connection handling in PHP Tools 1.3 and store all connections in the settings file we have also changed the connection syntax for PHP Tools.

The connection settings are now stored in the WcfSettingsObject object set up in settings.php. We removed the compression, connString and sharedKey parameters from the argument array and moved this into the WcfSettingsObject.