บันทึกข้อมูลจาก Datagrid ลง DB กดบันทึกเเล้วขึ้น Debug ตรง .ExecutNoncury() คับ ช่วยที่คับ

            strConnString = "Server=localhost;UID=sa;PASSWORD=;database=mydatabase;Max Pool Size=400;Connect Timeout=600;"

            strSQL = "INSERT INTO files (Name,FilesName,FilesType) " & _
            " VALUES " & _
            " (@sName,@sFilesName,@sFilesType)"
            objConn.ConnectionString = strConnString
            objConn.Open()

            Dim objCmd As New SqlCommand(strSQL, objConn)
            objCmd.Parameters.Add("@sName", SqlDbType.VarChar).Value = Me.txtName.Text
            objCmd.Parameters.Add("@sFilesName", SqlDbType.Binary).Value = imbByte
            objCmd.Parameters.Add("@sFilesType", SqlDbType.VarChar).Value = strMIME
            objCmd.ExecuteNonQuery()

.Parameters.AddWithValue("@c", DataGridView1.Rows(i).Cells(0))

.Parameters.AddWithValue("@c", DataGridView1.Rows(i).Cells(0).Value)

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        Using con As New SqlConnection("data source=tiger;initial catalog=transport;uid=sa;pwd=47222420322")
            con.Open()
            Dim bs As BindingSource = DataGridView1.DataSource   ' จะ Error ถ้าไม่ได้ใช้ BindingSource เป็น DataSource ในตอนแรก
            Dim tr As SqlTransaction = con.BeginTransaction
            Try 

            For i As Integer = 0 To DataGridView1.RowCount - 1
                Dim cm As New SqlCommand("insert into tblbbk1 (keydate,tranid,proid,proname,box,trancost,bill,amount,typeid,zone) values (@a,@b,@c,@d,@e,@f,@g,@h,@i,@j)", con, tr)
                With cm
                    .Parameters.AddWithValue("@a", dtp.Value.ToString("MM/dd/yyyy"))
                    .Parameters.AddWithValue("@b", cb1.SelectedValue)
                    .Parameters.AddWithValue("@c", bs.List(i)("proid"))
                    .Parameters.AddWithValue("@d", bs.List(i)("proname"))
                    .Parameters.AddWithValue("@e", bs.List(i)("box"))
                    .Parameters.AddWithValue("@f", bs.List(i)("trancost"))
                    .Parameters.AddWithValue("@g", txt6.Text)
                    .Parameters.AddWithValue("@h", bs.List(i)("amount"))
                    .Parameters.AddWithValue("@i", ty.Text)
                    .Parameters.AddWithValue("@j", tz.Text)
                    .ExecuteNonQuery()
                End With
            Next
            tr.Commit()
            MessageBox.Show("บันทึกการแก้ไขข้อมูลเรียบร้อยแล้ว", "Database", MessageBoxButtons.OK)
            DataGridView1.Rows.Clear()
            Catch ex As Exception
                  tr.Rollback()               ' ถ้าจะใช้ SqlTransaction ก็ควรจะมี tr.Rollback() เพื่อให้ทำงานได้เต็มรูปแบบ
                  MessageBox.Show(ex.Message)
            Finally
                  tr.Dispose
            End Try
        End Using
End Sub

