strsqlcount = "SELECT COUNT(re_id) FROM GoodsKurupan_Check_Mont WHERE re_id='" & cellid & "' AND date_check='" & lbldt.Text & "'"
connectdb()
cm = New SqlCommand(strsqlcount, cn)
i2 = cm.ExecuteScalar
closedb()
If i2 > 0 Then
'ถ้ามีข้อมูลอยู่แล้ว ก็ให้แก้ไขเข้าไปใหม่
Dim strsqlUpdate As String
strsqlUpdate = "UPDATE GoodsKurupan_Check_Mont SET chk_1=@chk_1,chk_2=@chk_2,chk_3=@chk_3 WHERE re_id='" & cellid & "'"
For ii As Integer = 0 To DataGridView1.RowCount - 1
If Not DataGridView1.Rows(ii).IsNewRow Then
connectdb()
cm = New SqlCommand(strsqlUpdate, cn)
With cm
If DataGridView1.Rows(ii).Cells(9).FormattedValue IsNot Nothing Then 'checked
.Parameters.Add(New SqlParameter("@chk_1", DataGridView1.Rows(ii).Cells(9).Value.ToString() = "1"))
Else
.Parameters.Add(New SqlParameter("@chk_1", DataGridView1.Rows(ii).Cells(9).Value.ToString() = "0"))
End If
If DataGridView1.Rows(ii).Cells(10).FormattedValue IsNot Nothing Then 'checked
.Parameters.Add(New SqlParameter("@chk_2", DataGridView1.Rows(ii).Cells(10).Value.ToString() = "1"))
Else
.Parameters.Add(New SqlParameter("@chk_2", DataGridView1.Rows(ii).Cells(10).Value.ToString() = "0"))
End If
If DataGridView1.Rows(ii).Cells(11).FormattedValue IsNot Nothing Then 'checked
.Parameters.Add(New SqlParameter("@chk_3", DataGridView1.Rows(ii).Cells(11).Value.ToString() = "1"))
Else
.Parameters.Add(New SqlParameter("@chk_3", DataGridView1.Rows(ii).Cells(11).Value.ToString() = "0"))
End If
End With
cm.ExecuteNonQuery()
cm.Parameters.Clear()
closedb()
End If
Next
MessageBox.Show("อัพเดท")
Else 'ถ้าไม่มีข้อมูลให้มาเพิ่มเข้าไป
'เพิ่มข้อมูลลง GoodsKurupan_Check_Mont
Dim strsqlInsert As String
strsqlInsert = "INSERT INTO GoodsKurupan_Check_Mont (date_check,re_id,name_re,chk_1,chk_2,chk_3) VALUES (@date_check,@re_id,@name_re,@chk_1,@chk_2,@chk_3)"
For i As Integer = 0 To DataGridView1.RowCount - 1
If Not DataGridView1.Rows(i).IsNewRow Then
connectdb()
cm = New SqlCommand(strsqlInsert, cn)
With cm
.Parameters.Add(New SqlParameter("@date_check", lbldt.Text))
.Parameters.Add(New SqlParameter("@re_id", DataGridView1.Rows(i).Cells(0).Value))
.Parameters.Add(New SqlParameter("@name_re", DataGridView1.Rows(i).Cells(3).Value))
If DataGridView1.Rows(i).Cells(9).FormattedValue IsNot Nothing Then 'checked
.Parameters.Add(New SqlParameter("@chk_1", DataGridView1.Rows(i).Cells(9).Value.ToString() = "1"))
Else
.Parameters.Add(New SqlParameter("@chk_1", DataGridView1.Rows(i).Cells(9).Value.ToString() = "0"))
End If
If DataGridView1.Rows(i).Cells(10).FormattedValue IsNot Nothing Then 'checked
.Parameters.Add(New SqlParameter("@chk_2", DataGridView1.Rows(i).Cells(10).Value.ToString() = "1"))
Else
.Parameters.Add(New SqlParameter("@chk_2", DataGridView1.Rows(i).Cells(10).Value.ToString() = "0"))
End If
If DataGridView1.Rows(i).Cells(11).FormattedValue IsNot Nothing Then 'checked
.Parameters.Add(New SqlParameter("@chk_3", DataGridView1.Rows(i).Cells(11).Value.ToString() = "1"))
Else
.Parameters.Add(New SqlParameter("@chk_3", DataGridView1.Rows(i).Cells(11).Value.ToString() = "0"))
End If
End With
cm.ExecuteNonQuery()
cm.Parameters.Clear()
closedb()
End If
Next
MessageBox.Show("บันทึก")
End If
Next
connectdb() ' connect ไว้เลยครับจะได้ไม่เสียเวลา
For i As Integer = 0 To DataGridView1.RowCount - 1
strsqlcount = "SELECT COUNT(re_id) FROM GoodsKurupan_Check_Mont WHERE re_id='" & DataGridView1.Rows(i).Cells(0).Value & "' AND date_check='" & lbldt.Text & "'"
cm = New SqlCommand(strsqlcount, cn)
i2 = cm.ExecuteScalar
closedb()
If i2 > 0 Then
Update
else
Insert
Endif
Next