Data view web part errors

Question

Hi,

I am using Data view web part to display my SQL table on my site. I have also enabled the EDIT and DELETE links ..which r meant to Update and Delete records.

When i do an EDIT, it shows me an error msg " The Data source control failed to execute the Update command "

Any suggestions ?...

Answer 1

I got this one fixed.

 Open  Sharepoint Designer >> Current Data Source

Right click on each Field names ( row names ) in the right hand side task pane , and Select
" Insert as text ".

Do a "Refresh Data Source" then.

Answer 2

Hi neeraj,

 

I have followed your steps but its still not working.

Any suggestions ?

 

Regards

Samson

Answer 3

Hi Samson,

Guess what, i also encountered the same problem again, even though i did select
" Insert as Text "

The funny thing is that it works some times, and sometimes even i follow the same procedure as i mentioned in my above thread it doesnt work.

But this is what i have observed....The Update works successfully, if you do an "Insert as Text" as i have mentioned in my above thread,  provided Primary Key field is not included / listed in the form (from which you are doing an Update/Edit) .

If you include all other fields, except your PK, then Update shuld work.

However, this wuz not the case when i first worked on it...i wuz able to update PK also....but now the moment i include PK in my form, it shows me " The update action failed" msg

I am working on it..will post soon..if i figure out a standardized solution.

Answer 4

Hi Neeraj,

 

Thanks for the reply. My table has PK(int), Name(nvarchar), Date(datatetime), Duration(int)datatypes.

After  primary key is excluded from form

 Im able to insert new records and the same records im able to edit and delete not the ones which are already there in the database.

 

Answer 5

Samson,

Did u figure out a way to Update the form with the Primary Key included ?..Is there any solution to tht prb ?

Neeraj

Answer 6

Hi Neeraj,

 

Im having problems excluding the primary key as well... If there are any null fields, Im not able to update. Im trying to figure out and I'll let you know abt it

 

Samson

Answer 7

Samson,...

Any update from ur side ..regarding doing an Update with the Primary Key included ?

Neeraj

Answer 8

any new info on this problem? I ahave a simple table 3 fields insert and delete work fine Update gives error:The data source control failed to execute the update command.

Answer 9

Hi,

 

I've been wrestling with this problem for awhile.  I tried eliminating the primary key and it still does not work. I still get the "The data source control failed to execute the update command."  message

 

Microsoft!!! Help!!! We're wasting too much time on these trivial issues.  How about throwing us a bone and telling us what we're doing wrong.  Also, how about some decent error messages to help us.

 

Mike

Answer 10

Mike,

 

I totally agree with u..this is such  a pain in the neck....I myself wasted 3-4 days on this ***...I dunno if Microsoft is goin to do anything abt this.

 

Well...as for ur problem..did u click on " Insert as Text " option from Sharepoint Designer...it still doesnt work ?

 

 

Answer 11

I tried all suggestions and ended up writing an aspx page to update the table and linked to it in  WSS 3.0.  The client was happy with the solution I was not. I am starting to think it is a permissions problem (possibly between Sharepoint Designer and Sharepoint) because I wrote my own update query and tried it in Sharepoint Designer and it did not work (same error) and used that same update query in the aspx page with no problems.

Answer 12

Hello,

are there any solution to this problem.

I have just now this problem and i work for two days at this?

I hope somebody can help me!!!!

 

Thanks,

Horst

Answer 13

I had the same problem “The data source control failed to execute the update command”, but after in depth research into the code, I found that the update statement is weird. I modified the update statement, & everything worked just fine after that.

Here is what you need to do to fix that problem:

1. Find the UpdateCommand on the page

You will find a statement that looks like this (UpdateCommand="UPDATE [Table1] SET [Col1] = @Col1, [Col2] = @Col2 WHERE [Col1] = @original_Col1 AND [Col1] = @original_Col1 AND [Col2] = @original_Col2" …

2. Replace the UpdateCommand with this "UpdateCommand="UPDATE [Table1] SET [Col1] = @Col1, [Col2] = @Col2 WHERE [Col_PrimaryKey Or Col1] = @original_PrimaryKey "

* Make sure you don't update the Primary Key; you are NOT suppose to update that column. If you do want to update the Primary Key; then, you are better off deleting that row from DB and create new one

3. After you Save the page, make sure the UpdateCommand you modified is NOT changed back to the original one (step 1).

It worked for me like a charm, Enjoy :)

 

 

Answer 14

SPD Data View web part questions go to the forum for SPD. -> Design/Customization.

Moving this

---

WSS FAQ sites: WSS 2.0: http://wssv2faq.mindsharp.com WSS 3.0 and MOSS 2007: http://wssv3faq.mindsharp.com
Total list of WSS 3.0 and MOSS 2007 Books (including foreign language titles) http://wssv3faq.mindsharp.com/Lists/v3%20WSS%20FAQ/V%20Books.aspx

Answer 15

Walid, it took me a bit of playing with this as it would not allow me to edit the file directly, but the resolution in my case was similar to what you described.

1. select the data view web part
2. open data source
3. click "configure database connection"
4. make sure authentication setup properly and click next
5. click "specify custom select, update, insert, and delete commands" and hit finish
6. click the update tab and hit edit command
7. delete everything past "WHERE [ID] = @original_ID"

** This didn't work for me before applying SP2 for Office 2007.

MS, fix this already!

Your dataview web part is NOT WORKING.

Answer 16

I too got the same problem. Finally i figured the problem to be with the update and insert statements.

For update command i ensured the reference of primary Key columns in the where clause.

For insert i ensured that all not null columns are included.

* For delete command even though my command for the dataview webpart is correct i analysed it failed because of a

   foriegn key violation.

After ensuring the above my dataview webpart works perfect.

Answer 17

Editing the UPDATE and not showing/editing the primary key makes a lot of sense, except...

In my page, there are embedded INSERT, SELECT, and DELETE, but no UPDATE !

Answer 18

If the above mention solution doesnt work there is one more step need to be performed after that

1. Find the UpdateCommand on the page

You will find a statement that looks like this (UpdateCommand="UPDATE [Table1] SET [Col1] = @Col1, [Col2] = @Col2 WHERE [Col1] = @original_Col1 AND [Col1] = @original_Col1 AND [Col2] = @original_Col2" …

2. Replace the UpdateCommand with this "UpdateCommand="UPDATE [Table1] SET [Col1] = @Col1, [Col2] = @Col2 WHERE [Col_PrimaryKey Or Col1] = @original_PrimaryKey "

* Make sure you don't update the Primary Key; you are NOT suppose to update that column. If you do want to update the Primary Key; then, you are better off deleting that row from DB and create new one

3. Delete all the parameters in the <UpdateParameters> tag where they start with original_ except the one with Primary Key and Viceversa for Primary key remove the parameters for the same key

for example if CustomerId is the Primary Key
- Keep <asp:parameter id='original_CustomerID'></asp:parameter>
remove other original parameters
- Remove <asp:parameter id='CustomerID'></asp:parameter>
Keep other parameters which contain modified values to be updates

4. make sure all the above mentioned changes are saved properly

This should work

one more thing some times foreign key contraints also create such kinda issues so beware..


hope this helps

it worked for me .. thank god