locked
C# How to assign certain columns of a datagridview with certain columns of a datatable that is loaded with sqlserver data RRS feed

  • Question

  • Hi:

    I have a datagridview that i'm trying to populate with a datatable that was filled with values from sql server.  There are 8 columns in the datagridview with two of the columns having checkboxes.  When I put in my code,

    if (dt.Rows.Count > 0 && dt != null)
    {
                           
       dataGridView.DataSource = dt;
                           
    }

    I get nothing.  Before I had the checkboxes, I had no problem with loading the data.
    How do you assign the datatable columns to the proper column of the datagridview?

    Also, I created my datagridview in the program _Load part of my code.  Should I create the columns there as well, if the data is not loaded until a certain button is clicked later on?


    column 1 is a checkboxcolumn
    column 2 is an ID number
    column 3 is a Holiday name
    column 4 is Day of week
    column 5 is Month
    column 6 is Day
    column 7 is the year
    column 8 is another checkboxcolumn

    The datagridview columns are shown below.  Right after Naming the first and last columns, I add a Checkbox to it.


    dataGridView.Columns[0].Name = "Holiday Approval";
    AddCheckBoxForDataGridView(dataGridView, "Holiday Approval");
    dataGridView.Columns[1].Name = "Holiday ID";
    dataGridView.Columns[2].Name = "Holiday";
    dataGridView.Columns[3].Name = "Day of Week";
    dataGridView.Columns[4].Name = "Month";
    dataGridView.Columns[5].Name = "Day";
    dataGridView.Columns[6].Name = "Year";
    dataGridView.Columns[7].Name = "Delete";
    AddCheckBoxForDataGridView(dataGridView, "Delete");

    Thank you very much for your time.

    Sincerely,

    Bosco

    • Moved by CoolDadTx Monday, July 21, 2014 4:43 PM Winforms related
    Monday, July 21, 2014 2:36 PM

Answers

