Data view web part to view data from non-SharePoint database (Adventure works)

  • Article

Followed the below steps 

=================

Prepare the SQL account

Made sure our SQL server is up and running,

In the object explorer under security, right click on logins to create an SQL server account that we shall use in the connection.

Note:  Here I used an SQL server account. then clicked on the user and granted sysadmin permissions under server roles

Next I change the default authentication mode of SQL from windows to SQL server and windows.

This is done by right clicking on the instance (SQL server name) under object explorer and then properties. In server properties under security change the server authentication to SQL server and windows authentication mode, clicked okay. then were  able to login using the account we just created using the SPD connection strings.

configure data source connection strings .

 

After the account in SQL is configured

 

Fired up SharePoint designer and opened the page on which we want to place our WebPart. 

After opening the page clicked on the link (click to insert a WebPart) in the desired zone on our page.

In SPD menu click insert - SharePoint controls - DataView

used the link in the empty WebPart to show the Data Source Library if not yet open.

image

 

Back to SPD data source library, clicked connect to database under database connections. in the data source properties dialog box,

 

image

 

 

image

under general tab gave our connection a name, here i left it as default custom query as its the database am going to use. back to the source tab - configure database connection;

server name - [SQL server name]

provider name - Microsoft .NET provider for oledb

authentication - use the details of the SQL account you created.

image

And next, ignored the warning as this was not a production scenario. and finally we were presented with a dropdown box to choose the database to use, i selected  Adventure works and the “Sales Reason table”  and got the data from the Adventure works database.

image

when we browse to the site we get an view as below

image