Saving a modified Datagridview

See more:

VB

SQL

I have a datagridview where i added 4 columns to 3 columns of data.
Of the added columns, 1 is filled by a checkbox selection, 2 are blank for User Entry and one is filled with a Date Selected in a DateTimePicker.

Everything displays correctly, just like I want it, but when I save this data to a New/Different table, it only saves the information pulled from my select statement, not the 4 added columns (leaves them as NULL).

I believe its in my counter in my save button (Button2) on the SQLBulkCopy. But cant put my finger on on it.

Imports System.Data.SqlClient
Imports System.Windows.Forms
Imports System.Windows.Forms.DataGridView
Imports System.Windows.Forms.CheckBox
Imports System.Windows.Forms.DateTimePicker

Public Class Form5

    Public Sub Form5_Load(sender As System.Object, e As System.EventArgs) Handles MyBase.Load
        Dim CheckBox1 As New CheckBox
        Dim CheckBox2 As New CheckBox
        Dim CheckBox3 As New CheckBox
        Dim DateTimePicker1 As New DateTimePicker
    End Sub

    Private Sub Button1_Click(sender As System.Object, e As System.EventArgs) Handles Button1.Click
        Dim sql As String
        Dim datepicked As Date
        datepicked = DateTimePicker1.Text
        'Checkbox Check Loop
        If CheckBox1.CheckState = False And CheckBox2.CheckState = False And CheckBox3.CheckState = False Then
            MessageBox.Show("Please select a Shift")
            Exit Sub
        End If
        If CheckBox1.Checked = True And CheckBox2.Checked = True Then
            MessageBox.Show("Please select a One Shift Only")
            Exit Sub
        End If
        If CheckBox2.Checked = True And CheckBox3.Checked = True Then
            MessageBox.Show("Please select a One Shift Only")
            Exit Sub
        End If
        If CheckBox1.Checked = True And CheckBox3.Checked = True Then
            MessageBox.Show("Please select a One Shift Only")
            Exit Sub
        End If

        'Selection of Shift 
        If CheckBox1.Checked = True Then
            sql = "SELECT MACHINENUMBER, STYLEWIDTH, SPACES FROM Loomset WHERE RunFlag1 = 1"
        ElseIf CheckBox2.Checked = True Then
            sql = "SELECT MACHINENUMBER, STYLEWIDTH, SPACES FROM Loomset WHERE RunFlag2 = 1"
        ElseIf CheckBox3.Checked = True Then
            sql = "SELECT MACHINENUMBER, STYLEWIDTH, SPACES FROM Loomset WHERE RunFlag3 = 1"
        End If

        'SQL Data connection
        Dim sqlCon1 As New SqlConnection("Data Source=SERVER1\DEV01;database=Production;uid=sa;pwd=passwordhere")
        Dim daSWC1 As New SqlDataAdapter(sql, sqlCon1)
        Dim SQLcmdBuilder1 As New SqlCommandBuilder(daSWC1)
        Dim ds1 As New DataSet

        'Adds the columns Picks, Runtime and Date.
        Dim AddCol1 As New DataGridViewTextBoxColumn
        Dim AddCol2 As New DataGridViewTextBoxColumn
        Dim AddCol3 As New DataGridViewTextBoxColumn
        Dim AddCol4 As New DataGridViewTextBoxColumn

        AddCol1.DataPropertyName = "Shift"
        AddCol1.HeaderText = "Shift"
        AddCol1.Name = "Shift"
        AddCol2.DataPropertyName = "Picks"
        AddCol2.HeaderText = "PICKS"
        AddCol2.Name = "PICKS"
        AddCol3.DataPropertyName = "RunTime"
        AddCol3.HeaderText = "RunTime"
        AddCol3.Name = "RunTime"
        AddCol4.DataPropertyName = "Date"
        AddCol4.HeaderText = "Date"
        AddCol4.Name = "Date"
        AddCol4.DefaultCellStyle.Format = "MM/dd/yyyy"

        daSWC1.Fill(ds1, "MACHINENUMBER")
        DataGridView1.DataSource = ds1.Tables(0)
        DataGridView1.Columns.Add(AddCol1)
        For Each dgvr As DataGridViewRow In DataGridView1.Rows
            If CheckBox1.Checked Then
                dgvr.Cells("Shift").Value = 1
            ElseIf CheckBox2.Checked Then
                dgvr.Cells("Shift").Value = 2
            ElseIf CheckBox3.Checked Then
                dgvr.Cells("Shift").Value = 3
            End If
        Next
        DataGridView1.Columns.Add(AddCol2)
        DataGridView1.Columns.Add(AddCol3)
        DataGridView1.Columns.Add(AddCol4)
        For Each dgvr As DataGridViewRow In DataGridView1.Rows
            dgvr.Cells("Date").Value = datepicked
        Next

    End Sub

    Private Sub Button2_Click(sender As System.Object, e As System.EventArgs) Handles Button2.Click
        Dim dt As New DataTable
        dt = DataGridView1.DataSource

        Dim objSQLCon As New SqlConnection("Data Source=SERVER1\DEV01;database=Production;uid=sa;pwd=passwordhere")
        Dim TableName As String = "ProdEnter_SAVE"
        Dim strErrorMessage As String = ""

        Try
            If objSQLCon.State = ConnectionState.Open Then
                objSQLCon.Close()
            End If

            objSQLCon.Open()

            Try
                'To Perform Bulk Copy for copy data from DataTable to SQL Table
                If dt.Rows.Count > 0 Then
                    Using Sqlbcp As SqlBulkCopy = New SqlBulkCopy(objSQLCon)
                        Sqlbcp.DestinationTableName = "[dbo].[" & TableName & "]"
                        Sqlbcp.WriteToServer(dt)
                    End Using
                End If

            Catch sqlEx As SqlException
                strErrorMessage = "SqlException: " & sqlEx.Message & Environment.NewLine & "Error Code: " & sqlEx.ErrorCode
            Catch ex As Exception
                strErrorMessage = ex.Message
            End Try

        Catch ex As Exception
            strErrorMessage = "Open SQL connection failed" & ex.Message
        End Try
    End Sub

    Private Sub Button3_Click(sender As System.Object, e As System.EventArgs) Handles Button3.Click
        Me.Close()
    End Sub

End Class