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.
2 comments:
Hi Rob,Nice atrical.
I am working on Dataview webpart and Sql Server.in each there is connection string but now i want to deploy it on other enviroment but i dont know how there is dirty way to do by changing connection string in each page.so is there way i can place connection string in Variable and use it in dataview webpart ?
Thanks for reading! What you can do is edit the code for your pages to get the connection string from the web.config. Then in the web.config for each of your environments, point to the appropriate database. Feel free to use the contact form to send an email if you need more specifics.
Post a Comment