Friday, February 27, 2009

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.



image



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.



image



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.



image



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



image



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:



image



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



image



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.



image



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



image



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.

11 comments:

tjkells said...

I am currently pulling my hair out trying to get a drop down list working on a custom data form in SPD. It populates and renders correctly but I am unable to get it the data binding working correctly so when I click save im getting a null entry for that column. Please please please finish part 3 of this so i can save some of my hair!! If anyone has any pointers feel free to email me.

tkells[at]fimed.com

jabit said...

I agree, please post part 3 of this series, I've been trying to accomplish just this for weeks now! For the life of me I can't figure out how to change the foreign "ID" key of my other table into something readable for the user while still allowing them to update it. Please help!

jabit said...

I agree, please post part 3 of this series, I've been trying to accomplish just this for weeks now! For the life of me I can't figure out how to change the foreign "ID" key of my other table into something readable for the user while still allowing them to update it. Please help!

Rob Wilson, SharePoint MCTS, MCT said...

Thank you both for reading and for your feedback. You got it! I'll finish this off next week.

jabit said...

Thanks, I'll be waiting patiently for it every day. If I can't get this part figured out I may be forced to use some contractor developers.

jabit said...

Thanks, I'll be waiting patiently for it every day. If I can't get this part figured out I may be forced to use some contractor developers.

jabit said...

Do you still think you'll be able to show us how to do it this week? If not, could you at least point me in the right direction? I'm under a deadline coming up fast lately.

Thanks!

Rob Wilson, SharePoint MCTS, MCT said...

jabit,

If you need to go with contract developers, I can recommend an excellent company in Evansvile. :)

Seriously, life happened this week in a big way and I have not been able to finish this. I will soon, but in the meantime click on the "Contact Us" link at the top and it will send me a private email. Give me your contact information and I'll point you in the right direction until the post is finished. There is a workaround for the problem that you have identified. I'll share that with you.

Thanks

McGraw said...

I'm using a sql server database table to create a data view web part that I'm trying to setup for editing. I want to be able to restrict the values entered for the fields to be edited by providing a dropdown list of valid values. I have also not been able to get this to work. It appears that you may be adding a part 3 to your article that might cover this. Have you been able to get this to work and if so do you have any idea when you will publish part 3? Thanks in advance for your help.

Rob Wilson, SharePoint MCTS, MCT said...

Part 3 has been posted. See http://sharepointblog.kellerschroeder.com/2009/10/data-forms-and-sql-server-part-3.html

Marlin7 said...

In the Data View Properties dialog, why would the Edit,Delete item link checkboxes be disabled? They are for me, which means users can't write back to the sql db...