locked
doing a query inside a webgrid column RRS feed

  • Question

  • User379720387 posted

    Compiler complains: {"Invalid column name \"db.QueryValue(sqlSelect1, @item.ProviderId, @item.ownerBillToId)\"."}

    thanks in advance

    var sqlSelect1 = "SELECT SUM(Charge) AS BalanceOwner  FROM Transactions WHERE ProviderId = @0 AND ownerBillToId=@1 AND IsPaid=0";
    grid.Column(@db.QueryValue(sqlSelect1, @item.ProviderId, @item.ownerBillToId), "Balance Owner"),



    Thursday, June 5, 2014 5:10 PM

Answers

  • User-821857111 posted

    The WebGrid doesn't allow columns to be decided after the data has been passed in to it. You need to prepare the data with the "BalanceOwner" property/column before it gets passed to the WebGrid constructor. You should be able to do this with a single SQL query using Joins.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, June 5, 2014 5:29 PM
  • User-821857111 posted

    The alternative is to create a new package of data based on the original query using an anonymous type. It might look something like this:

    var data = db.Query(originalQuery, param1, param2, param_etc).Select(d => new /* this is the anonymous type */ { 
        ProviderId = d.ProviderId, 
        OwnerBillTold = d.ownerBillTold,
        // other fields,
        BalanceOwner = db.QueryValue(sqlSelect1, d.ProviderId, d.ownerBillToId)
    });
    
    var grid = new WebGrid(data);

    It's not ideal because it still causes an extra database call to be made per row of data which may slow the whole lot down. But it is a potential solution. 

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, June 6, 2014 2:44 AM

All replies

  • User-821857111 posted

    The WebGrid doesn't allow columns to be decided after the data has been passed in to it. You need to prepare the data with the "BalanceOwner" property/column before it gets passed to the WebGrid constructor. You should be able to do this with a single SQL query using Joins.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, June 5, 2014 5:29 PM
  • User379720387 posted

    Bummer!

    Thought I found something here, but that didn't work either.

    grid.Column(columnName: "Balance Owner",  format: @<text>db.QueryValue(@sqlSelect1, @item.ProviderId, @item.ownerBillToId)</text> ),

    The query is a nasty one, at least with SQL CE.

    Thursday, June 5, 2014 5:54 PM
  • User-821857111 posted

    The alternative is to create a new package of data based on the original query using an anonymous type. It might look something like this:

    var data = db.Query(originalQuery, param1, param2, param_etc).Select(d => new /* this is the anonymous type */ { 
        ProviderId = d.ProviderId, 
        OwnerBillTold = d.ownerBillTold,
        // other fields,
        BalanceOwner = db.QueryValue(sqlSelect1, d.ProviderId, d.ownerBillToId)
    });
    
    var grid = new WebGrid(data);

    It's not ideal because it still causes an extra database call to be made per row of data which may slow the whole lot down. But it is a potential solution. 

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, June 6, 2014 2:44 AM
  • User379720387 posted

    This works. Thanks.

    For clarification to others in the future where mikesdotnetting has //other fields you put the column names there that you selected in the originalQuery.

    Friday, June 6, 2014 5:25 PM