All replies

  • If you create yourself columns, you have the autogeneratecolumns property to false

    http://msdn.microsoft.com/en-us/library/system.windows.forms.datagridview.autogeneratecolumns(v=vs.110).aspx

    Did you know that there is a forum especially for your kind of problems

    http://social.msdn.microsoft.com/Forums/windows/en-US/home?forum=winformsdatacontrols


    Success
    Cor

    • Marked as answer by bosco dog Wednesday, July 23, 2014 11:12 PM
    Monday, July 21, 2014 3:11 PM
  • Thanks for answering.

    I looked on the forum that you suggested, and unfortunately, I could not find the answer to my question.  I think the closest answer was found on this site:

    http://msdn.microsoft.com/en-us/library/system.windows.forms.datagridviewcolumn.datapropertyname(v=vs.110).aspx

    But, this is not a datatable.  It is great information for the future, but it does not fit what I need.

     I just need to know how to assign a column from a datatable to a column in a datagridview. 

    example:

    assign column1 of datatable to column 2 of datagridview

    assign column 2 of datatable to column 3 of datagridview and  . . . so forth

    thank you for your time

    Bosco


    • Edited by bosco dog Monday, July 21, 2014 4:36 PM mistake
    Monday, July 21, 2014 4:35 PM
  • There is a complete sample how to assign selectable columns of a datatable to a datagridview in the first link I gave you.


    Success
    Cor

    Monday, July 21, 2014 4:44 PM
  • Thanks anyways,  I found the answer that I needed.  Here it is:

                             for (int i = 0; i < dt.Rows.Count; i++)
                                {
                               
                                     dataGridView.Rows.Add();
                                    dataGridView.Rows[i].Cells[1].Value = dt.Rows[i].ItemArray[0];
                                    dataGridView.Rows[i].Cells[2].Value = dt.Rows[i].ItemArray[1];
                                    dataGridView.Rows[i].Cells[3].Value = dt.Rows[i].ItemArray[2];
                                    dataGridView.Rows[i].Cells[4].Value = dt.Rows[i].ItemArray[3];
                                    dataGridView.Rows[i].Cells[5].Value = dt.Rows[i].ItemArray[4];
                                    dataGridView.Rows[i].Cells[6].Value = dt.Rows[i].ItemArray[5];

                                }

    Thanks again for your time.

    Sincerely,

    Bosco

    For those who sees this. 

    This is not databinding, this is dumb 1990 style setting of data to a control, and therefore OT in this forum.

    You cannot use this to update the data in the normal datagridview way using the DataAdapter or any other provider from this millenium.


    Success
    Cor


    Tuesday, July 22, 2014 10:02 AM
  • I would advise to set a break point on the line that sets dt to the DataSource of the DataGridView. When the break point is hit hover over any instance of dt and view the data.

    if (dt.Rows.Count > 0 && dt != null)
    {
                            
       dataGridView.DataSource = dt;
                            
    }

    There is zero need to set column names as the column names will be the field names of the underlying DataTable, instead set the header text for the columns in the DataGridview. Done this way if the first column in the DataTable is Boolean then it will be a DataGridViewCheckBoxColumn in the DataGridView etc. As Cor pointed out, there do not cycle thru the DataTable to add rows to the DataGridView as this is primitive in nature and defeats the purpose of a DataTable, might as well use a DataReader to populate the DataGridView.


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem.

    Tuesday, July 22, 2014 11:23 AM
  • Hi Bosco,

    Your solution is not common databinding way but it might be a solution to manually do it. Do you mind to paste more code on this problem if you want see the reason of your problem?

    For example, in my mind there are so many reasons:

    1. May the datatable actually a dataset and you haven't assign the right table to your DGV?

    2. What if you give your datatable a table name. When I was coding with a combobox I meet this problem because of no table name. You can just give it a try.

    If you are interest in it, feel free to paste some more code so that we may be able to reproduce your issue.

    Best regards,



    Barry
    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Wednesday, July 23, 2014 6:26 AM
  • Cor: 

    I got it to work as per that site that you sent.  It was a combination of that site and what Kevin said below that made me get it to work like the pros like you do. I hope this is right. But, It does work without all that crazy looping code that I had in the beginning. Thank you so much. You have no clue how I felt when my data popped in with these changes. I felt like I won the lottery. Thanks again. Here's the code:

     public void GetHolidays(int empID, DataGridView dataGridView, string procedure)
            {

                if (conn != null && conn.State == ConnectionState.Closed)
                {
                    conn.Open();
                }

                try
                {
                    using (SqlCommand cmd = new SqlCommand(procedure, conn))
                    {
                        cmd.CommandType = CommandType.StoredProcedure;
                        cmd.Parameters.AddWithValue("@empID", SqlDbType.Int).Value = @empID;

                        DataTable dt = new DataTable();

                    


                        dt.Load(cmd.ExecuteReader(CommandBehavior.CloseConnection));

                        dataGridView.AutoResizeColumns();
                        dataGridView.AllowUserToOrderColumns = true;


                        // Configure the details DataGridView so that its columns automatically
                        // adjust their widths when the data changes.
                        dataGridView.AutoSizeColumnsMode =
                            DataGridViewAutoSizeColumnsMode.AllCells;
                       
                        if (dt.Rows.Count > 0 && dt != null)
                        {
                            if (procedure.Equals("proc_GetScheduledHolidays", StringComparison.Ordinal))
                            {
                                // Note:  I took all that crazy looping code out.
                                dataGridView.AutoGenerateColumns = false;
                                AddChecksToDataGridView(dataGridView);
                                dataGridView.DataSource = dt;
                               
                            }
                            else
                            {
                                AddCheckBoxForDataGridView(dataGridView, "Holiday Approval", 0);
                                dataGridView.DataSource = dt;
                                  
                            }

                        }
                        else
                        {
                            if (procedure.Equals("proc_GetScheduledHolidays", StringComparison.Ordinal))
                            {
                               
                                dataGridView.ColumnCount = 6;
                                dataGridView.DataSource = null;
                                DataRow dr = dt.NewRow();
                                string[] rowString = { "", "", "", "", "", "" };
                                dataGridView.Rows.Add(rowString);

                            }
                            else
                            {
                                dataGridView.ColumnCount = 4;
                                dataGridView.Columns[0].Name = "Holiday Date";
                                dataGridView.Columns[1].Name = "Holiday ID";
                                dataGridView.Columns[2].Name = "Holiday";
                                dataGridView.Columns[3].Name = "Scheduled Day Off";
                                dataGridView.DataSource = null;
                                DataRow dr = dt.NewRow();
                                string[] rowString = { "No", "More", "Days", "Available" };
                                dataGridView.Rows.Add(rowString);
                                dataGridView.AllowUserToAddRows = false;
                            }

                        }
                        dataGridView.AllowUserToAddRows = false;
                        dataGridView.Columns[0].ReadOnly = true;
                        dataGridView.Columns[1].ReadOnly = true;
                        dataGridView.Columns[2].ReadOnly = true;
                        dataGridView.Columns[3].ReadOnly = true;

                    }
                }
                catch (NullReferenceException nullex)
                {
                    MessageBox.Show(nullex.Message);

                }
                catch (System.Data.SqlClient.SqlException ex)
                {
                    MessageBox.Show("There was an error in executing the SQL." +
                            "\nError Message:" + ex.Message, "SQL");
                }
                finally
                {
                    conn.Close();
                }

            }
            /* This function is called by the GetHolidays function.  This will dress up the DataGridView on the
             * right hand side of the form.  This DataGridView holds the dates that an employee has requested
             * to take his/her floating holiday. */
            public void AddChecksToDataGridView(DataGridView dataGridView)
            {

                // Initialize and add a check box column.
                DataGridViewCheckBoxColumn chkColumn = new DataGridViewCheckBoxColumn();
               
                chkColumn.DataPropertyName = "Holiday Approval";
                chkColumn.HeaderText = "Holiday Approval";
                chkColumn.Name = "Holiday Approval";
                chkColumn.ThreeState = true;
                dataGridView.Columns.Insert(0, chkColumn);
                dataGridView.Columns[0].ReadOnly = false;
                chkColumn.AutoSizeMode = DataGridViewAutoSizeColumnMode.DisplayedCells;

                // Initialize and add a new column
                DataGridViewColumn column = new DataGridViewColumn();
                column.CellTemplate = new DataGridViewTextBoxCell();
                column.HeaderText = "Holiday ID";
                column.DataPropertyName = "Holiday ID";
                column.Name = "Holiday ID";
                dataGridView.Columns.Insert(1, column);


                // Initialize and add a new column
                column = new DataGridViewColumn();
                column.CellTemplate = new DataGridViewTextBoxCell();
                column.HeaderText = "Holiday";
                column.DataPropertyName = "Holiday";
                column.Name = "Holiday";
                dataGridView.Columns.Insert(2, column);


                // Initialize and add a new column
                column = new DataGridViewColumn();
                column.CellTemplate = new DataGridViewTextBoxCell();
                column.HeaderText = "Day of Week";
                column.DataPropertyName = "Day of Week";
                column.Name = "Day of Week";
                dataGridView.Columns.Insert(3, column);


                // Initialize and add a new column
                column = new DataGridViewColumn();
                column.CellTemplate = new DataGridViewTextBoxCell();
                column.HeaderText = "Month";
                column.DataPropertyName = "Month";
                column.Name = "Month";
                dataGridView.Columns.Insert(4, column);

                // Initialize and add a new column
                column = new DataGridViewColumn();
                column.CellTemplate = new DataGridViewTextBoxCell();
                column.DataPropertyName = "Day";
                column.HeaderText = "Day";
                column.Name = "Day";
                dataGridView.Columns.Insert(5, column);

                // Initialize and add a new column
                column = new DataGridViewColumn();
                column.CellTemplate = new DataGridViewTextBoxCell();
                column.DataPropertyName = "Year";
                column.HeaderText = "Year";
                column.Name = "Year";
                dataGridView.Columns.Insert(6, column);


                // Instead, I added a delete button.
                DataGridViewButtonColumn button = new DataGridViewButtonColumn();
                button.HeaderText = "Delete";
                button.Text = "Delete";
                button.HeaderText = "Delete";
                button.FlatStyle = FlatStyle.System;
                button.DefaultCellStyle.BackColor = Color.AliceBlue;
                button.DefaultCellStyle.ForeColor = Color.Black;
                dataGridView.Columns.Insert(7, button);

            }

    Thanks again, 

    Bosco


    • Edited by bosco dog Wednesday, July 23, 2014 11:33 PM mistake
    Wednesday, July 23, 2014 11:12 PM
  • Kevin:  I got it to work with the suggestions that you and Cor gave.  Thank you so much.  I got rid of all that looping code I originally had.

    Thanks again.  I put the code up in the Cor section.

    Bosco

    • Marked as answer by bosco dog Wednesday, July 23, 2014 11:14 PM
    Wednesday, July 23, 2014 11:14 PM
  • Barry, I got it to work just like Cor and Kevin advised me to do.  I'm so excited. 

    Thanks again for your encouragement.

    Bosco

    Wednesday, July 23, 2014 11:16 PM
  • Glad to know that:)


    Barry
    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Thursday, July 24, 2014 5:51 AM