Private Sub butsup_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles butsup.Click
        Using con As New SqlConnection("data source=tiger;initial catalog=transport;uid=sa;pwd=47222420322")
            con.Open()
            Dim bs As BindingSource = DataGridView1.DataSource 'จะ Error ถ้าไม่ได้ใช้ BindingSource เป็น DataSource ในตอนแรก
            Dim tr As SqlTransaction = con.BeginTransaction
            Try
                For i As Integer = 0 To DataGridView1.RowCount - 1
                    Dim cm As New SqlCommand("insert into tblbbk1 (keydate,tranid,proid,proname,box,trancost,bill,amount,typeid,zone) values (@a,@b,@c,@d,@e,@f,@g,@h,@i,@j)", con, tr)
                    With cm
                        .Parameters.AddWithValue("@a", dtp.Value.ToString("MM/dd/yyyy"))
                        .Parameters.AddWithValue("@b", cb1.SelectedValue)
                        .Parameters.AddWithValue("@c", bs.List(i)("proid"))
                        .Parameters.AddWithValue("@d", bs.List(i)("proname"))
                        .Parameters.AddWithValue("@e", bs.List(i)("box"))
                        .Parameters.AddWithValue("@f", bs.List(i)("trancost"))
                        .Parameters.AddWithValue("@g", txt6.Text)
                        .Parameters.AddWithValue("@h", bs.List(i)("amount"))
                        .Parameters.AddWithValue("@i", ty.Text)
                        .Parameters.AddWithValue("@j", tz.Text)
                        .ExecuteNonQuery()
                    End With
                Next
                tr.Commit()
                MessageBox.Show("บันทึกข้อมูลเรียบร้อยแล้ว", "Database", MessageBoxButtons.OK)
                DataGridView1.Rows.Clear()
                'หากโปรแกรม Error ให้เเสดงข้อมความของ Debug โดยใช้ Catch . . . as Exception
            Catch ex As Exception
                tr.Rollback()
                MessageBox.Show(ex.Message)
            Finally
                tr.Dispose()
            End Try
        End Using
End Sub

Using con As New SqlConnection("data source=tiger;initial catalog=transport;uid=sa;pwd=47222420322")
            con.Open()
            Dim bs As BindingSource = DataGridView1.DataSource 'จะ Error ถ้าไม่ได้ใช้ BindingSource เป็น DataSource ในตอนแรก
            Dim tr As SqlTransaction = con.BeginTransaction
            Try
                For i As Integer = 0 To DataGridView1.RowCount - 1
                    Dim cm As New SqlCommand("insert into tblbbk1 (keydate,tranid,proid,proname,box,trancost,bill,amount,typeid,zone) values (@a,@b,@c,@d,@e,@f,@g,@h,@i,@j)", con, tr)
                    With cm
                        .Parameters.AddWithValue("@a", dtp.Value.ToString("MM/dd/yyyy"))
                        .Parameters.AddWithValue("@b", cb1.SelectedValue)
                        .Parameters.AddWithValue("@c", bs.List(i)("proid"))
                        .Parameters.AddWithValue("@d", bs.List(i)("proname"))
                        .Parameters.AddWithValue("@e", bs.List(i)("box"))
                        .Parameters.AddWithValue("@f", bs.List(i)("trancost"))
                        .Parameters.AddWithValue("@g", txt6.Text)
                        .Parameters.AddWithValue("@h", bs.List(i)("amount"))
                        .Parameters.AddWithValue("@i", ty.Text)
                        .Parameters.AddWithValue("@j", tz.Text)
                        .ExecuteNonQuery()
                    End With
                Next
                tr.Commit()
                MessageBox.Show("บันทึกข้อมูลเรียบร้อยแล้ว", "Database", MessageBoxButtons.OK)
                DataGridView1.Rows.Clear()
                'หากโปรแกรม Error ให้เเสดงข้อมความของ Debug โดยใช้ Catch . . . as Exception
            Catch ex As Exception
                tr.Rollback()
                MessageBox.Show(ex.Message)
            Finally
                tr.Dispose()
            End Try
        End Using
End Sub

