C# Displaying a sql database object's name in

Question

I am trying to make a small contact book that takes contacts details from a mssql database.It has 3 tables: contacts, last_talk(last time i talked with a contact+short description of the discussion), and another table(that has both primary keys from the first 2 tables )

On the form(tab of tabcontrol) where i display the contacts, i have added 2 listboxes, one loads and displays the contacts names, and the second listbox loads the "Last talk" list for every contact i select depending how many "talks" i had with a contact.

What i am trying to do now is: when i select a contact, i also want to display near the listboxes, some labels for the contact name, company, etc, that change their text to the database entry for the selected contact's name/company...

Here is a part of the code:

    private void lstContactList_SelectedIndexChanged(object sender, EventArgs e)
    {
        PopulateTalkList();
        PopulateContactLabels();
    }

    private void ContactBookForm_Load(object sender, EventArgs e)
    {
        PopulateContactList();

    }

    private void PopulateContactList()
    {
        string query = "SELECT * FROM Contact";
        using (connection = new SqlConnection(connectionString))
        using (SqlDataAdapter adapter = new SqlDataAdapter(query, connection))
        {
            connection.Open();
            DataTable contactTable = new DataTable();
            adapter.Fill(contactTable);

            lstContactList.DisplayMember = "Name";
            lstContactList.ValueMember = "Id";
            lstContactList.DataSource = contactTable;


        }
    }

here is the method that i try to use to change the labels:

        private void PopulateContactLabels()
    {
        string query = "SELECT * FROM Contact";

        using (connection = new SqlConnection(connectionString))
        using (SqlCommand command = new SqlCommand(query, connection))
        using (SqlDataAdapter adapter = new SqlDataAdapter(command))
        {
            connection.Open();
            SqlDataReader rdr = command.ExecuteReader();

            while (rdr.Read())
            {
                lblContactName.Text = rdr["Name"].ToString();
                lblCompany.Text = rdr["Company"].ToString();
                lblOccupation.Text = rdr["Occupation"].ToString();
                lblPhoneNumber.Text = rdr["PhoneNumber"].ToString();
                lblEmail.Text = rdr["Email"].ToString();
            }
            rdr.Close();
            connection.Close();

        }
    }

And it does change the labels, but it selects the last contact added to the database, and it doesn't change when i select another contact.

What am i doing wrong?

Answer 1

That's because in your PopulateContactLabels method, you are selecting the whole Contact table and then reading through the whole list, so it's always the last one which is shown.

You need a query more like SELECT * FROM Contact WHERE ContactId = @contactID, and then add the contactId (or whatever value you are using to find the contact) as a parameter on the SqlCommand object.