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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SharePoint

From novice to tech pro — start learning today.