DataView Webpart, SharePoint Designer & Sql express 2005

This is a step by step guide that i eventually used after hours of googling and binging coupled with anger at my screen for showing me endless generic errors, just maybe someone finds it useful

Task
Getting a dataview webpart to display data from sql 2005 express on a sharepoint page
tools
-running sharepoint site (WSS 3.0)
-sharepoint designer (SPD) 2007
-sql express 2005 (with sample databases & management studio express )
note that all the above are free downloads from microsoft :)
Get the webpart in place
Fire up your sharepoint designer and open the page on which you want to place your webpart. its good practice to use a fresh webpart page from which the webpart can be exported and later imported to any desired part of the site.
After opening the page click on the link (click to insert a webpart) in the desired zone on your page.
In SPD menu click insert - sharepoint controls - dataview
use the link in the empty webpart to show the Data Source Library if not yet open.


Prepare sql account
Make sure your sql express is up snd 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 use an sql server account. d-click on the user and grant sysadmin permissions under server roles



next we are to chnage the default authentication mode of sql from windows to sql server and windows.
this is done by right clicking on the instance ([computername]\sqlexpress) under object explorer and then properties. In server properties under security change the server authentication to sql server and windows authentication mode, click okay. now we should be able to login using the account we just created using the SPD connection strings.

configure data source connection strings
back to SPD data source library, click connect to database under database connections. in the datasoure properties dialaog box, under general tab give your connection a name, here i leave it as default custom query as its the database am going to use. back to the source tab - configure database connection;
server name - [comutername]\sqlexpress
provider name - ms .net provider for oledb
authentication - use the details of the sql account you created.
and next, ignore the warning as this aint a producton scenario. and finally you are presented with a dropdown box to chose the databse to use, i will go for Adventure works and the contacts table. WAIT! DONT select any table .



NB: if u select a table and finish the wizard, then SPD will make a select statement of the form SELECT * FROM [Contact] and finally when you try to show the data you end up with the error below
"the server returned a non-specific error when trying to get data from the data source. check the format and the content of your query and try again."

sql server uses schemas to identify database tables and if u check the tables in studio you realise the contact table is under the person schema
and so the statements are expected to follow this structure
[ select * from person.contact]

so instead of selecting a table, we specify custom statements and edit the alreday existing commands to follow the schema structure.


click okay and u are well to go. in the connection if u click on show data it should bring up the fields which u can add to the dataview either as single item or multiple item.
here i added as multiple item view



and finally on my sharepoint page



there are many modifications that can be made to this wepbart like having links and the ability to modify which i will attempt in my next post.