To get the name from the ID of the value in the selected row in datagridview

Question

I hide Author_ID and Publisher_ID in gridview .I want when I click a row , author and publisher combobox texts fill with selected row values. There are publisher and author id in rows as foreign key but i want to show their name on combobox text . How can access them.

    private  void gridViewBook_CellContentClick(object sender, DataGridViewCellEventArgs e)
    {

        gridViewBook.CurrentRow.Selected = true;

        txtID.Text = gridViewBook.Rows[e.RowIndex].Cells["BOOK_ID"].FormattedValue.ToString();
        txtISBN.Text = gridViewBook.Rows[e.RowIndex].Cells["ISBN"].FormattedValue.ToString();
        txtName.Text = gridViewBook.Rows[e.RowIndex].Cells["BOOK_NAME"].FormattedValue.ToString();
        txtPage.Text = gridViewBook.Rows[e.RowIndex].Cells["PAGE"].FormattedValue.ToString();
        txtLang.Text = gridViewBook.Rows[e.RowIndex].Cells["LANGUAGE"].FormattedValue.ToString();
        comboBoxAuthor.Text= gridViewBook.Rows[e.RowIndex].Cells["AUTHOR_ID"].FormattedValue.ToString();
        
        comboBoxPublisher.Text = gridViewBook.Rows[e.RowIndex].Cells["PUBLISHER_ID"].FormattedValue.ToString();
    }

I could only access their ID . I filled the comboboxes like this.

    private void fillAuthorBox()
    {
        conn.Open();

        SqlCommand cmd = new SqlCommand("SELECT * FROM AUTHOR ", conn);
        SqlDataAdapter da = new SqlDataAdapter(cmd);
        DataTable dt = new DataTable();
        da.Fill(dt);

        comboBoxAuthor.ValueMember = "AUTHOR_ID";
        comboBoxAuthor.DisplayMember = ("AUTHOR_FULLNAME");
        comboBoxAuthor.DataSource = dt;

        conn.Close();
    }

    private void fillPublisherBox()
    {
        conn.Open();

        string query = " SELECT * FROM PUBLISHER ";
        SqlCommand cmd = new SqlCommand(query, conn);
        SqlDataAdapter da = new SqlDataAdapter(cmd);
        DataTable dt = new DataTable();
        da.Fill(dt);

        comboBoxPublisher.ValueMember = "PUBLISHER_ID";
        comboBoxPublisher.DisplayMember = "PUBLISHER_NAME";
        comboBoxPublisher.DataSource = dt;

        conn.Close();

    }

Answer 1

I'm looking at fillAuthorBox() and fillPublisherBox() first. Somewhere you have code that calls these methods. We can't see that in the question, so I'll pretend it's a generic form load method, like this:

void form_Load(...)
{
    fillAuthorBox();
    fillPublisherBox();
}

We can do better. There are some common mistakes in those methods, that I will correct below:

//only re-use the connection string, NOT the whole connection object
private connString As String = "connection string here";

//return a value, don't touch any form controls
// even better if these methods are moved to a different class
public DataTable getAuthorNames()
{        
    using (var conn = new SqlConnection(connString))
    // Don't SELECT * if you don't need all the fields!
    using (var cmd = new SqlCommand("SELECT AUTHOR_ID, AUTHOR_FULLNAME FROM AUTHOR ", conn))
    using (var da = new SqlDataAdpater(cmd))
    { 
        var dt = new DataTable();
        da.Fill(dt); // Fill() automatically opens and closes the connection object
        return dt;
    } 
    //using blocks make sure your items are fully disposed when the block closes, 
    // **even if an exception is thrown!**
}

public DataTable getPublisherNames()
{
    ​using (var conn = new SqlConnection(connString))
    using (var cmd = new SqlCommand("SELECT PUBLISHER_ID, PUBLISHER_NAME FROM PUBLISHER", conn))
    using (var da = new SqlDataAdpater(cmd))
   ​ { 
        ​var dt = new DataTable();
       ​ da.Fill(dt);
       ​ return dt;
   ​ }     
}

Now my original form_Load() looks like this:

void form_Load(...)
{
    comboBoxAuthor.ValueMember = "AUTHOR_ID";
    comboBoxAuthor.DisplayMember = "AUTHOR_FULLNAME";
    comboBoxAuthor.DataSource = getAuthorNames();

    comboBoxPublisher.ValueMember = "PUBLISHER_ID";
    comboBoxPublisher.DisplayMember = "PUBLISHER_NAME";
    comboBoxPublisher.DataSource = getPublisherNames();
}

---

From here we can move on to selecting the correct entry when someone clicks a row in your grid. To do this, rather than setting the Text property we want to change which item is selected. Since we know the ValueItem, we can do it via the SelectedValue property.

private  void gridViewBook_CellContentClick(object sender, DataGridViewCellEventArgs e)
{
    gridViewBook.CurrentRow.Selected = true;

    txtID.Text = gridViewBook.Rows[e.RowIndex].Cells["BOOK_ID"].FormattedValue.ToString();
    txtISBN.Text = gridViewBook.Rows[e.RowIndex].Cells["ISBN"].FormattedValue.ToString();
    txtName.Text = gridViewBook.Rows[e.RowIndex].Cells["BOOK_NAME"].FormattedValue.ToString();
    txtPage.Text = gridViewBook.Rows[e.RowIndex].Cells["PAGE"].FormattedValue.ToString();
    txtLang.Text = gridViewBook.Rows[e.RowIndex].Cells["LANGUAGE"].FormattedValue.ToString();

    comboBoxAuthor.SelectedValue = gridViewBook.Rows[e.RowIndex].Cells["AUTHOR_ID"].Value;        
    comboBoxPublisher.SelectedValue = gridViewBook.Rows[e.RowIndex].Cells["PUBLISHER_ID"].Value;
}

But be careful here! I'm a bit out of practice with WinForms, and I don't recall how forgiving this will be for mismatched types. It's likely the combobox ValueMember is an int or DataGridViewCell, but we're using a string to set the SelectedValue, and that might not work as well. You may have to convert the string back to int, or find the index of your value to update the SelectedIndex property instead.