• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 229
  • Last Modified:

List Boxes and Data Bindings

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
kevon
Asked:
kevon
  • 4
  • 3
1 Solution
 
dunglaCommented:
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
 
kevonAuthor Commented:
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
 
dunglaCommented:
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
Introducing Cloud Class® training courses

Tech changes fast. You can learn faster. That’s why we’re bringing professional training courses to Experts Exchange. With a subscription, you can access all the Cloud Class® courses to expand your education, prep for certifications, and get top-notch instructions.

 
kevonAuthor Commented:
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
 
dunglaCommented:
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
 
kevonAuthor Commented:
LDLP,

You just plain rock!

Kevon
0
 
dunglaCommented:
Thanks for the points Kevon :-)
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now