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.)
- 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.
2 responses to “Simple read-write connection of a database table (non-SharePoint) to a DataView Web Part in MOSS 2007”
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?
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.