Private Sub butsup_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles butsup.Click
        Using con As New SqlConnection("data source=tiger;initial catalog=transport;uid=sa;pwd=47222420322")
            con.Open()
            Dim bs As BindingSource = DataGridView1.DataSource 'จะ Error ถ้าไม่ได้ใช้ BindingSource เป็น DataSource ในตอนแรก
            Dim tr As SqlTransaction = con.BeginTransaction
            Try
                For i As Integer = 0 To bs.Count - 1
                    Dim cm As New SqlCommand("insert into tblbbk1 (keydate,tranid,proid,proname,box,trancost,bill,amount,typeid,zone) values (@a,@b,@c,@d,@e,@f,@g,@h,@i,@j)", con, tr)
                    With cm
                        .Parameters.AddWithValue("@a", dtp.Value.ToString("MM/dd/yyyy"))
                        .Parameters.AddWithValue("@b", cb1.SelectedValue)
                        .Parameters.AddWithValue("@c", bs.List(i)("proid"))
                        .Parameters.AddWithValue("@d", bs.List(i)("proname"))
                        .Parameters.AddWithValue("@e", bs.List(i)("box"))
                        .Parameters.AddWithValue("@f", bs.List(i)("trancost"))
                        .Parameters.AddWithValue("@g", txt6.Text)
                        .Parameters.AddWithValue("@h", bs.List(i)("amount"))
                        .Parameters.AddWithValue("@i", ty.Text)
                        .Parameters.AddWithValue("@j", tz.Text)
                        .ExecuteNonQuery()
                    End With
                Next
                tr.Commit()
                MessageBox.Show("บันทึกข้อมูลเรียบร้อยแล้ว", "Database", MessageBoxButtons.OK)
                DataGridView1.Rows.Clear()
                'หากโปรแกรม Error ให้เเสดงข้อมความของ Debug โดยใช้ Catch . . . as Exception
            Catch ex As Exception
                tr.Rollback()
                MessageBox.Show(ex.Message)
            Finally
                tr.Dispose()
            End Try
        End Using
End Sub

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        MessageBox.Show("DataSource: " & DataGridView1.DataSource.GetType.ToString)
        Using con As New SqlConnection("data source=tiger;initial catalog=transport;uid=sa;pwd=47222420322")
            con.Open()
            Dim bs As BindingSource = DataGridView1.DataSource   ' จะ Error ถ้าไม่ได้ใช้ BindingSource เป็น DataSource ในตอนแรก
            Dim tr As SqlTransaction = con.BeginTransaction
            Try 

            For i As Integer = 0 To DataGridView1.RowCount - 1
                Dim cm As New SqlCommand("insert into tblbbk1 (keydate,tranid,proid,proname,box,trancost,bill,amount,typeid,zone) values (@a,@b,@c,@d,@e,@f,@g,@h,@i,@j)", con, tr)
                With cm
                    .Parameters.AddWithValue("@a", dtp.Value.ToString("MM/dd/yyyy"))
                    .Parameters.AddWithValue("@b", cb1.SelectedValue)
                    .Parameters.AddWithValue("@c", bs.List(i)("proid"))
                    .Parameters.AddWithValue("@d", bs.List(i)("proname"))
                    .Parameters.AddWithValue("@e", bs.List(i)("box"))
                    .Parameters.AddWithValue("@f", bs.List(i)("trancost"))
                    .Parameters.AddWithValue("@g", txt6.Text)
                    .Parameters.AddWithValue("@h", bs.List(i)("amount"))
                    .Parameters.AddWithValue("@i", ty.Text)
                    .Parameters.AddWithValue("@j", tz.Text)
                    .ExecuteNonQuery()
                End With
            Next
            tr.Commit()
            MessageBox.Show("บันทึกการแก้ไขข้อมูลเรียบร้อยแล้ว", "Database", MessageBoxButtons.OK)
            DataGridView1.Rows.Clear()
            Catch ex As Exception
                  tr.Rollback()               ' ถ้าจะใช้ SqlTransaction ก็ควรจะมี tr.Rollback() เพื่อให้ทำงานได้เต็มรูปแบบ
                  MessageBox.Show(ex.Message)
            Finally
                  tr.Dispose
            End Try
        End Using
End Sub

