Solved

List Boxes and Data Bindings

Posted on 2004-10-31
221 Views
Last Modified: 2010-04-15
Ok, I'm working on populating a list box from a database and would like to use data binding so that the rest of the fields in the form display the associated data.

Here's the code that I've got;

private void BindPersonListBox()
{
   DataTable playerTable = Database.GetPlayerList();//returns a DataTable with all the proper fields
   lbxPlayers.DataSource = playerTable;
   lbxPlayers.DisplayMember = "Last_Name";
   lbxPlayers.ValueMember = "Person_ID";
}

What I would like to know is this;  How do I make it so that the DisplayMember can be a concatenation of a couple fields in my table?  I would like the listbox to show "Last_Name, First_Name Middle_Initial" rather than just the "Last_Name"

I read somewhere that if you leave off the "DisplayMember" part then it calls the ToString() function on the object, however, since I am pulling directly from my database table as the data source there is no actual class being used for the "player"

I know that I could populate a DataTable, then loop through it creating objects of my classPerson, then use those to populate the list box, but how would that work with DataBinding?  Will it automatically know that it has the correct person?  Also, how would I go about using the DataTable?  It does not seem to work like the datasets that I have used in the past.  It does not seem to want to respond to playerTable[i].Player_ID.

My event handler for selected index changed is

person = Database.GetPerson(lbxPlayers.SelectedValue.ToString());
ShowPersonData(); //calls a function which changes the txtBoxes on the screen to match person.whatever

I suppose I could do the previous idea and fill it with classPerson objects and change the SelectedIndexChanged event handler to something like;

person = Database.GetPerson(((classPerson)lbxPlayers.Items[lbxPlayers.SelectedIndex]).Person_ID.ToString());

but that seems a little awkward, and there seems like there ought to be a more simple way of doing it.

So, the points will go to someone who can show me, if possible, a more elegant way of doing what I want to do.

If it's not possible, then, tell me why, and offer a solution, or validate that the way that I came up with is the only / best way to do it, and you can have the points...

