Simple read-write connection of a database table (non-SharePoint) to a DataView Web Part in MOSS 2007


Test this out/play with it in a test area. Don’t do this in production. Duh.

Required:

  • SQL Server 2005
  • SharePoint v3 (I don’t honestly know, but I think just WSS 3.0 w/o MOSS 2007)
  • SharePoint Designer 2007
  • A SQL account with db_datareader and db_datawriter permissions on the database in question, and the account’s password
  • A table in the database with a primary key
  • Sufficient SharePoint rights to use SharePoint Designer to create DataViews on Web Part Pages somewhere

Procedure:
(Italics are steps added after MS Support helped me out with this.)

  1. Make sure you have the SQL account name and password at the ready (for this example, account is sqlmossupdatetest)
  2. Make sure you know the SQL Server name/instance name (if any – for this example, the server name is DEV1 and the instance name is MOSS)
  3. Make sure that the table in the database has an autoincrementing primary key (Column settings for this are related to Identity properties w/in SQL Management Studio).
  4. Create a new Web Part Page in one of your document libraries. Exit Edit Mode.
  5. Open the site where your Web Part Page resides in SharePoint Designer 2007.
  6. Next, open up the Web Part Page you just created by browsing to it and double-clicking it in SharePoint Designer.
  7. In SharePoint Designer’s default upper right toolbox pane, choose the Data Source Library tab.
  8. Under the Database Connections group, click the link for Connect to a database…
  9. Click the Configure Database Connection… button.
  10. In the server name textbox, type the server name, or server name\instance name if you’ve got an instance name (i.e. in my case, it was DEV1\MOSS).
  11. Keep Microsoft .NET Framework Data Provider for SQL Server for the Provder Name.
  12. For Authentication choose (not very secure) the first radio button, Save this username and password in the data connection, and enter the username and password you have in the Database.
  13. Click Next. If you get an alert about how other authors can see the user/password information, click OK.
  14. Choose the proper Database and Table for your Data View.
  15. Click Finish. Then click OK.
  16. Click and drag the new Database Connection to a Web Part Zone in your Web Part Page.
  17. Use the right menu-arrow on the Data View Web Part to bring up the menu items for the web part and choose Edit Columns… .
  18. Remove the Identity/PK column from the Display section. Click OK. 
  19. Use the right menu-arrow on the Data View Web Part to bring up the various menu items for the web part and choose Data View Properties… .
  20. Choose the Editing tab and enable the checkboxes for Show edit item links, Show delete item links & Show insert item links.
  21. Save the page in SharePoint Designer and click Yes if prompted about changing the site definition.
  22. Close SharePoint Designer and refresh the Web Part Page in IE. Be sure to populate your ID field with a unique value. Obviously this cries out for more work to actually dynamically create a unique key, etc.

Update: Oddly enough, while T-SQL INSERTs & DELETEs seem to work, UPDATEs don’t, so I’m now digging further and seeing whether I can write custom SQL in the connection definition to do the UPDATE properly.

Update 2: A support ticket (via my employer, a Microsoft Gold Manged Regional Partner) has been opened about the UPDATEs issue. I’ll report back when we have a fix.

Update 3: The support issue is closed. The issue with updates was caused by trying to edit the PK value. To keep the web part from trying to edit/update the PK value, you remove it from sight and then it doesn’t bother with trying to change it on update, so the update is successful. I added a couple of steps to the procedure to do that. Also, I forgot to say that I brought up the question of documentation for custom SQL and apparently there is no such documentation currently at MS for the Parameters features.


2 responses to “Simple read-write connection of a database table (non-SharePoint) to a DataView Web Part in MOSS 2007”

  1. Have you found out any additional information about SQL data views in SharePoint? I have a SQL table with an int PK field. When I include the PK field in the view, UPDATE fails, although INSERT and DELETE work fine. When I exclude the PK field from the view, I cannot enable the edit/insert/delete functionality in the data view properties, leading me to believe I need to write custom SQL commands for the data source. Any thoughts?

  2. Eric, The support person and my own experience lead me to believe that any time you use a DataView Web Part, any field that is displayed by the Web Part is then something the DVWP attempts to update when you use the update operation. Since you can’t update a PK field once created (because that would screw up indexing, etc.), you can’t show the PK field as part of the DVWP when you plan to use it for updates.

    I didn’t have your issue with not being able to enable insert/delete without the PK. I haven’t tried it in a while (and can’t right now because my new workplace isn’t up to 2007 yet), but I seem to remember that you can include the PK in the retreived fields but not the display fields.

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.