Data View web part in SharePoint 2007

Open the WSS site in SharePoint Designer 2007 where you want to use the Data View web part. As usual, I have opened “Test Site Collection” in SharePoint Designer to add Data View web part.

Select File > New > Create from Master Page… to create a new page for Data View web part.

Select appropriate Master Page for creating a new page. I want default look and feel of WSS site so I have used Default Master Page.

The new page should look like this,

Next step is to create a web part zone where you can add the Data View web part. Select “PlaceHolderMain (Master)” and click “>” arrow on right hand side top corner. Click “Create Custom Content” under “Common Content Tasks” menu as shown below,

Now you should be able to click inside the “PlaceHolderMain (Custom)” and add a new web part zone. Select Insert > SharePoint Controls > Web Part Zone

This should add a new web part zone, which we will use to add the Data View web part. Select “Data Source Library” tab on the right hand side panel of SharePoint Designer and click “Connect to a databases…” link.

This will open “Data Source Properties” window. Click “Configure Database Connection” button.

There are multiple ways of connecting to SQL Server

1. Saving username and password as clear text in the data connection.

2. Using Single Sign-On authentication

3. Providing custom connection string

I have used the first method – Saving username and password as clear text in the data connection. I have created a new user called “DBAdmin”, which will work with SQL Server authentication as shown below.

Note: You may need to configure database connection with Single Sign-On authentication, if you want to use Windows authentication. You may provide custom connection string but that may require stored procedure to query the database instead of custom query. I have seen few blog posts regarding the issues in using custom query with custom connection string and you can query database only with stored procedures.

http://www.u2u.info/Blogs/karine/Lists/Posts/Post.aspx?ID=4

Provide the name of the database server, username and password by selecting “Save this username and password in the data connection” under Authentication.

Click “OK” on warning message window – “The selected authentication option saves the username and password as clear text in the data connection. Other authors of this Web site can access this information.”

Select appropriate database and table to bring the data in Data View web part. I have chosen “Emp” table of “test” database as shown below.

You may select only certain fields instead of all by clicking “Fields…” button. You may also use “Filter…” and “Sort…” buttons for customizing your query. You may provide appropriate name to this data connection in “General” tab. I have named it “Employee Details”. Click “OK” after you are done.

Click “Employee Details” under Database Connections in Data Source Library tab and click “Show Data”.

This will display data under “Data Source Details” tab. Click “Insert Selected Fields as…” button and select appropriate view. I have selected “Multiple Item View”.

This will add the Data View web part to the web part zone. Select Data View web part and select appropriate operation under “Common Data View Tasks” menu if you want to customize it further.

Finally click “Save” icon on the top of SharePoint Designer and save this page to the desired location. I stored it as “EmpDetails.aspx” at http://sharepoint-dev.corp.netapp.com/sites/SCTest/EmpDetails.aspx.

Following screen capture shows EmpDetails.aspx in SharePoint Designer 2007.

Here is the screen capture after browsing EmpDetails.aspx page which shows Data View web part with the data pulled from SQL Server 2005.