Ok so I searched Google/MSDN for DAYS on this problem, and I finally figured out why after piecing a ton of bits of advice together, plus a little ingenuity. A little background, first, however:
In Sharepoint Designer 2007, You have the ability to connect to a SQL Server 2005/2008 database. Whether you do it through Single Sign On (awesome blog post here) or directly through basic connection string (under Data Source Library-> Database Connections and then “Connect to a database” and “Configure Database Connection”, as shown below), it doesn’t matter; just connect successfully to the database.
Make sure in SQL Server you give db_owner or db_reader and db_writer permissions. Also, be sure your table has a Primary Key (id, guid column will work, autoincrement is a good idea too, but this is obvious stuff). Once you connect to the external DB, you should be able to see the Data Source Details (just like you would a list) by going to your Database Connection list and clicking “Show Data.” From there Data Source Details will pop up.
Then, if you want to allow users on Sharepoint to insert/edit/delete to the external Database, select all of the Rows and “Insert Selected Fields As….” and whatever you want; I always do Single Item View. Anyway, next you’ll want to highlight the Data Form Web Part and go to its properties by going to the arrow on the far left (Common Data View Tasks). Once there, click the tab on the far right called “Editing.” Check all boxes necessary including “Synchronize Commands” then click OK. Then, if you have permissions set up properly in SQL Server, you’ll be able to delete and insert, but when you try to edit, just when you submit, it will give you this lovely screen (click to enlarge):
Now, let me tell you, I tried EVERYTHING to remedy this, in SP Designer, SQL SERVER 2008, etc. Nothing doing. Why so? Well it turns out SP Designer, the great product that it is, writes the Update syntax incorrectly; for one reason or another it tries to overwrite the Primary Key setup in your database table. So if you search “UpdateCommand” in the code/split view of SP Designer, you’ll see a big long UPDATE command, depending on how many rows you have. Simply re-write this yourself! For example, I had this:
“UPDATE [test_table] SET [Name] = @Name, [Occuptation] = @Occuptation WHERE [id] = @original_id AND ([Name] = @original_Name or ([Name] IS NULL and @original_Name IS NULL)) AND ([Occuptation] = @original_Occuptation or ([Occuptation] IS NULL and @original_Occuptation IS NULL))”
And simply changed it manually to this:
UPDATE [test_table] SET [Name] = @Name, [Occuptation] = @Occuptation WHERE [id] = @original_id
(in SP Designer below)
This is a simple fix, and depending on how complex or simple you want your update command definitely is up to you and will vary. For me, this was pretty straightfoward. Then it just worked for me! Insert, Delete and Edit worked beautifully then. I’ve heard of hot fixes remedying this issue, but none worked for me so I just manually solved it. Anyway, this was a lot of text, so I’ll bullet point it out the best I can.
TL;DR: SP Designer’s Update command is broken, fix it by manually writing it!
If you have any questions, comments or opinions please comment below! I am so glad I can share this solution with others.
.
