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.

Adam Zbudniewek
  • 107
  • 2
  • 12
  • 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 – Caius Jard Nov 03 '21 at 08: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. – Thom A Nov 03 '21 at 08:56

2 Answers2

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 )
Elie Asmar
  • 2,995
  • 4
  • 17
  • 30
  • 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. – Adam Zbudniewek Nov 03 '21 at 09:21
  • Correct, I will amend my answer to address this issue – Elie Asmar Nov 03 '21 at 09:46
  • Isn't there better and simpler idea how to do update like this? It's annoying to get workaround like this one – Adam Zbudniewek Nov 03 '21 at 10:27
  • 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. (https://learn.microsoft.com/en-us/dotnet/framework/data/adonet/updating-data-sources-with-dataadapters). Try Adding a primary key to the datatable and then performing the update. It may work – Elie Asmar Nov 03 '21 at 10:38
  • 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 – Adam Zbudniewek Nov 03 '21 at 11:19
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

akash rehmat
  • 3
  • 3
  • That isn't quite true.. – Caius Jard Nov 03 '21 at 09:01
  • 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](/help/how-to-answer). – Community Nov 03 '21 at 10:05