none
how to update two table with single query in ms sql RRS feed

  • Question

  • Dear All

    Please check the attach picture 

    i have two tables one is cashpayment table for details and other is linked with datagridview for entries as show in attachment.both tables has relationship in ms sql. 

    i want to update both table with single query and also i need entries must show in datagridview as follow

    account number                                      description                                            dabit              credit

    133                                             cash in hand                                                   2000

                                                                  expanses                                                                     2000

    nattation :             entry passed against expanses

    i need this entry must pass in datagridview or any other way will be welcome .

    many thanks 


    • Edited by Omi4u Thursday, May 16, 2019 9:01 AM
    Thursday, May 16, 2019 9:01 AM

Answers

  • From what I see in your screenshot, I would keep it pretty much how it is now as shown below. Each row represents a transaction in the grid and a button to provide opening another window to expand a transaction's details.

    Note the difference between available cash and current balance, I'm thinking of a common case of an account there there may be a pending transaction where if there was a pending transaction for debit for say 100 dollars this would be the difference between available case and current balance.


    Please remember to mark the replies as answers if they help and unmarked them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.

    NuGet BaseConnectionLibrary for database connections.

    StackOverFlow
    profile for Karen Payne on Stack Exchange


    Friday, May 17, 2019 1:05 PM
    Moderator

All replies

  • You can not update more than one table at a time. You best option is to create a transaction and in the transaction do multiple updates, commit the updates unless there is an error then if there are failures do a rollback. In short you can only have one base table in a update statement.

    Conceptual example for working with a transaction.

    DECLARE @ids TABLE (id int);
    BEGIN TRANSACTION
    
    UPDATE Table1 
    SET Table1.LastName = 'DR. XXXXXX'  
    OUTPUT INSERTED.id INTO @ids
    WHERE T1.field = '010008';
    
    UPDATE Table2 
    SET Table2.WAprrs = 'start,stop' 
    FROM Table2 
    JOIN @ids i on i.id = Table2.id;
    
    COMMIT;


    Please remember to mark the replies as answers if they help and unmarked them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.

    NuGet BaseConnectionLibrary for database connections.

    StackOverFlow
    profile for Karen Payne on Stack Exchange

    Thursday, May 16, 2019 9:49 AM
    Moderator
  • ok how can i make below posting format in datagridview 

    account number                                      description                                            dabit              credit

    133                                             cash in hand                                                   2000

                                                                  expanses                                                                     2000

    nattation :             entry passed against expanses

    Friday, May 17, 2019 5:01 AM
  • ok how can i make below posting format in datagridview 

    account number                                      description                                            dabit              credit

    133                                             cash in hand                                                   2000

                                                                  expanses                                                                     2000

    nattation :             entry passed against expanses

    What you are asking really does not feel right for a DataGridView, things like credit and cash on hand belong on a control such as a TextBox while expanses could be in another control be in a DataGridView, ListBox so I would not use a DataGridView for all details.

    Please remember to mark the replies as answers if they help and unmarked them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.

    NuGet BaseConnectionLibrary for database connections.

    StackOverFlow
    profile for Karen Payne on Stack Exchange

    Friday, May 17, 2019 10:18 AM
    Moderator
  • ok can u share with me any example to see how i can post it ?
    Friday, May 17, 2019 11:56 AM
  • From what I see in your screenshot, I would keep it pretty much how it is now as shown below. Each row represents a transaction in the grid and a button to provide opening another window to expand a transaction's details.

    Note the difference between available cash and current balance, I'm thinking of a common case of an account there there may be a pending transaction where if there was a pending transaction for debit for say 100 dollars this would be the difference between available case and current balance.


    Please remember to mark the replies as answers if they help and unmarked them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.

    NuGet BaseConnectionLibrary for database connections.

    StackOverFlow
    profile for Karen Payne on Stack Exchange


    Friday, May 17, 2019 1:05 PM
    Moderator
  • so available cash will come frm cash in hand ? or it will be from account's available cash ?>

    also how can i able to generate the report of this transection and if user want to make payment of 3 different accounts then how it will update those accounts in COA after saving it ..(COA = chart of accounts)

    Saturday, May 18, 2019 10:46 AM
  • so available cash will come frm cash in hand ? or it will be from account's available cash ?>

    also how can i able to generate the report of this transection and if user want to make payment of 3 different accounts then how it will update those accounts in COA after saving it ..(COA = chart of accounts)

    I have to say the current questions are way off course from the initial question, I highly recommend closing this thread out and starting a new question for what now is a discussion on the user interface and reporting which is not about data operations. 

    Please remember to mark the replies as answers if they help and unmarked them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.

    NuGet BaseConnectionLibrary for database connections.

    StackOverFlow
    profile for Karen Payne on Stack Exchange

    Saturday, May 18, 2019 11:11 AM
    Moderator