VS C# Write data set to another table

I have a dataset defined as MyDataSet= new System.Data.DataSet() along with a DataAdapter called MyDataAdapter. I then use _commandBuilder = new SqlCommandBuilder(MYDataAdapter) and finally MyDataAdapter.Fill(MyDataSet) to get the data and present it to the user via a data grid view. On the dgv I did add a Checkbox. Now on the screen the user sees values for order number, customer, amount plus the checkbox. The only field the user can change is the checkbox. If the user checks the box, I do not want to write these order numbers to another table. So the question here is:
How do I set up the code under the 'Save' button so it will write this dataset to another table but only for those records that have not been checked? Do I have to loop through each record in the dataset looking for the unchecks one and write only them or is there a way to commit this entire dataset to another table? How do I get this commit to write to another table? I have included the complete code below.

      private void DisplayOpenSalesOrders()
        {
            /* Define dataset for open sales orders in My */
            MyDataSet = new System.Data.DataSet();
            MyDataSet.CaseSensitive = false;

            /* Define the data command for the open sales orders in My */
            MyDataCommand = new System.Data.SqlClient.SqlCommand();
            MyDataCommand.Connection = MyDataConnection;

            MyDataCommand.CommandText = "SELECT * FROM SelectOpenOrders WHERE DOCDATE <= '" + txtThroughDate.Text + "' ORDER BY DOCDATE";
           
            /* Define the data adapter for the open sales orders */
            MyDataAdapter = new System.Data.SqlClient.SqlDataAdapter();
            MyDataAdapter.SelectCommand = MyDataCommand;

            _commandBuilder = new SqlCommandBuilder(MyDataAdapter);

            /* Populate the data set */
            MyDataAdapter.Fill(MyDataSet);

            dgvOpenSalesOrders.DataError += new DataGridViewDataErrorEventHandler(dgvOpenSalesOrders_DataError);

            dgvOpenSalesOrders.RowHeadersVisible = false;
            dgvOpenSalesOrders.DataSource = MyDataSet.Tables[0].DefaultView;
            dgvOpenSalesOrders.AutoGenerateColumns = true;
            dgvOpenSalesOrders.DefaultCellStyle.NullValue = ' ';
            dgvOpenSalesOrders.AllowUserToAddRows = false;
            dgvOpenSalesOrders.AllowUserToDeleteRows = false;

            dgvOpenSalesOrders.Columns[0].Width = 80;
            dgvOpenSalesOrders.Columns[0].ReadOnly = true;
            dgvOpenSalesOrders.Columns[0].HeaderText = "Doc Date";
            dgvOpenSalesOrders.Columns[0].DefaultCellStyle.Tag = "TOUPPER";

            dgvOpenSalesOrders.Columns[1].Width = 110;
            dgvOpenSalesOrders.Columns[1].ReadOnly = true;
            dgvOpenSalesOrders.Columns[1].HeaderText = "Req Ship Date";
            dgvOpenSalesOrders.Columns[1].DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleCenter;
            dgvOpenSalesOrders.Columns[1].DefaultCellStyle.Tag = "TOUPPER";

            dgvOpenSalesOrders.Columns[2].Width = 100;
            dgvOpenSalesOrders.Columns[2].ReadOnly = true;
            dgvOpenSalesOrders.Columns[2].HeaderText = "Doc Type";
            dgvOpenSalesOrders.Columns[2].DefaultCellStyle.Tag = "TOUPPER";

            dgvOpenSalesOrders.Columns[3].Width = 120;
            dgvOpenSalesOrders.Columns[3].ReadOnly = true;
            dgvOpenSalesOrders.Columns[3].HeaderText = "Doc Number";
            dgvOpenSalesOrders.Columns[3].DefaultCellStyle.Tag = "TOUPPER";

            dgvOpenSalesOrders.Columns[4].Width = 100;
            dgvOpenSalesOrders.Columns[4].ReadOnly = true;
            dgvOpenSalesOrders.Columns[4].HeaderText = "Cust Number";
            dgvOpenSalesOrders.Columns[4].DefaultCellStyle.Tag = "TOUPPER";

            dgvOpenSalesOrders.Columns[5].Width = 300;
            dgvOpenSalesOrders.Columns[5].ReadOnly = true;
            dgvOpenSalesOrders.Columns[5].HeaderText = "Cust Name";
            dgvOpenSalesOrders.Columns[5].DefaultCellStyle.Tag = "TOUPPER";

            dgvOpenSalesOrders.Columns[6].Width = 100;
            dgvOpenSalesOrders.Columns[6].ReadOnly = true;
            dgvOpenSalesOrders.Columns[6].DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleRight;
            dgvOpenSalesOrders.Columns[6].DefaultCellStyle.Format = "c";
            dgvOpenSalesOrders.Columns[6].HeaderText = "Doc Amount";

            DataGridViewCheckBoxColumn colCheckBox = new DataGridViewCheckBoxColumn();
            colCheckBox.HeaderText = "Keep";
            colCheckBox.Name = "colCheck";
            colCheckBox.Width = 36;
            dgvOpenSalesOrders.Columns.Insert(7, colCheckBox);

            dgvOpenSalesOrders.EditMode = DataGridViewEditMode.EditOnEnter;
            dgvOpenSalesOrders.Focus();
        }

        private void dgvOpenSalesOrders_DataError(object sender, DataGridViewDataErrorEventArgs anError)
        {
        }

        private void btnDisplay_Click(object sender, EventArgs e)
        {
            DisplayOpenSalesOrders();
        }
    }
