Create a user friendly drop down while retaining original column value?

I am trying to create a user friendly drop down using the following columns:

LNAME, FNAME, USERID

Here is my SelectCommand:

SELECT ISNULL(LNAME,'') + ', ' + ISNULL(FNAME,'') + ' (' + ISNULL(USERID,'') + ') ' AS FullName FROM USER ORDER BY FullName

The result is:

LNAME, FNAME (USERID)

This is what I want. I am trying to set the DataValueField to the USERID for use in SelectedIndexChanged. Right now it only works when DataValueField is set to FullName. I receive the following error when setting it to USERID:

DataBinding: 'System.Data.DataRowView' does not contain a property with the name 开发者_运维百科'USERID'.

I need to use the USERID for the next step. Is there a way to extract USERID from the alias in this SELECT statement?

Without seeing your code it hard to give a definate answer, but I would recommend selecting the "user friendly" value as well as the UserID:

SELECT ISNULL(LNAME,'') + 
       ', ' + 
       ISNULL(FNAME,'') + 
       ' (' + 
       ISNULL(USERID,'') + 
       ') ' AS FullName,
       UserID
FROM USER ORDER BY FullName

Then specify UserId as the DataValueField. Right now you aren't selecting UserId, so if you try to specify it as the DataValueField it will blow up.

Change your SQL to separate value and label (text)

SELECT
   USERID, -- what the user actually selects
   ISNULL(LNAME,'') + ', ' + ISNULL(FNAME,'') + ' (' + ISNULL(USERID,'') + ') '
           AS FullName --what user sees and appears to SELECT
FROM
   USER
ORDER BY
   FullName

Then you bind

• DataValueField to USERID
• DataTextField to Fullname

The example on MSDN shows this: the value has no spaces eg "Dark Khaki" vs "DarkKhaki"