At long last, I am going to finish this series on Data Forms and SQL Server. I do want to thank all of you who responded asking for the final post. I hope you will find it to be helpful. Let me start by saying that I am running SharePoint Designer 2007 with Service Pack 2. If you do not have the latest service pack, results may vary.
Continuing the FAQ exercise, I have two tables in my SQL Server database: Faqs and FaqCategories. I have created a Data Source for Faqs in SharePoint Designer and selected three columns: FaqQuestion, FaqAnswer and FaqCategoryID. Have opened the default.aspx page for my FAQ SharePoint site.
Be sure you have the appropriate Web Part Zone selected on your canvas, and choose Insert Selected Field as… –> New Item Form. Note that I did not include the FaqID column because it is an identity column in SQL Server and will be incremented automatically.
Now your canvas should look like this:
The trouble is that the FaqCategoryID column is useless to us. It is a text box right now. We want to make it a drop-down with a friendly name using the foreign key relationship to the FaqCategories table. Click on the text box to the right of the “FaqCategoryID:” label and delete it. We will add it back manually.
The first thing we need to do is drag a Data View DropDownList control from the Data View Controls section of the Toolbox onto the canvas where we just removed the FaqCategoryID text box.
The control will initially be unbound.
Then create a new Data Source for our FaqCategories table. Create the Data Source and select both the field you want to display in the drop down (FaqCategoryName), and the field you want to store in the database (FaqCategoryID).
Now go back to the design canvas and position your cursor just after the unbound control you just added. Click on the FaqCategories Data Source on the right, then select Insert Data Source Control.
The end result should be similar to this (names may vary):
Now if you click the arrow that appears when you hover over the Unbound control, you will be presented with a popup dialog. Choose the Data Fields… option.
Select the values as shown below:
“Select a data field to save values to:” is the field in Faqs that will hold the Category ID foreign key.
The “Select a data source:” section identifies the data source you just added over the FaqCategories table, the friendly name you want to display in the drop down, and the ID column value you want to supply to the Faqs table.
Save your work and open up the page in a web browser. Your web part should appear on the page similar to this:
Each time I fill in the blanks and hit the Save button, a new row is added to the Faqs table in SQL Server with the appropriate ID for the FaqCategoryID column.
Remember to give your web part a friendly title and to change the labels to something more meaningful.