Kevon
0
Question by:kevon
    7 Comments
     
    LVL 13

    Expert Comment

    by:dungla
    Kevon,

    If you just want to the listbox to show "Last_Name, First_Name Middle_Initial" rather than just the "Last_Name". I have the solution: when you retrieve data from database and put into a DataTable, just using Alias to concatenation Last, first and middle, so the query string should be "Select Last_Name + ', ' + First_Name + ' ' + Middle_Initial as PlayerName. And then put the field name PlayerName to your DataTable and bind to datasource of ListBox. The DisplayMember should set to "PlayerName". Just try it instead of trying to concatenation two fields from your result DataTable. I think it should be easier way than trying to concatenation two fields.

    Just tell me extractly what you want.

    Hope this help
    .:: LDLP ::.
    0
     

    Author Comment

    by:kevon
    LDLP,

    That sounds like it would work, as long as I can write it so that I can set the ValueMember to "Person_ID"

    I am not familir enough with SQL to write the select statement like you describe.  What I am confused on is how to get the "Select Last_Name + ', ' + First_Name + ' ' + Middle_Initial" to map to PlayerName in the DataTable.  Is "as" a SQL keyword?

    Here is my current select statement to create the DataTable.

    public static DataTable GetPlayerList()
    {
      string selectStatement = "SELECT * FROM tblPerson ORDER BY Last_Name, First_Name, MI";
      SqlCommand selectCommand = new SqlCommand(selectStatement, GetConnection());
      SqlDataAdapter personDataAdapter = new SqlDataAdapter(selectCommand);
      DataSet personDataSet = new DataSet();
      personDataAdapter.Fill(personDataSet, "tblPerson");
      return personDataSet.Tables["tblPerson"];
    }

    Obviously the Select * is selecting everything.  I guess that for the purpose of the DataTable I don't need all of the info, just the Person_ID, First_Name, Middle_Initial, Last_Name.

    so, if you can help me write the SELECT statement, that would rock.  Additionally, how does it effect the PersonDataAdapter.Fill(personDataSet, "tblPerson") when tblPerson does not have PersonName as a field?  Do I need to do that differently?  Should I code something to let the DataSet and the DataTable set it's own column Names or something?  If so, How do I do that?

    Thanks for the help,

    Kevon
    0
     
    LVL 13

    Expert Comment

    by:dungla
    Kevon,

    Select statement should be

    "SELECT Person_ID, First_Name + ', ' + Last_Name + ' ' + Middle_Initial AS PlayerName FROM tblPerson ORDER BY Last_Name, First_Name, Middle_Initial";

    When you execute this query string, the result contains two fields named: Person_ID and PlayerName. Therefore when you using SqlDataAdapter to fill your DataSet, the table "tblPerson" of return value (return personDataSet.Tables["tblPerson"]) will have two fields Person_ID and PlayerName.

    After that when you bind data to your list box:

    lbxPlayers.DataSource = playerTable;
    lbxPlayers.DisplayMember = "PlayerName"; (lbxPlayers.DataTextField = "PlayerName";)
    lbxPlayers.ValueMember = "Person_ID"; (lbxPlayers.DataValueField = "Person_ID";)

    That all. Just try it and tell me what happen.

    Hope this help
    .:: LDLP ::.
    0
     

    Author Comment

    by:kevon
    Yup, that works.  What was confusing me was where the tblPerson was being used...  in the select statement the tblPerson is my table from the database, and I guess in the fill statement it is not, it's the table that I'm creating.  

    Ok, that has gotten the points for this, however, I do have one other question, and this may be a SQL statement, or it might be a c#.  If you can answer this, I will double the points, othewise I'll ask it somewhere else.  The help you have given so far has been awesome and greatly appreciated.

    When the select statement gets the information from the table, there are a lot of padding spaces in the names and stuff.  Is there a way to trim that blank space before displaying?  Is there something in the sql statement that could do that, or am I stuck with a display like...

    Lastname           , Firstname         MI

    when what I would like is

    Lastname, Firstname MI
    0
     
    LVL 13

    Accepted Solution

    by:
    Kevon, if you want to trim blank space before displaying, try RTRIM and LTRIM to do that.

    RTRIM will trimming blank space from end character ("abc     ").
    LTRIM will trimming blank space from start character ("     abc")

    for example:

    Lastname = "Kevon        "
    Firstname = "     Kevon    "

    If you want to trim all then blank space then the select statement should be

    SELECT RTRIM(Lastname) AS LastName, LTRIM(RTRIM(Firstname)) AS FirstName FROM tblPerson

    ========= Result==========
    LastName: "Kevon"
    FirstName: "Kevon"
    =======================

    If you remove LTRIM or RTRIM when retreive Firstname, the result should be

    =========Result==========
    LastName: "Kevon"
    FirstName: "     Kevon" (if remove LTRIM)
    FirstName: "Kevon    " (if remove RTRIM)
    =======================

    Got it? Just try and tell me what happen next

    Hope this help
    .:: LDLP ::.
    0
     

    Author Comment

    by:kevon
    LDLP,

    You just plain rock!

    Kevon
    0
     
    LVL 13

    Expert Comment

    by:dungla
    Thanks for the points Kevon :-)
    0

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Hire Top Freelancers to Complete C# Projects

    Source the talented Expert Exchange community
    for top quality work on your C# projects.

    Hire the best. Collaborate easily. Get quality work.

    Suggested Solutions

    This article is for Object-Oriented Programming (OOP) beginners. An Interface contains declarations of events, indexers, methods and/or properties. Any class which implements the Interface should provide the concrete implementation for each Inter…
    Exception Handling is in the core of any application that is able to dignify its name. In this article, I'll guide you through the process of writing a DRY (Don't Repeat Yourself) Exception Handling mechanism, using Aspect Oriented Programming.
    how to add IIS SMTP to handle application/Scanner relays into office 365.
    This video is in connection to the article "The case of a missing mobile phone (https://www.experts-exchange.com/articles/28474/The-Case-of-a-Missing-Mobile-Phone.html)". It will help one to understand clearly the steps to track a lost android phone.

    856 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    13 Experts available now in Live!

    Get 1:1 Help Now