locked
How to select header from sqldatasource ? RRS feed

  • Question

  • User-775831949 posted

    I have a datasource and a valid working sqldatasource on aspx page.
    This database the table structure keeps changing, so the column name also keeps changing.

    I now want a SQL select statement on page not to select the data row but to select the header row.

    For example, the database table is like this:

    Country | Sales
    US | 100
    UK | 200
    Etc

    I want a select statement (or whatever) to select dynamically (ie not hard code) out headertext

    Country, Sales

    Is this possible ?

    Friday, November 1, 2013 7:51 PM

Answers

  • User-821857111 posted

    So are you using Access for this? If so, you need to use the OleDbConnection.GetSchema method for retrieving column names from a table. That means you can't use a SqlDataSource control as it doesn't support the GetSchema method. You will have to do it in code behind:

    using (var connection = new OleDbConnection(YourConnectionString)) {
        connection.Open();
        var dataTable = conn.GetSchema("Columns", new[] { null, null, "YourTableName" });
        var columns = dataTable.AsEnumerable().Select(row => row.Field<string>("COLUMN_NAME")).ToArray();
        label1.Text = string.Join(",", columns);
    }

    What I mean is the table structure keeps changing, so the headertext are changing.

    Each time you run the method, you will get the current column names.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, November 5, 2013 1:40 AM

All replies

  • User-821857111 posted
    SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = N'MyTable'

    Saturday, November 2, 2013 4:30 AM
  • User-1324836503 posted
    SELECT table_name, column_name, data_type, data_length FROM USER_TAB_COLUMNS WHERE table_name = 'MYTABLE'

    SELECT [name] AS [Column Name] FROM syscolumns WHERE id = (SELECT id FROM sysobjects WHERE type = 'V' AND [Name] = 'Your table name')
    hope that helps
    Saturday, November 2, 2013 5:06 AM
  • User-775831949 posted

    Thanks a lot. What I want is to get the column header text dynamically since the header keeps changing.

    By the way what if it is a access database and is a accessdatasource, would it be possible to get the header and become a one row multiple value ?

    Thanks

    Saturday, November 2, 2013 9:43 PM
  • User-821857111 posted

    What I want is to get the column header text dynamically since the header keeps changing.

    I don't understand what you are saying.

    By the way what if it is a access database and is a accessdatasource,

    Then none of the suggestions above will work.

    would it be possible to get the header and become a one row multiple value

    Can you exaplin that in a different way?

    Sunday, November 3, 2013 3:00 AM
  • User1508394307 posted

    if it is a access database and is a accessdatasource, would it be possible to get the header and become a one row multiple value ?

    In general, you do it simply as

     <asp:accessdatasource
            id="AccessDataSource1"
            runat="server"
            datasourcemode="DataSet"
            datafile="~/App_Data/Northwind.mdb"
            selectcommand="SELECT * FROM Employees" >
          </asp:accessdatasource>
    
          <asp:gridview
            id="GridView1"
            runat="server"
            autogeneratecolumns="True" 
            datasourceid="AccessDataSource1">
          </asp:gridview>

    where autogeneratecolumns="True" tells to generate headers automatically 

    If for some reasons you need to make it differently, use server side code

    Example, accessing columns vb

    Dim dv As DataView = AccessDataSource1.Select(DataSourceSelectArguments.Empty);
    Dim dc As DataColumn
    For Each dc In dv
    Label1.Text &= dc.ColumnName
    Next

    Hope this helps.

    Sunday, November 3, 2013 4:52 AM
  • User-775831949 posted

    "What I want is to get the column header text dynamically since the header keeps changing."

    I don't understand what you are saying.

    What I mean is the table structure keeps changing, so the headertext are changing.

    And, for the example I posted, I want to get the header into a single line comma separated string to display in label1.text.

    ie Country,Sales into label1.text,

    Thanks

    Tuesday, November 5, 2013 12:35 AM
  • User-821857111 posted

    So are you using Access for this? If so, you need to use the OleDbConnection.GetSchema method for retrieving column names from a table. That means you can't use a SqlDataSource control as it doesn't support the GetSchema method. You will have to do it in code behind:

    using (var connection = new OleDbConnection(YourConnectionString)) {
        connection.Open();
        var dataTable = conn.GetSchema("Columns", new[] { null, null, "YourTableName" });
        var columns = dataTable.AsEnumerable().Select(row => row.Field<string>("COLUMN_NAME")).ToArray();
        label1.Text = string.Join(",", columns);
    }

    What I mean is the table structure keeps changing, so the headertext are changing.

    Each time you run the method, you will get the current column names.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, November 5, 2013 1:40 AM