LVL 1
rwheeler23Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Daniel Van Der WerkenIndependent ConsultantCommented:
You have to loop through the data grid view and grab the value of the check box. What I'd do is create a method that loops through the rows of the data grid view and looks to see if the check box is selected. Then, "save" that information to an object that represents the data you want to insert into the database. Then, when all of the rows have been read, write out that data. There are lots of ways you can perform this action via the code.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
rwheeler23Author Commented:
This is my first shot at writing an external database to a T-SQL table. Can you point me to any sample code? I am curious about looping through a dataset, saving only the qualifying records to an object and then writing that object to SQL.
0
rwheeler23Author Commented:
I am trying this but it keeps telling me the table rbsSOPTRXHIST does not exist even though it does. The data connection for this is the same as the one used to read the data so I know the data connection is correct.

        private void bthTrxHist_Click(object sender, EventArgs e)
        {
            var adapterForSOPTrxHist = new SqlDataAdapter("SELECT * FROM GPDataSet.Tables[0] WHERE colcheck = false", GPDataConnection);
            var builderForSOPTrxHist = new SqlCommandBuilder(adapterForSOPTrxHist);
            adapterForSOPTrxHist.Update(GPDataSet, "rbsSOPTRXHIST");
        }
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

rwheeler23Author Commented:
OK, how do I loop through the records in the datagrid? I discovered how to loop through the records in the dataset but that does me no good because the check column is not part of the dataset.
0
rwheeler23Author Commented:
Why does this code give me the message "Object not set to an instance of an object"? The field causing the issue is colCheck and that in the Checkbox that was added to the dataview.

        private void bthTrxHist_Click(object sender, EventArgs e)
        {
            TRNSDATE = DateTime.Now;
            TRNSFLAG = false;

            foreach(DataGridViewRow row in dgvOpenSalesOrders.Rows)
            {
                try
                {
                    MessageBox.Show(row.Cells["SOPNUMBE"].Value.ToString() + "Keep: " + row.Cells["colCheck"].Value.ToString());
                }
                catch (Exception ex)
                {
                    string eMsg = "004: ERROR: " + ex.Message;
                    if (StackTraceWanted) eMsg += "\n" + ex.StackTrace;
                    MessageBox.Show(eMsg);
                }
            }
          }
0
rwheeler23Author Commented:
I discovered that I first had to loop through the add check column field to give the column the value of false and not null. You got me started. Thanks.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
C#

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.