locked
i have a table with fields,whenever select the Poll then give corresponding records in Country dropdown,whenever selecting Country populate Region,whenever region then Revenue RRS feed

  • Question

  • Hi ebery one..

          i have a table with fields

     
    Poll Country Region Revenue
    ANZ Australia Sydney 20000
    ANZ Newzeland NW 50000
    EU Europe Eu 40000
    Asia India Banglore 60000
    Asia Srilanka Colombo 70000
    Asia Pakistan Karachi 80000
                   i need to populate the values in three dropdown by default  first dropdown is Poll,first time all dropdowns shown null values,whenever select the Poll  then give corresponding records in Country dropdown,whenever selecting Country populate Region,whenever region then Revenue.How will i do this in Sql server 2008..any help ....
    Saturday, December 8, 2012 5:41 PM

Answers

  • You will need to do the dropdown filtering/association either from the DB side (return only the filtered data) or from the client side (return all and handle from client code).  If you return all data to the client, there is likely a way to filter based on selection, but you'd need to ask that question in the appropriate client technology forum.  From a SQL Server perspective, you can create a stored procedure for each of these relationships and call into these SPs when a dropdown is selected.  For Poll/Country, the SP will take the Poll value as input and return the list of Countries.  Same strategy for Country/Region.  This would require SP calls to the DB for each dropdown modification, which could have performance impact depending on how frequently this data is getting accessed/changed.  My guess would be that it is better to handle from the client code, though it can be solved through the DB as well.

    Thanks,
    Sam Lester (MFST)


    My Blog

    This posting is provided "AS IS" with no warranties, and confers no rights. Please remember to click "Mark as Answer" and "Vote as Helpful" on posts that help you. This can be beneficial to other community members reading the thread.

    Saturday, December 8, 2012 5:57 PM
  • create procedure getPolls
    as
    SELECT POLL FROM TABLE ORDER BY POLL;
    CREATE PROCEDURE GETCOUNTRIES
    @POLL VARCHAR(10)
    AS
    SELECT COUNTRY FROM TABLE WHERE POLL = @POLL ORDER BY COUNTRY;
    And so on

    Sunday, December 9, 2012 1:35 AM

All replies

  • You will need to do the dropdown filtering/association either from the DB side (return only the filtered data) or from the client side (return all and handle from client code).  If you return all data to the client, there is likely a way to filter based on selection, but you'd need to ask that question in the appropriate client technology forum.  From a SQL Server perspective, you can create a stored procedure for each of these relationships and call into these SPs when a dropdown is selected.  For Poll/Country, the SP will take the Poll value as input and return the list of Countries.  Same strategy for Country/Region.  This would require SP calls to the DB for each dropdown modification, which could have performance impact depending on how frequently this data is getting accessed/changed.  My guess would be that it is better to handle from the client code, though it can be solved through the DB as well.

    Thanks,
    Sam Lester (MFST)


    My Blog

    This posting is provided "AS IS" with no warranties, and confers no rights. Please remember to click "Mark as Answer" and "Vote as Helpful" on posts that help you. This can be beneficial to other community members reading the thread.

    Saturday, December 8, 2012 5:57 PM
  • Much depends on the programming language you use to, and the environment, whether web or desktop.

    Basically when the value of the first combo to invoke such a query to load the data in the second combo

    SELECT COUNTRY FROM TABLE WHERE POLL = @POLL ORDER BY COUNTRY;

    Once again change the value of the second combo again invoke another SQL query to load the third combo

    SELECT REGION FROM TABLE WHERE COUNTRY = @COUNTRY ORDER BY REGION;
    and so on to the next combo. Remember that each change in value of each combo should send selected value as parameter.

    Saturday, December 8, 2012 7:48 PM
  • Hi,

       can you please share me code how to do it in Stored Procedures..


    Thanks&Regards Srinivas.M

    Sunday, December 9, 2012 1:24 AM
  • create procedure getPolls
    as
    SELECT POLL FROM TABLE ORDER BY POLL;
    CREATE PROCEDURE GETCOUNTRIES
    @POLL VARCHAR(10)
    AS
    SELECT COUNTRY FROM TABLE WHERE POLL = @POLL ORDER BY COUNTRY;
    And so on

    Sunday, December 9, 2012 1:35 AM
  • Thank you.........

    Thanks&Regards Srinivas.M

    Sunday, December 9, 2012 2:10 PM