Private Sub butre_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles butre.Click
        If MessageBox.Show("คุณต้องการเรียกข้อมูลใช่หรือไม่?", "Database", MessageBoxButtons.YesNo, MessageBoxIcon.Question) = Windows.Forms.DialogResult.Yes Then
            '****การดึงข้อมูลมาโชว์ใน DataGrid****'
            DataGridView1.Show()
            lv.Hide()
            Using con As New SqlConnection("data source=tiger;initial catalog=transport;uid=sa;pwd=47222420322")
                con.Open()
                Dim sql As String = "select * from tblsum where keydate ='" & dtp.Value.ToString("MM/dd/yyyy") & "'and tranid ='" & cb1.SelectedValue & "'"
                Dim cmd As SqlCommand = New SqlCommand(sql, con)
                Dim dr As SqlDataReader = cmd.ExecuteReader
                Dim dt = New DataTable
                If dr.HasRows Then
                    Dim r As Integer = 0
                    While dr.Read
                        With DataGridView1
                            r = .RowCount - 1
                            .Rows.Add()
                            .Rows(r).Cells(0).Value = dr.Item(2)
                            .Rows(r).Cells(1).Value = dr.Item(3)
                            .Rows(r).Cells(2).Value = dr.Item(4)
                        End With
                    End While
                End If
            End Using
        End If
        DataGridView1.Focus()

f MessageBox.Show("คุณต้องการเรียกข้อมูลใช่หรือไม่?", "Database", MessageBoxButtons.YesNo, MessageBoxIcon.Question) = Windows.Forms.DialogResult.Yes Then
            '****การดึงข้อมูลมาโชว์ใน DataGrid****'
            DataGridView1.Show()
            lv.Hide()
            Using con As New SqlConnection("data source=tiger;initial catalog=transport;uid=sa;pwd=47222420322")
                con.Open()
                Dim sql As String = "select * from tblsum where keydate ='" & dtp.Value.ToString("MM/dd/yyyy") & "'and tranid ='" & cb1.SelectedValue & "'"
                Dim cmd As SqlCommand = New SqlCommand(sql, con)
                Dim dr As SqlDataReader = cmd.ExecuteReader
                Dim dt = New DataTable
                If dr.HasRows Then
                    Dim r As Integer = 0
                    While dr.Read
                        With DataGridView1
                            r = .RowCount - 1
                            .Rows.Add()
                            .Rows(r).Cells(0).Value = dr.Item(2)
                            .Rows(r).Cells(1).Value = dr.Item(3)
                            .Rows(r).Cells(2).Value = dr.Item(4)
                        End With
                    End While
                End If
            End Using
        End If
        DataGridView1.Focus()

If MessageBox.Show("คุณต้องการเรียกข้อมูลใช่หรือไม่?", "Database", MessageBoxButtons.YesNo, MessageBoxIcon.Question) = Windows.Forms.DialogResult.Yes Then
            '****การดึงข้อมูลมาโชว์ใน DataGrid****'
            DataGridView1.Show()
            lv.Hide()
            Using con As New SqlConnection("data source=tiger;initial catalog=transport;uid=sa;pwd=47222420322")
                con.Open()
                Dim sql As String = "select * from tblsum where keydate ='" & dtp.Value.ToString("MM/dd/yyyy") & "'and tranid ='" & cb1.SelectedValue & "'"
                Dim cmd As SqlCommand = New SqlCommand(sql, con)
                Dim dr As SqlDataReader = cmd.ExecuteReader
                Dim dt = New DataTable
                If dr.HasRows Then
                    Dim r As Integer = 0
                    While dr.Read
                        With DataGridView1
                            r = .RowCount - 1
                            .Rows.Add()
                            .Rows(r).Cells(0).Value = dr.Item(2)
                            .Rows(r).Cells(1).Value = dr.Item(3)
                            .Rows(r).Cells(2).Value = dr.Item(4)
                        End With
                    End While
                End If
            End Using
        End If
        DataGridView1.Focus()

