Data Forms and SQL Server, Part 1

In my previous series of posts, I demonstrated how to create a FAQ page on SharePoint using two SharePoint lists. In this post I will demonstrate creating a FAQ page on SharePoint using SQL Server and the Data View Web Part. The following post will demonstrate how to maintain the SQL Server table using a Data Form Web Part. It should be noted that I often use the terms “Data Form Web Part” and “Data View Web Part” interchangeably. A Data Form Web Part is a Windows SharePoint Services 3.0/Moss 2007 phenomenon while a Data View Web Part was introduced in the previous versions. Technically, however, it could be stated that a Data View Web Part will allow one to view data from a datasource in read-only mode, while a Data Form Web Part will allow the data to be edited. With that in mind, this post will demonstrate a Data View Web Part and the following one a Data Form Web Part.

First, I should mention that I have already created two tables in a SQL Server database. One table is a FAQ Questions table and the other is a FAQ Categories. They are related via FAQCategoryID.

Also, to keep things simple I have set up SQL Server Authentication on my database, SharePointDemoDatabase. In addition, I have created a view—vFaqs—that contains FaqCategoryName, FaqQuestion and FaqAnswer.

Begin by creating a blank SharePoint site and opening its default page in SharePoint Designer. Refer to the previous posts if you need assistance. Once the site has been opened in SharePoint Designer, click Data View --> Manage Data Sources from the top menu. In the Data Source Library window, click on Database Connections to add a connection to our SQL Server database.

In the dialog that appears, click on Configure Database Connection.

Enter the required information including the server name, username and password. Leave the provider name at the default.

Because I am using SQL Server authentication, I am warned about other authors of this web site seeing the contents of the connection string. For the purpose of keeping this exercise simple, click OK to accept the warning and continue. Note that in a production environment you should use Windows Authentication with integrated security for this. The next dialog allows you to select your database in a drop-down list and then to choose the table or view that you want to display.

Select the vFaqs view. From this point on, things are just as they were in the previous posts. You may set your sorting and grouping options (I sorted mine by FaqCategoryName).

After clicking OK, click the Show Data button to see the Data Source Details. Highlight your Web Part Zone on the web page and choose Insert Selected Field as… --> Multiple Item View from the Data Source Details tab.

When the Data View is inserted into the Web Part Zone, choose the Sort and Group option in order to change the grouping options.

Be sure to check “Show group header” so that the expand/collapse icon appears in your view.

You could optionally add a secondary sort order of FaqQuestion to assist in finding questions more quickly. One final piece of clean-up that can be done is to the FaqCategoryName label that appears on the ensuing Data List. To do that, highlight the text in the label in SharePoint Designer and click the delete key. Then highlight the colon that appears just to the right of it and remove it the same way. Save the page and view it in a browser. Remove the FaqCategoryName from the list of fields to display since it is already a group field. Remove the header rows and add a title to the web part.

Assuming you have data entered, your page should look something like this:

In the next post I will demonstrate how to use the Data Form Web Part to create a user-friendly page for maintaining this list in SQL Server.

P.S. I want to add that our thoughts and prayers are with the thousands affected this week by the winter storm in Southern Indiana, Southern Illinois and Western Kentucky...and with the crews who are working tirelessly to restore their power and water.

Creating Data Forms in SharePoint Designer, Part 4: Sorting and Grouping

In the previous post I showed you how to add a Multiple Item View Data Form Web Part to your SharePoint page. Next, I will show you how to add sorting and grouping to it to make it more user-friendly. I have added some more questions to the Frequently Asked Questions list. The list currently looks like this:

I will open the web page (default.aspx) in SharePoint Designer and then click on the DataFormWebPart so that it is highlighted. You will notice the expansion arrow appear to the right:

When I click on the arrow, a menu appears for Common Data View Tasks:

I select the Sort and Group task and I am presented with the Sort and Group options window. Because I want to group by Category, I must add it first to my sort order list and then select the checkbox that reads “Show group header” with the default option of “Expand group by default”. The options now look like this:

When I click OK, I return to the Common Data View Tasks menu. From here I can set additional options such as how many items to display per page and other formatting options. I will leave everything else alone for now and save my web page. When I preview it in my browser it now looks like this:

In a future post I will demonstrate how to use the Data Form Web Part with a SQL Server database instead of a SharePoint list. I will also demonstrate how to use the Data Form Web Part to collect information from a user. Is there something in particular you would like to see demonstrated? Leave a comment and let me know.