none
How to retreive/update a single row in a datatable? RRS feed

  • Question

  • Hello,

    I'm a newbie...

    I have master/detail view with two datagrids based on an Invoices dataset with two tables:

    Invoices (int InvoiceNum, int ClientNum, ..., INVOICE_TOTAL)

    InvoiceLineItems (int LineItemNum, int Quantity, float Price, ...)

    The invoices datatable is based on a stored procedure which returns data from the underlying Invoices table but also aggregate data calculated in the stored procedure - INVOICES_TOTALS (i.e.: for each invoice in the list the sproc calculates the sum of quantity*price for children in the InvoiceLineItems table). I do this in SQL Server for performance reasons and to be able to show the sum of all invoices at the bottom of the invoices datagrid (the master view).

    Here is my problem: when I add line items to my InvoiceLineItems table in the detail datagrid I would like the corresponding Invoice row in the master datagrid to be refreshed to show the new INVOICE_TOTAL calculated in SQL. I don't want to update the whole Invoices datatable. I would basically like to call something like dataAdapter.Update(<just one row of my datatable>).

    I thought of manually setting the invoice row's rowstate property to modified and then calling dataAdapter.Update but I don't know if this will really update just that row.

    I hope this is clear. Thanks for your help.

    Cheers,

    Charlie

    Friday, March 11, 2011 12:52 PM

Answers

  • It seems to me that once you've added a new InvoiceLineItem, you could simply add the newly calculated amount (Quantity * Price) from the InvoiceLineItem row to the INVOICE_TOTAL field of the parent row in the Invoices DataTable. Add it directly to the field that you already have in the DataTable ... there's no need to retrieve any data from the database.
    ~~Bonnie Berent [C# MVP]

    geek-goddess-bonnie.blogspot.com
    Sunday, March 27, 2011 1:17 AM

All replies

  • Hi Charlie,

    Welcome!

    According to your description, I think it's hard to use Update method to achieve your goal, If you just to want to Count the Totals, You can count them in client instead of control the logic in database, and you should refresh the datasources.

    Have a nice day.


    Alan Chen[MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Sunday, March 13, 2011 12:01 PM
    Moderator
  • Hi Charlie,

    I am writing to check the status of the issue on your side.  Would you mind letting us know the result of the suggestions? 
     
    If you need further assistance, please feel free to let me know.   I will be more than happy to be of assistance.

    Have a nice day.


    Alan Chen[MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Wednesday, March 16, 2011 2:29 AM
    Moderator
  • Alan,

    I have yet to find a solution but I have moved on for now. For now, I reload the whole list to update the one row. But I can see that causing performance problems as data accumulates and the list contains thousands of rows.

    I don't want to count the totals; I want to calculate the totals. I'm guessing that's what you meant but...

    Calculating invoice totals in the client requires retreiving, not only the Invoices table, but also the entire InvoiceLineItems table, each time the user refreshes the invoice list. I will have thousands of invoices which means tens of thousands of line items. That's a lot of network traffic! Also, the clients then needs to calculate the total for each invoice row from the InvoiceLineItems table. That sounds like something SQL Server does much better than the client. My invoice list shows all invoices (with totals) but invoice line items are fetched only for the selected invoice.

    I would like to change the invoice total in the list when a user adds a line item to that invoice in the detail form. I would like do this in the client so that the list shows the same total as the detail for the selected invoice without having to request the whole invoice list from the database.

    Since my local Invoices datatable has a readonly INVOICE_TOTAL field which it gets from its datasource (stored procedure), could I change that value 'temporarily' (until the user himself refreshes the entire list) in the datatable?

    I hope this is clear.

    Thanks for your feedback,

    Charlie


    Charlie
    Tuesday, March 22, 2011 10:40 PM
  • It seems to me that once you've added a new InvoiceLineItem, you could simply add the newly calculated amount (Quantity * Price) from the InvoiceLineItem row to the INVOICE_TOTAL field of the parent row in the Invoices DataTable. Add it directly to the field that you already have in the DataTable ... there's no need to retrieve any data from the database.
    ~~Bonnie Berent [C# MVP]

    geek-goddess-bonnie.blogspot.com
    Sunday, March 27, 2011 1:17 AM
  • Hi

    Check the below link, might be that will be useful to you.

     

    http://support.microsoft.com/kb/308055

     


    Thanks: Bijay Kumar Sahoo Personal & SharePoint Blog URL: http://www.fewlines4biju.com MCTS(MOSS 2007)
    Monday, March 28, 2011 11:59 AM