Using SQL table in SharePoint List

I have a SQL 2005 table that I would like to use in a SharePoint list.  I would like the viewers to be able to edit the data in the SQL table from Sharepoint.
sproctor722Asked:
Who is Participating?
 
zephyr_hex (Megan)DeveloperCommented:
that sounds like a problem with your primary key.
make sure you have one defined in your SQL table
make sure you are including the primary key column in your database connection query in sharepoint designer
and make sure you are NOT adding that primary key field to your web part.  it must be in the query, but can not be displayed in the web part if you want the user to be able to edit.

also, it might be worth checking the code in designer for the EDIT command, and see if the SQL looks right.
0
 
zephyr_hex (Megan)DeveloperCommented:
you can do this using Data Form web part.

it's similar to Data View web part, except you allow write permissions.  to see how to enable write, pay attention to the section called "Creating a Read/Write Data View"
http://www.lcbridge.nl/vision/2009/dvwp.htm

one other note:  in order to do this, you MUST have a primary key in your table, and you can NOT allow users to write to that field.  in some cases, the easiest solution is to use a Identity column and make it the primary key.  the column must be included in the query for the web part, but don't add the field to the page itself.
0
 
sproctor722Author Commented:
The article was very helpful.  Is there any way to create a SharePoint list from this data?  I would like to be able to control who has access to edit the data.
0
 
zephyr_hex (Megan)DeveloperCommented:
there isn't a direct way to put the data in a sharepoint list because the data reside in the sharepoint database.  if you want to put it in the database, you'd need to use sharepoint object model to properly write the data (i.e.  you'd write custom code to insert the data in the sharepoint db).

rather than adding that layer of complexity, what i've done in the past is to control access using sharepoint permissions on a page.  so, for example, i put the web part on a page and configure the page permissions in sharepoint.

i did a quick google, and it seems like this might be another approach:
http://raiumair.wordpress.com/2009/08/17/security-trim-contents-of-a-data-view-web-part/
0
 
sproctor722Author Commented:
When I attempt to edit and save the data I get the following error:

The data source control failed to execute the update command.  
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.