Retrieve data from database into DataGridView C#

Question

I have two tables in my database Category (Cat_id, Cat_name) and Products. Cat_id is a foreign key in the Products table.

When I retrieve product details from the Products table into a DataGridView, I want to show Cat_id column's value with its name (by Cat_name) not id itself.

How do I do that? Please help me. Below is a sample code.

private void searchIconPIctureBox_Click(object sender, EventArgs e)
{
        string product_txt = searchTxt.Text;

        if ((product_txt) != "")
        {
            try
            {
                conn.Open();
                com.Connection = conn;

                string search_Product = searchTxt.Text;

                com.CommandText = "select Name, Type, Amount, Price, Cat_id from Product_records where Name = search_Product ";

                SqlDataReader dtr = com.ExecuteReader();
                DataTable dttable = new DataTable();
                dttable.Load(dtr);             
                ProductGridView.DataSource = dttable;
                
                conn.Close();
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.ToString());
            }
        }
}

Deathstick_59081 · Answer

Your SQL query should use an inner join between Products and Categories tables.

select Name, Type, Amount, Price, c.Name
from Personal_records
inner join Category c on records.Cat_id = c.Cat_id
where Name = search_Product

user_1410664 · Answer

You can use a DataGridViewComboBox to do it for you

I do it in this video https://www.youtube.com/watch?v=zrtjZlTYLQA from 3:50 onwards, but the video leading up it shows how I get to a situation where it'll work with creating the dataset and putting it on a form

Here's a text recap if you're a reader rather than a watcher. You might have done some of this already:

Add a dataset to your project
Connect the dataset to your db (right click, add tableadapter, finish the wizard, having done a SELECT * FROM products, repeat for categories)
Switch to form designer
Open datasources window from view menu Other Windows submenu
Drag the products grid to the form
Edit its columns
Change the category column from textbox to combobox
Set its datasource to be the categories table in the dataset, set the displaymember to the name and the valuemember to the id
Run the project

Retrieve data from database into DataGridView C#

2 Answers