How to populate a field in gridview with data FROM ANOTHER TABLE?

Discussion in 'ASP .Net' started by Kurt of San Jose, Apr 4, 2008.

  1. All-

    Please advise as to what's the best way to display data in a column in a
    grid view (and I'm using a dataset) in a table (say TABLE2) which:

    - is non-zero only if a boolian field in the same row is true, and
    - when the boolean field is true, gets its value FROM ANOTHER TABLE?

    Say we have these two tables:
    table1_id (int)
    some_number (int)

    table2_id (int)
    table1_id (int, FK)
    is_coming (bit)

    Now, I display TABLE2 in a gridview and add, say, a calculated column named
    "value". So again, I'd like the logic for the calculated field to be as

    - If is_coming = False, then dispaly "0" or nothing
    - If is_coming = True, then display the corresponding some_number value from

    So what I think I need is some type of conditional select statement. The
    last message from someone on a forum suggested using this select statement in
    a stored procedure:

    1 SELECT
    3 table2.table2_id,
    4 table2.is_coming,
    5 CASE table2.is_coming WHEN 'False' THEN '0' ELSE table1.some_number
    END AS 'MyArtificialColumn'
    7 FROM table2
    9 INNER JOIN table1
    10 ON table2.table1_id = table1.table1_id

    However, I'm not sure how to display the value returned by the SP in the
    gridview column. Note, too, that I'd want the ability to sum the column in
    the gridview footer (but I think I can handle that).

    Searching the internet, I've only been able to find out that Expressions in
    calculated fields can only use values from the same table (no lookups in
    other tables). Another suggested that the value of the field could be
    populated by using INSERT or UPDATE trigger on table2. Not sure how to
    proceed. Any ideas would be appreciated.


    Kurt of San Jose, Apr 4, 2008
    1. Advertisements

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.