Click here to Skip to main content
15,124,487 members
Please Sign up or sign in to vote.
5.00/5 (2 votes)
See more:
Hello Everyone

I have a DataGridView on my Form which is being populated with database records on the click event button.
How can I populate another two column template programatically at run time?

The two column template are Qty In Stock and Status

This is my datagridview what looks like when populated on click event button...

===============================================================
FoodName        FoodType     Qty In Stock     Status
===============================================================
Olives          Starter                     
Soup            Starter                     
Caprese	        Starter
Bruschetta	Starter
Mushroom	Starter
Antipasto	Starter
Scallops	Starter
Calamari	Starter
Crab Avocado	Starter
Pizza Bread	Starter
===============================================================


And this is datagridview what I want to look like

=================================================================
FoodName        FoodType     Qty In Stock     Status
=================================================================
Olives          Starter      0                Allways On Stock
Soup            Starter      0                Allways On Stock
Caprese	        Starter      0                Allways On Stock
Bruschetta	Starter      0                Allways On Stock
Mushroom	Starter      0                Allways On Stock
Antipasto	Starter      0                Allways On Stock
Scallops	Starter      0                Allways On Stock
Calamari	Starter      0                Allways On Stock
Crab Avocado	Starter      0                Allways On Stock
Pizza Bread	Starter      0                Allways On Stock
=================================================================


Here the code of datagridview populated on click event button...

private DataGridViewTextBoxColumn ColFoodQtyStock = new DataGridViewTextBoxColumn();
        private DataGridViewTextBoxColumn ColFoodStatus = new DataGridViewTextBoxColumn();

        private void cmdStarters_Click(object sender, EventArgs e)
        {
            OleDbConnectionStringBuilder connBuilder = new OleDbConnectionStringBuilder();
            connBuilder.DataSource = @"C:\Users\AP_AE\Desktop\DTPOS_APP\DataBase\DtposMenu.accdb";
            connBuilder.Provider = "Microsoft.ACE.OLEDB.12.0";
            connBuilder.Add("Jet OLEDB:Engine Type", "5");

            // Food SQL Query
            string foodTypeSql = @"SELECT FoodName, FoodType FROM Food WHERE FoodType = @foodType";
            using (OleDbConnection conn = new OleDbConnection(connBuilder.ConnectionString))
            {
                dataGridView1.Columns.Clear();
                dataGridView1.RowTemplate.Height = 60;
                //====================================\\
                dataGridView1.Visible = true;
                dataGridView2.Visible = false;
                try
                {
                    OleDbCommand foodsCommand = new OleDbCommand(foodTypeSql, conn);
                    OleDbParameter foodType = foodsCommand.Parameters.Add("@foodType", OleDbType.VarChar, 15);
                    OleDbDataAdapter foodsDa = new OleDbDataAdapter(foodsCommand);
                    //DataRow dr;
                    DataSet ds = new DataSet();
                    conn.Open();
                    foodType.Value = "Starter";
                    foodsDa.Fill(ds, "Food_table");
                    
                    conn.Close();
                    dataGridView1.DataSource = ds;
                    dataGridView1.DataMember = "Food_table";

                    dataGridView1.Columns.AddRange(ColFoodQtyStock, ColFoodStatus);
                    
                    DataGridViewCellStyle dataGridViewCellStyle1 = new DataGridViewCellStyle();
                    this.dataGridView1.ColumnHeadersDefaultCellStyle = dataGridViewCellStyle1;
                    dataGridViewCellStyle1.Font = new Font("Verdana", 20.25F, System.Drawing.FontStyle.Bold, System.Drawing.GraphicsUnit.Point, ((byte)(0)));

                    this.dataGridView1.Columns[0].Width = 420;
                    this.dataGridView1.Columns[1].Width = 180;
                    this.dataGridView1.Columns[2].Width = 300;
                    this.dataGridView1.Columns[3].Width = 308;

                    // ColStatus 
                    ColFoodStatus.HeaderText = "Status";
                    ColFoodStatus.Name = "ColFoodStatus";

                    // ColQtyStock
                    ColFoodQtyStock.HeaderText = "Quantity In Stock";
                    ColFoodQtyStock.Name = "ColFoodQtyStock";

                }
                catch (Exception ex)
                {
                    MessageBox.Show("Error: " + ex);
                }
            }
        }


Could someone help me to solve this problem please

Thanks in advance


lapeci
Posted
Updated 8-Jan-11 17:23pm
v2
Comments
thatraja 8-Jan-11 23:12pm
   
Check my updated answer LAPEC
Sandeep Mewara 9-Jan-11 2:54am
   
Nicely framed.
thatraja 9-Jan-11 9:22am
   
Dude you can generate dynamic columns by my code & now you just need to genenerate dynamic columns in dataset/database as already I said. Check my updated answer again.
LAPEC 9-Jan-11 11:36am
   
Hi Thatraja

