locked
Add values to table column using lookup from another table RRS feed

  • Question

  • I have two tables.

    TableA having columns ID,NAME and COMPANY

    TableB having columns ID,  NAME, COMPANY, BatchNumber and QTY

    I have created a dataset containing ID, NAME, COMPANY, BatchNumber and QTY.

    I used the table adapter to insert values for ID,  BatchNumber and QTY from text box in form. I want the NAME and COMPANY automatically updated in TableB from TableA.

    Could anyone please give some suggestion I program in vb

    Thursday, August 7, 2014 7:46 AM

Answers

  • Typically you don't duplicate data in related tables, but if you are using TableA as a lookup table then the INSERT statement would look something like this:

    INSERT INTO [TableB] ( ID, [Name], Company, BatchNumber, QTY )
    SELECT [@IDParam], [TableA].[Name], [TableA].Company, [@BatchNumberParam], [@QTYParam]
    FROM [TableA]
    WHERE [@IDParam]=[TableA].[ID]

    It isn't clear to me what type of database you are working with so the SQL syntax may vary.

    Paul ~~~~ Microsoft MVP (Visual Basic)


    Monday, August 11, 2014 3:53 PM

All replies

  • Hi,

    I would suggest you write a SQL Query(Assuming you use SQL for back end) such that it fetches the "NAME" and "COMPANY" details from table A and perform an insert in the table B.


    Regards, Ram If you find the answer as helpful, Please vote as helpful.

    • Marked as answer by Ajesh Antony Thursday, August 7, 2014 1:24 PM
    • Unmarked as answer by Ajesh Antony Thursday, August 7, 2014 1:25 PM
    Thursday, August 7, 2014 8:23 AM
  • You need to use a JOIN statement.  The join can either be done in a query to the database or using LINQ to your dataset/datatable.  See webpage below

    http://code.msdn.microsoft.com/LINQ-Join-Operators-dabef4e9 


    jdweng

    Thursday, August 7, 2014 9:49 AM
  • can you give me the SQL query for the same
    Thursday, August 7, 2014 1:57 PM
  • Hi Ajesh,

    I involved VB expert to further look at this issue. I am moving your thread into the Visual Basic Forum for dedicated support. Thanks for your understanding.

    Best Regards,


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place. <br/> Click <a href="http://support.microsoft.com/common/survey.aspx?showpage=1&scid=sw%3Ben%3B3559&theme=tech"> HERE</a> to participate the survey.

    Monday, August 11, 2014 7:28 AM
  • Typically you don't duplicate data in related tables, but if you are using TableA as a lookup table then the INSERT statement would look something like this:

    INSERT INTO [TableB] ( ID, [Name], Company, BatchNumber, QTY )
    SELECT [@IDParam], [TableA].[Name], [TableA].Company, [@BatchNumberParam], [@QTYParam]
    FROM [TableA]
    WHERE [@IDParam]=[TableA].[ID]

    It isn't clear to me what type of database you are working with so the SQL syntax may vary.

    Paul ~~~~ Microsoft MVP (Visual Basic)


    Monday, August 11, 2014 3:53 PM