Get Columns From SP!

Discussion in 'ASP General' started by Arpan, Sep 29, 2005.

  1. Arpan

    Arpan Guest

    An ASP application needs to display DISTINCT records of all the columns
    existing in a SQL Server DB table in different drop-down lists. The
    column names & the no. of columns aren't known while coding. The no. of
    drop-downs depend on the no. of columns i.e. if the DB table has 10
    columns, there should be 10 drop-downs. This is the stored procedure I
    have written to do this:

    ----------------------------------------
    CREATE PROCEDURE GetRecords
    AS
    DECLARE
    @ordpos int,
    @colname sysname,
    @sql varchar(8000)

    SET @ordpos=1

    WHILE @ordpos<=(SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE
    TABLE_NAME='MyTable')
    BEGIN
    SELECT @colname=COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE
    TABLE_NAME='MyTable' AND ORDINAL_POSITION=@ordpos
    SET @sql='SELECT DISTINCT(' + @colname + ') FROM MyTable ORDER BY ' +
    @colname
    EXEC(@sql)
    SET @ordpos=@ordpos+1
    END
    ----------------------------------------

    As such, the above stored procedure works fine when executed in the
    Query Analyzer but the problem is in populating the drop-downs in the
    ASP page. Since the stored procedure generates the column names
    dynamically, I don't know the column names while coding the different
    drop-downs.

    How do I populate the drop-downs? Note that I want the name of the
    individual drop-downs to be the name of the column.

    Thanks,


    Arpan
     
    Arpan, Sep 29, 2005
    #1
    1. Advertising

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.
Similar Threads
  1. helpful sql
    Replies:
    0
    Views:
    852
    helpful sql
    May 19, 2005
  2. Replies:
    5
    Views:
    632
  3. Replies:
    4
    Views:
    964
  4. =?Utf-8?B?YmVub2l0?=

    Read CSV - string Columns - Int columns

    =?Utf-8?B?YmVub2l0?=, May 8, 2006, in forum: ASP .Net
    Replies:
    0
    Views:
    452
    =?Utf-8?B?YmVub2l0?=
    May 8, 2006
  5. Jacksm
    Replies:
    2
    Views:
    890
    Paul Chalekian
    Nov 21, 2006
Loading...

Share This Page