I'm trying to follow what you said but still I don't understand (I'm trying to learn the C# programming because I'm new to this, never came across before), could you please have a look at my code and tell me where to make changes please.

kind regards

lapeci
thatraja 9-Jan-11 20:35pm
   
Check my one more updated answer & hope your issue solved.

1 solution

I think you can generate the default values(for Columns Qty In Stock, Status) in your datasource(In dataset or database) & then you can bind with Datagridview.

Have a look at these (Involves dynamic columns creation too)


101 Ways to Manipulate the DataGridView Control - Part 1
[^]

101 Ways to Manipulate the DataGridView Control - Part 2[^]

[EDIT]
I think you forgot to fill the value for DataPropertyName property, so assign the value for Design columns + runtime columns.

Call the below method in form_load in your application.
C#
public void BindGridView()
{
    DataTable table = new DataTable();
    table.Columns.Add("FoodName", typeof(string));
    table.Columns.Add("FoodType", typeof(string));
    table.Columns.Add("QtyInStock", typeof(int));
    table.Columns.Add("Status", typeof(string));
    string[] arrFoodName = new string[] { "Olives", "Soup", "Caprese", "Bruschetta", "Mushroom", "Antipasto", "Scallops", "Calamari", "Crab Avocado", "Pizza Bread" };
    string[] arrFoodType = new string[] { "Starter", "Other" };
    bool IsStock = false;
    long Qty = 0;
    for (int i = 0; i < 10; i++)
    {
        if (i > 5)
        {
            Qty = i * 5;
            IsStock = true;
        }
        if (IsStock)//A dummy condition to check the stock status
        {
            table.Rows.Add(arrFoodName[i], arrFoodType[1], Qty, "Always On Stock");
        }
        else
        {
            table.Rows.Add(arrFoodName[i], arrFoodType[0], 0, "No Stock");
        }
    }
    dataGridView1.AutoGenerateColumns = false;
    dataGridView1.DataSource = table;

    DataGridViewTextBoxColumn ColFoodQtyStock = new DataGridViewTextBoxColumn();
    DataGridViewTextBoxColumn ColFoodStatus = new DataGridViewTextBoxColumn();

    ColFoodStatus.HeaderText = "Status";
    ColFoodStatus.DataPropertyName = "Status";//You forgot to fill this one
    ColFoodStatus.Name = "Status";

    ColFoodQtyStock.HeaderText = "Quantity In Stock";
    ColFoodQtyStock.DataPropertyName = "QtyInStock";//You forgot to fill this one
    ColFoodQtyStock.Name = "QtyInStock";
    dataGridView1.Columns.AddRange(ColFoodQtyStock, ColFoodStatus);
}

Now It will display four columns with data.
Note : check the column names mine with yours.

Sample for adding dynamic columns & rows to datatable
C#
DataTable dt = new DataTable();
DataRow myNewRow;
myNewRow = dt.NewRow();
myNewRow.Table.Columns.Add("QtyInStock");
myNewRow.Table.Columns.Add("Status");
myNewRow["QtyInStock"] = 1;
myNewRow["Status"] = "No Stock" ;
dt.Rows.Add(myNewRow);

[EDIT]

[Final EDIT]
If you want the 0, "Always on Stock" as default for your newly generated run time columns(QtyInStock & Status) then change the query dude.
C#
string foodTypeSql = @"SELECT FoodName, FoodType, 0 AS QtyInStock, 'Always on Stock' AS Status FROM Food WHERE FoodType = @foodType";

You can CASE statement[^] for defining the row values based on conditions. That's it.
[/Final EDIT]


BTW I'm glad you came with good question with your snippet(I like the style of your question), please keep continue always dude.

Cheers. :thumbsup:
   
v5
Comments
LAPEC 8-Jan-11 21:55pm
   
Hi Thatraja

Thanks for answering my qustion, but I think you probably didn't understand my question.
Your answer looks perfectly right, but its not working in my program the way I wanted.

If you look my code inside the cmdStarters_Click event, I'm generating the two columns with records from the databse (such as: FoodName and FoodType). Now the other two columns (such as: Quantity In Stock and Status) they can not be generated from database, they must be generated at run time as template columns and the rows of this two columns must be filled with data like so...

[code]=================================================================
FoodName FoodType Qty In Stock Status
=================================================================
Olives Starter 0 Allways On Stock
Soup Starter 0 Allways On Stock
Caprese Starter 0 Allways On Stock
Bruschetta Starter 0 Allways On Stock
Mushroom Starter 0 Allways On Stock
Antipasto Starter 0 Allways On Stock
Scallops Starter 0 Allways On Stock
Calamari Starter 0 Allways On Stock
Crab Avocado Starter 0 Allways On Stock
Pizza Bread Starter 0 Allways On Stock
=================================================================[/code]

Kind regards

lapeci
thatraja 8-Jan-11 23:11pm
   
Check my updated answer

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900