C# – Binding multiple fields to listbox in ASP.NET

asp.netclinqlinq-to-sqlsql

I'm fairly new to asp.net and especially LINQ and SQL.

Say I have a table "Employees" with fields "Lastname", "Firstname", and "ID".

I want to bind this to a list box. I want the list box to display it's contents like "$LASTNAME, $FIRSTNAME" and I want the value of each item to be "ID".

It's trivial to bind either name column to the listbox and the values accordingly, but I can't figure out how to do this..

Thanks in advance

Best Answer

You could try something like this:

var datasource = from employee in employees 
                 select new
                 { 
                     Name = employee.lastName + ", " + employee.firstName, 
                     Id = employee.ID 
                 };

myListBox.DataSource = datasource;
myListBox.DataTextField = "Name";
myListBox.DataValueField = "Id";
myListBox.DataBind();

This constructs a list of anonymous types from your employee table to bind your listbox to.

Related Solutions

C# – Catch multiple exceptions at once

Catch System.Exception and switch on the types

catch (Exception ex)            
{                
    if (ex is FormatException || ex is OverflowException)
    {
        WebId = Guid.Empty;
        return;
    }
    
    throw;
}

Sql – How to concatenate text from multiple rows into a single text string in SQL Server

If you are on SQL Server 2017 or Azure, see Mathieu Renda answer.

I had a similar issue when I was trying to join two tables with one-to-many relationships. In SQL 2005 I found that XML PATH method can handle the concatenation of the rows very easily.

If there is a table called STUDENTS

SubjectID       StudentName
----------      -------------
1               Mary
1               John
1               Sam
2               Alaina
2               Edward

Result I expected was:

SubjectID       StudentName
----------      -------------
1               Mary, John, Sam
2               Alaina, Edward

I used the following T-SQL:

SELECT Main.SubjectID,
       LEFT(Main.Students,Len(Main.Students)-1) As "Students"
FROM
    (
        SELECT DISTINCT ST2.SubjectID, 
            (
                SELECT ST1.StudentName + ',' AS [text()]
                FROM dbo.Students ST1
                WHERE ST1.SubjectID = ST2.SubjectID
                ORDER BY ST1.SubjectID
                FOR XML PATH ('')
            ) [Students]
        FROM dbo.Students ST2
    ) [Main]

You can do the same thing in a more compact way if you can concat the commas at the beginning and use substring to skip the first one so you don't need to do a sub-query:

SELECT DISTINCT ST2.SubjectID, 
    SUBSTRING(
        (
            SELECT ','+ST1.StudentName  AS [text()]
            FROM dbo.Students ST1
            WHERE ST1.SubjectID = ST2.SubjectID
            ORDER BY ST1.SubjectID
            FOR XML PATH ('')
        ), 2, 1000) [Students]
FROM dbo.Students ST2

Best Answer

Related Solutions

C# – Catch multiple exceptions at once

Sql – How to concatenate text from multiple rows into a single text string in SQL Server

Related Topic