0

I want is to update 2 fields: p.FlagaWaznosci and p.Notatka

My select looks like:

Select  DISTINCT p.id,p.Model_Number,p.Product_Name,p.Website_Link,p.Entry_date,p.LastUpdate_date,p.PrzydzialRozmiarow_ID,p.FlagaWaznosci,p.Notatka,pr.NazwaRozmiarowki,wd.LINK_StockX 
from Products p with(nolock)
left outer  join Widok_Model_Sklep_Stockx_Linki wd with(nolock) on wd.Product_ID = p.id 
left outer join PrzydzialRozmiarow pr with(nolock) on pr.id = p.PrzydzialRozmiarow_ID 
inner join Shops s with(nolock) on s.ID = p.Shop_ID

There is just outer joins to get correct data that I need to be displayed in gridview. And now when values p.FlagaWaznosci or p.Notatka is changed I want to save update in my database.

I try to use

//loads dataand fill to gridview
 DataTable WszystkieProduktyDlaDanegoSklepu;
 SqlDataAdapter sda555123 = new SqlDataAdapter("here is my select", conn123);
 sda555123.Fill(WszystkieProduktyDlaDanegoSklepu);
 
 //later update table Prooducts and save changed on p.Notatka and p.FlagaWaznosci
 cmdbl = new SqlCommandBuilder(sda555123);
 cmdbl.ConflictOption = ConflictOption.OverwriteChanges;
 sda555123.Update(WszystkieProduktyDlaDanegoSklepu);

But this way I have error

enter image description here

So I searched a lot and found: I have to write own CommandUpdate.

So ... sda555123.UpdateCommand and I don't have idea how can I write own update for it in update command.

The update in SQL Server should looks like:

Update Products
set FlagaWaznosci = @Flagawaznosci from my sda555123,
Notatka = @Notatka from my sda555123 
where id = @ p.ID from my sda555123

How my command update should looks like here?

EDIT 1 :

i try added : WszystkieProduktyDlaDanegoSklepu.PrimaryKey = new DataColumn[] { WszystkieProduktyDlaDanegoSklepu.Columns["id"] } but nothing . Still this error.

Improve this question
2
  • A command builder won't understand how to write an update from ; you'll have to write it manually and plumb it up to your datatable yourself. It's probably time to switch your data access layer to something newer (Entity Framework) that can maintain a graph of related objects, track changes to all of them and save them individually Commented Nov 3, 2021 at 8:21
  • If there a reason you're spamming the NOLOCK hint in your first query? if you really need to use NOLOCK against every table (doubtful) you should be changing the isolation level of the transaction. NOLOCK isn't a magic "go faster" keyword. Commented Nov 3, 2021 at 8:56

2 Answers 2

Reset to default
1

I would solve the problem by changing the approach instead of mutating the update command of the SqlDataAdapter.

Given that Products.id in your query is unique within the result set:

1- Create a temporary table (local or global), having its columns same as the result of the query with id as primary key.

2- Insert data into the temporary table using your select statement.

3- DataAdatper.selectQuery.commandText is set to "select * from TempTable"

4- The update command is now based on a simple select statement, consequently any change in the datagridview/datatable can be updated to the temptable using dataadapter.update(datatable)

5- As for the final database update, you could use the below statement

Update Prd
set Prd.FlagaWaznosci = TempTable.FlagaWaznosci ,Prd.Notatka = TempTable.Notatka  etc.. all the fields that need to be updated  
from my Products as Prd 
Inner Join TempTable on TempTable.id = Prd.id

Note that the update in (5) will affect all rows, even unchanged ones. To address this issue you can proceed as below

1- Save changed ids in a list.

List<string> lst = new List<string>();
foreach(DataRow dr in datatable.GetChanges(DataRowState.Modified))
{
 lst.add(dr["id"].ToString());
}

2- Convert your list to a string value to be concatenated with the query in (5)

 String strchange = String.Join(",",lst); //will give you id1,id2,...
 //The update query becomes
 Update Prd
 set Prd.FlagaWaznosci = TempTable.FlagaWaznosci ,Prd.Notatka = 
 TempTable.Notatka  etc.. all the fields that need to be updated  
 from my Products as Prd 
 Inner Join TempTable on TempTable.id = Prd.id
 Where Prd.id In ( strchange )
Improve this answer
Sign up to request clarification or add additional context in comments.

5 Comments

It's a good idea . But the main problem in this case is : you update everything. - not only changed data. when you only update 1 row of 1000 - the update will be places on 1000 rows . And when second user use it paraller - there will be update and save his work.
Correct, I will amend my answer to address this issue
Isn't there better and simpler idea how to do update like this? It's annoying to get workaround like this one
If the SelectCommand returns the results of an OUTER JOIN, the DataAdapter will not set a PrimaryKey value for the resulting DataTable. You must define the PrimaryKey yourself to ensure that duplicate rows are resolved correctly. (learn.microsoft.com/en-us/dotnet/framework/data/adonet/…). Try Adding a primary key to the datatable and then performing the update. It may work
Nope it don't works . I added : WszystkieProduktyDlaDanegoSklepu.PrimaryKey = new DataColumn[] { WszystkieProduktyDlaDanegoSklepu.Columns["id"] } , and still the same error . It's so stupid because 2 last values it's never modified and i want only display them in table. And i can't figure it out
0

Kindly update your tables separately because in join you just seen two or more than two tables into one table form . but you cant do any crud operation on

Improve this answer

2 Comments

That isn't quite true..
As it’s currently written, your answer is unclear. Please edit to add additional details that will help others understand how this addresses the question asked. You can find more information on how to write good answers in the help center.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.