INSERT, DELETE, UPDATE Staments for 2 tables

INSERT, DELETE, UPDATE Staments for 2 tables

• Jeffrey Harmon

  It is an acknowledgement to your query. I am very sorry for not being able to respond as I got too much involved in some other issues. So, please don't feel offended.

  Regarding the topic, I am back to my work and if I find any solution, I will share with yout.

  Regards,

  Abbasi

  
  

• Darren Tao

  Hi Abbasi,
  
  I facing same problem with you, sigh. I had spent more than a month, yet still cannot resolve this problem.
  
  How about you Did you found the solution Mind to share
  
  Thanks a lot!
  
  Best Regards,
  Boon.
  
  
  

• RobSteele

  use the SqlCommandBuilder, usually it will generate the appropriate commands/statements accordingly.

  I would also suggest learning more about SQL/Ms Access and how databases work as it would help you understand a lot more about how things are done and how statements can be generated.

  check this out:

  http://msdn2.microsoft.com/en-us/library/ms171933.aspx

  http://msdn2.microsoft.com/en-us/library/bz9tthwx.aspx

   

  
  
  

• Mateusz Rajca

  Hi,

  Million thanks in advance. If I found any information regarding this topic, I will update here :>

  Good Day!

  Best Regards,

  Boon.

  
  
  

• bbdobuddy

  As for as I understand, SqlCommandBuilder works with DataAdapter, not with TableAdapter.

  Secondly, SqlCommandBuilder does not generate these statements for SQL containing JOINs.

  Thirdly, I want to learn that which I don't know.

   My question is more explained as below:
  I am using the following SQL statement:
  
  SELECT tblDrugs.DrugID AS DrugID, tblDrugs.DrugName,
tblDrugs.PriceID AS PriceID,
tblPrices.*
FROM tblDrugs LEFT JOIN
tblPrices ON tblDrugs.PriceID = tblPrices.PriceID
  
  When my VB2005 program executes the following statement:
  
  Me.taDrugs.Update(Me.myDataSet.tblDrugs)
  
  The above Update method expects the relevant Update statement, which has to be provided manually in this case - because TableAdapter Configuration Wizard does not generate this statement for the SELECT with 2 related tables (joined).
  
  What I want is the Update statement, which can satisfy the Update method of the TableAdapter - taDrugs in the above code.

  
  

• dajm220366

  Abbasi wrote:

  .... Secondly, SqlCommandBuilder does not generate these statements for SQL containing JOINs. .... The above Update method expects the relevant Update statement, which has to be provided manually in this case - because TableAdapter Configuration Wizard does not generate this statement for the SELECT with 2 related tables (joined). What I want is the Update statement, which can satisfy the Update method of the TableAdapter - taDrugs in the above code.

  *Sigh*

  Alright - which table tblDrugs or tblPrices do you want to update with this command You'll have to choose one or the other.

  Or if you want to be able to edit BOTH tables, change your query!!! Use TWO tableadapters, one for each table, and define a relationship between them in the dataset. Add columns to the Prices table that have an expression that pulls the parent drug table fields. Then bind to the prices table. You'll have the same effect as this bothersome joined query.

  
  
  

• AVVIT

  Joined queries should really be used for read only access. You can write an update command but it will only be able to update one of the tables in the query (unless perhaps you use a stored procedure).

  I think the primary two methods are:

  1. Use the joined query to display data and have seperate datatable objects for each table involved in the join. These tables have seperate edit controls bound to them and are used to edit/update the associated records

  2. Fill the associated tables independantly and define a relationship in the dataset. The child table can be extended with columns that use an expression to display parent columns. The fields in the child table will be directly editable/updatable; fields in the parent table will need to be updated from seperate controls bound to the parent.

  
  
  
• INSERT, DELETE, UPDATE Staments for 2 tables