Test this out/play with it in a test area. Donâ€™t do this in production. Duh.
- 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
(Italics are steps added after MS Support helped me out with this.)
- Make sure you have the SQL account name and password at the ready (for this example, account is sqlmossupdatetest)
- 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)
- 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).
- Create a new Web Part Page in one of your document libraries. Exit Edit Mode.
- Open the site where your Web Part Page resides in SharePoint Designer 2007.
- Next, open up the Web Part Page you just created by browsing to it and double-clicking it in SharePoint Designer.
- In SharePoint Designerâ€™s default upper right toolbox pane, choose the Data Source Library tab.
- Under the Database Connections group, click the link for Connect to a databaseâ€¦
- Click the Configure Database Connectionâ€¦ button.
- 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).
- Keep Microsoft .NET Framework Data Provider for SQL Server for the Provder Name.
- 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.
- Click Next. If you get an alert about how other authors can see the user/password information, click OK.
- Choose the proper Database and Table for your Data View.
- Click Finish. Then click OK.
- Click and drag the new Database Connection to a Web Part Zone in your Web Part Page.
- 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… .
- Remove the Identity/PK column fromÂ the Display section. Click OK.Â
- 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â€¦ .
- Choose the Editing tab and enable the checkboxes for Show edit item links, Show delete item links & Show insert item links.
- Save the page in SharePoint Designer and click Yes if prompted about changing the site definition.
- 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.