In my stored procedure I have a column that haS a dynamic column name that’s multiline. In MS SQL this column looks exactly how I want it, but in my gridview it’s single line. I am using autogeneratecolumn=“true” because there are so many different types of column names that could be displayed. How can I have that column only be multiline? Thanks in advance

update: here is my sproc and gridview sproc

declare @min varchar(max), @max varchar(max), @c varchar(max), @slqStmt varchar(max)
select @min = max(somecolumn1), @max = max(somecolumn2) from someDB.dbo.someTable
where Name = @somenamesenttosproc
set @c =  @min   ' - '    @max  '
BOTTOMLINEOFCOLUMNTEXT'

set @slqStmt = '
SELECT Name '   'AS ['   @c  ']'  '
from someDB.dbo.someTable '

PRINT @slqStmt
EXEC(@slqStmt)

gridview

<asp:GridView CssClass="border border-top-0 border-start-0 border-end-0 border-dark z-0 w-100" ID ="gridView" runat="server" AutoGenerateColumns="true" cellpadding="10" ShowHeaderWhenEmpty="True" GridLines="Both" BorderColor="Black" EmptyDataText="No records found..." AllowPaging="true" PageSize="100" Font-Size="Small">
<%-- Grid Style --%>
<HeaderStyle BackColor="#c4143b" Font-Bold="True" ForeColor="White" CssClass="border border-bottom-0 border-dark sticky-top-15"/>
<AlternatingRowStyle BackColor="#f7bac7" />
<RowStyle CssClass="border border-top-0 border-bottom-0 border-dark" />
<PagerSettings Position="Top" />
<PagerStyle HorizontalAlign="Left" CssClass="pagination-ys" />

CodePudding user response：

I have to wonder - REALLY wonder if this is a good idea?

I have to look at the sample store procedure - even worse looking!

In other words, the crazy hand stands you going though here is OH SO MUCH worse then just setting up a header. Do you really not have control of what table and grid you going to design for a given page? Really?

You can imbed a "br" in the column text. But if the data for the heading is suppled by the data source, then markup is ignored. So, this issue makes this even worse!

However , you can cook up (assume) some char in the heading to be the start of the 2nd line.

SQL rather limited, you can use assume a "_" or maybe a "$" or a "#" in the supplied header. (but, few choices - sql don't like much else like a or others (invalid syntax)

Lets do a simple example:

And lets thus assume a "_" (under score) will be 2nd line of heading.

So, this markup:

<asp:GridView ID="GridView1" runat="server" Width="40%" CssClass="table">


</asp:GridView>

And code to load is this:

   protected void Page_Load(object sender, EventArgs e)
    {
        if (!IsPostBack)
            LoadGrid();
    }

    void LoadGrid()
    {
        string strSQL =
            @"SELECT FirstName as First_Name,
            LastName as Last_Name,
            City,
            HotelName as Hotel_Name,
            Description FROM tblHotelsA ORDER BY HotelName";

        DataTable rstData = new DataTable();
        using (SqlConnection conn = new SqlConnection(Properties.Settings.Default.TEST4))
        {
            using (SqlCommand cmdSQL = new SqlCommand(strSQL, conn))
            {
                conn.Open();
                rstData.Load(cmdSQL.ExecuteReader());
            }
        }
        GridView1.DataSource = rstData;
        GridView1.DataBind();

        // optional, convert all "_" to 2nd line in heading
        for (int i = 0; i < rstData.Columns.Count; i  )
        {
            GridView1.HeaderRow.Cells[i].Text =
                rstData.Columns[i].ColumnName.Replace("_", "<br/>");
        }
    }

And now we get this:

So, I would try to avoid hacking to death the source SQL. But, I suppose introduction of the assumption of some char in the column name, and them processing out as per above is workable.

As a general rule, I would try and keep that SQL clean, nice and simple, and not hack to death existing SQL. The requirement to NOT know the column name(s) ahead of time is red flag in this design - and I would spend greater efforts attempting to avoid that design assumption in the first place.