Data Forms and SQL Server, Part 2

There is much excitement about Data Forms in Evansville right now. As the leading SharePoint consulting firm in Evansville, we have implemented a variety of SharePoint-based forms solutions for our clients – based on what was the best solution for them. Every solution has its place: InfoPath Forms, custom web forms, AJAX-based forms and even Silverlight forms. The Data Forms are popular with power users and developers who want to be able to develop forms solutions quickly, but do not have InfoPath available to them.

In this second post of the SharePoint Data Form Web Part and SQL Server series, I will demonstrate how to maintain the FAQ Database displayed in the previous posts. I had a lot of fun trying to make the categories dropdown work just right. I will share more about that in the next post.

Using the same steps as in my previous post on Data Forms and SQL Server, I have configured two database connections in SharePoint Designer – this time to the Faqs and FaqCategories tables instead of a view. First, I will use a Data Form Web Part to add new FAQ Categories. Then I will use a Data Form Web Part to add entries to it.

First, a quick sidebar on Data Forms and Data Views: there is an option to link Data Sources together in SharePoint Designer.

This is similar to using a SQL Server View to join tables together, except that the link in this case exists in SharePoint Designer and not in the database. I explored the possibility of using this in my FAQ maintenance, but SharePoint Designer only allows Data Views against a Linked Source – no Data Forms.

So I have created my FaqCategories Data Source to the Data Source Library and switched to the Data Source Details tab. I highlight the Web Part Zone where I want to insert it – “Zone 2.”

Now, select only the FaqCategoryName field and click on Insert Selected Fields as… to display the Data View and Data Form options. The FaqCategoryID field is managed by the database itself.

I am going to add a Data View Web Part and then change the properties of the view to allow Categories to be added, changed and deleted. I am going to select the Multiple Item View option to insert a form for displaying multiple rows of data.

Next, I am going to click on Data View Properties and switch directly to the Editing tab.

Select all three checkboxes to enable edit, delete and insert. Next, click the OK button at the bottom of the dialog. The Data View now looks like this:

If I look at my page in the browser, it now is a simple web application maintenance program over a SQL Server database.

So this top Data Form can be used for maintaining the categories, but now I will add another Data Form for adding frequently asked questions and associating them with these categories. I am now looking at my Faqs Data Source in the Data Source Details tab. I will select only the FaqQuestion, FaqAnswer and FaqCategoryID and insert them as a New Item Form in a different Web Part Zone on the page. I do not need to select the FaqID column because it is the identity column for this table and automatically increments.

So now the right side of my web part page looks like this:

The problem with this is that the bottom Data Form is not user-friendly because you have to know the exact FaqCategoryID from the database in order to associate a question with a category. So I am going to change the Text Box for FaqCategoryID to a Drop Down List to make it more user-friendly. Watch for the next post – Data Forms and SQL Server, Part 3 – to see how I finish off this simple maintenance application.