How to display multiple local database items in listBox?

Question

Im new to programming and my problem is im trying to display multiple items in a listbox and the items come from a local database. I got a table Users in my local database that contains columns ID, Firstname, lastname, email and address.

I have binded the listBox with my local dababase table "Users" and here's what i got so far:

private void button_ShowItems_Click(object sender, EventArgs e)
    {
        var dt = new DataTable();

        string connectionString = @"Data Source=MyDatabase;Password=xxxxxx;";

        using (var cn = new SqlCeConnection(connectionString))
        using (var cmd = new SqlCeCommand("Select * From Users", cn))
        {
            cn.Open();

            using (var reader = cmd.ExecuteReader())
            {
                dt.Load(reader);
                listBox_Users.DataSource = dt;
                listBox_users.DisplayMember = "Firstname" + "Lastname";
                listBox_Users.ValueMember = "ID";
            }
        }
    }

When i press the button it only shows the "Firstname" and not the "Lastname".

I got a form with textBoxes that enter data into the local database "Users" and that works:

private void button_add_Click(object sender, EventArgs e)
{
var insertSQL = "INSERT INTO Inimesed (Firstname, Lastname, Email, Address) VALUES (Firstname, Lastname, Email, Address)";

string connectionString = @"Data Source=myDatabase;Password=xxxxxx;";
using (var cn = new SqlCeConnection(connectionString))
using (var cmd = new SqlCeCommand(insertSQL, cn))
    {
         cn.Open();

        cmd.Parameters.Add("@Firstname", SqlDbType.NVarChar);
        cmd.Parameters.Add("@Lastname", SqlDbType.NVarChar);
        cmd.Parameters.Add("@Email", SqlDbType.NVarChar);
        cmd.Parameters.Add("@Address", SqlDbType.NVarChar);

        cmd.Parameters["Firstname"].Value = textBox1_Firstname.Text;
        cmd.Parameters["Lastname"].Value = textBox2_Lastname.Text;
        cmd.Parameters["Email"].Value = textBox3_Email.Text;
        cmd.Parameters["Address"].Value = textBox4_Address.Text;
        cmd.ExecuteNonQuery();

    }

}

And i have a followup question if anyone knows: What would i have to do to add the selected listBox values using the "Firstname" + "Lastname" back into the textboxes?

There are tons of tutorials on "How to add textBox items into local database in C#", but none "How to add local database items items into textBox". I got all the values defined. Should i use "foreach" commands, "if" commands or "if" commands inside "foreach" commands?

Any help would be great!

User_T could you please ensure you either mark as answer or comment if this answer is not helpful. Many thanks! — Bertie
– Bertie, Commented May 1, 2013 at 9:54

Bertie · Accepted Answer · 2013-04-17 15:33:07Z

1

For whatever reason you cannot bind on the concatenation - best bet is to use Linq to create an Anonymous type that holds the concat itself - see below.

var results = (from row in dt.AsEnumerable()
               select new  
               {
                   UserID = row.Field<int>("UserPK"), 
                   FirstName = row.Field<string>("FirstName"), 
                   LastName = row.Field<string>("LastName"), 
                   FullName = row.Field<string>("FirstName") + " " + row.Field<string>("LastName") 
               }).ToList();

listBox1.DataSource = results;
listBox1.DisplayMember = "FullName";
listBox1.ValueMember = "UserID";

Et voila, correctly displays the full names.

answered Apr 17, 2013 at 15:33

Bertie

7336 silver badges16 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

How to display multiple local database items in listBox?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related