Private Sub butsup_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles butsup.Click
        Using con As New SqlConnection("data source=tiger;initial catalog=transport;uid=sa;pwd=47222420322")
            con.Open()
            'Dim bs As BindingSource = DataGridView1.DataSource 'จะ Error ถ้าไม่ได้ใช้ BindingSource เป็น DataSource ในตอนแรก
            Dim tr As SqlTransaction = con.BeginTransaction
            'Try
            For i As Integer = 0 To DataGridView1.RowCount - 1

                Dim cm As New SqlCommand("insert into tblbbk1 (keydate,tranid,proid,proname,box,trancost,bill,amount,typeid,zone) values (@a,@b,@c,@d,@e,@f,@g,@h,@i,@j)", con, tr)
                With cm
                    .Parameters.AddWithValue("@a", dtp.Value.ToString("MM/dd/yyyy"))
                    .Parameters.AddWithValue("@b", cb1.SelectedValue)
                    .Parameters.AddWithValue("@c", DataGridView1.Rows(i).Cells(0).Value)
                    .Parameters.AddWithValue("@d", DataGridView1.Rows(i).Cells(1).Value)
                    .Parameters.AddWithValue("@e", DataGridView1.Rows(i).Cells(2).Value)
                    .Parameters.AddWithValue("@f", DataGridView1.Rows(i).Cells(3).Value)
                    .Parameters.AddWithValue("@g", txt6.Text)
                    .Parameters.AddWithValue("@h", DataGridView1.Rows(i).Cells(4).Value)
                    .Parameters.AddWithValue("@i", ty.Text)
                    .Parameters.AddWithValue("@j", tz.Text)
                    .ExecuteNonQuery()
                End With
            Next
            tr.Commit()
            MessageBox.Show("บันทึกข้อมูลเรียบร้อยแล้ว", "Database", MessageBoxButtons.OK)
            DataGridView1.Rows.Clear()
            '' ''หากโปรแกรม Error ให้เเสดงข้อมความของ Debug โดยใช้ Catch . . . as Exception
            'Catch ex As Exception
            '    tr.Rollback()
            '    MessageBox.Show(ex.Message)
            'Finally
            '    tr.Dispose()
            'End Try
        End Using
    End Sub

With cm
.Parameters.AddWithValue("@a", dtp.Value.ToString("MM/dd/yyyy"))
.Parameters.AddWithValue("@b", cb1.SelectedValue)
.Parameters.AddWithValue("@c", DataGridView1.Rows(i).Cells(0).Value)
.Parameters.AddWithValue("@d", DataGridView1.Rows(i).Cells(1).Value)
.Parameters.AddWithValue("@e", DataGridView1.Rows(i).Cells(2).Value)
.Parameters.AddWithValue("@f", DataGridView1.Rows(i).Cells(3).Value)
.Parameters.AddWithValue("@g", txt6.Text)
.Parameters.AddWithValue("@h", DataGridView1.Rows(i).Cells(4).Value)
.Parameters.AddWithValue("@i", ty.Text)
.Parameters.AddWithValue("@j", tz.Text)
.ExecuteNonQuery()
End With

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        Using con As New SqlConnection("data source=tiger;initial catalog=transport;uid=sa;pwd=47222420322")
            con.Open()
            Dim tr As SqlTransaction = con.BeginTransaction
            For i As Integer = 0 To DataGridView1.RowCount - 1
                Dim cm As New SqlCommand("insert into tblbbk1 (keydate,tranid,proid,proname,box,trancost,bill,amount,typeid,zone) values (@a,@b,@c,@d,@e,@f,@g,@h,@i,@j)", con, tr)
                With cm
                    .Parameters.AddWithValue("@a", dtp.Value.ToString("MM/dd/yyyy"))
                    .Parameters.AddWithValue("@b", cb1.SelectedValue)
                    .Parameters.AddWithValue("@c", DataGridView1.Rows(i).Cells(0))
                    .Parameters.AddWithValue("@d", DataGridView1.Rows(i).Cells(1))
                    .Parameters.AddWithValue("@e", DataGridView1.Rows(i).Cells(2))
                    .Parameters.AddWithValue("@f", DataGridView1.Rows(i).Cells(3))
                    .Parameters.AddWithValue("@g", txt6.Text)
                    .Parameters.AddWithValue("@h", DataGridView1.Rows(i).Cells(4))
                    .Parameters.AddWithValue("@i", ty.Text)
                    .Parameters.AddWithValue("@j", tz.Text)
                    .ExecuteNonQuery()
                End With
            Next
            tr.Commit()
            MessageBox.Show("บันทึกการแก้ไขข้อมูลเรียบร้อยแล้ว", "Database", MessageBoxButtons.OK)
            DataGridView1.Rows.Clear()
        End Using
End Sub