0

I had a <asp:SqlDataSource connected to a database using a connection string however I wanted to have 2 columns merged together to create 1 column such as:

Column: First Name
Column: Last Name

Creates:

Column: Full Name

I had a hard time finding how to do this in aspx but a really easy time finding this to do in the page directly.

My Sql Data Source was as follows:

<asp:SqlDataSource ID="databaseWork" runat="server" ConnectionString="<%$ ConnectionStrings:ConnectionString%>" SelectCommand="SELECT  [FirstName],[LastName] FROM [People]">
    </asp:SqlDataSource>

I wanted to have them in a drop down list. My drop down list was:

<asp:DropDownList ID="ddlPeople" runat="server" AutoPostBack="True" DataSourceID="databaseWork" DataTextField= 'FirstName' DataValueField="ID">
</asp:DropDownList>

No matter what I did nothing seemed to work, I went to multiple sites and tried many things however I figured it out on my own that I could just modify the SQL Query and create my own column instead of concatenating fields together when I used them.

I am adding an answer to my own question as I believe it will be very helpful to others who want the same result.

L1ghtk3ira
  • 3,021
  • 6
  • 31
  • 70

2 Answers2

0

What I did turned out to be very simple to achieve 1 column that had 2 or more columns created together.

My SqlDataSource is as follows:

<asp:SqlDataSource ID="databaseWork" runat="server" ConnectionString="<%$ ConnectionStrings:ConnectionString%>" SelectCommand="SELECT [FirstName] + ' ' + [LastName], AS [FullName] FROM [People]">
</asp:SqlDataSource>

Now when using it as a column in DropDown Lists Or a Grid View you can simply call that new column we created.

Drop Down List Example:

<asp:DropDownList ID="ddlPeople" runat="server" AutoPostBack="True" DataSourceID="databaseWork" DataTextField= 'FullName' DataValueField="ID">

Grid View With Column Example:

<asp:GridView
  ID="gridPeople"
  runat="server"
  DataSourceID="databaseWork"
  AllowPaging="True" 
  AllowSorting="True" 
  AutoGenerateColumns="False" PageSize="50">

    <Columns> 
        <asp:BoundField DataField="FullName" HeaderText="Full Name Column"  SortExpression="FullName" />

    </Columns>

</asp:GridView>

Instead of trying to add two columns to create one in the drop down list or grid view on the fly it is much better and EASIER to create the column in the SQL Query itself and simply call it. Now when we call FULL Name we get results like this:

  1. John Smith
  2. Hannah Montanna
halfer
  • 19,824
  • 17
  • 99
  • 186
L1ghtk3ira
  • 3,021
  • 6
  • 31
  • 70
0

SqlDataSource

<asp:SqlDataSource ID="databaseWork" runat="server" ConnectionString="<%$ ConnectionStrings:ConnectionString%>" SelectCommand="SELECT [ID], [FirstName] + ' '+[LastName] AS [FullName] FROM [People]">
</asp:SqlDataSource>

Note query "SELECT [ID], [FirstName]+' '+[LastName] AS [FullName] FROM [People]"

For Dropdown

<asp:DropDownList ID="ddlPeople" runat="server" AutoPostBack="True" DataSourceID="databaseWork" DataTextField= 'FullName' DataValueField="ID">
Sami
  • 3,686
  • 4
  • 17
  • 28