vb.net update table with datagridview

Question

I stack at my code I have a datagridview then if datagridview values has all ready in my table then update my table with datagridview value else insert into my table. But I stack at when update then the updated value just once and insert into my table when the values is not in my table failed.

Here is my table and value

Here my code

For Each row As DataGridViewRow In dgvLosshours.Rows
If (row.Cells("losshrs").Value = "") Then
    MsgBox("Losshours can't Empty", MsgBoxStyle.Critical)
    Return
Else
    Dim i As Integer = 0
    Dim hd As Integer = row.Cells("headcount").Value * row.Cells("workhrs").Value - row.Cells("losshrs").Value
    Dim cmd1 As New MySqlCommand
    Dim mydt1 As New DataSet
    Dim myadapt1 As New MySqlDataAdapter
    openConnectionsMySQL()
    cmd1 = New MySqlCommand("select * from whweek where periode = date_format(NOW(), '%m-%Y') ", connectMySQL)
    myadapt1 = New MySqlDataAdapter(cmd1)
    myadapt1.Fill(mydt1, "list")
    connectMySQL.Dispose()
    If (mydt1.Tables(0).Rows.Count = 19) Then
        Dim c As String = "Update whweek losshours : " & row.Cells("losshrs").Value & ", RegularHours :" & hd
            With dgvLosshours
                dt = ctrl.ActionQuery("update whweek set workhrs = '" & row.Cells("workhrs").Value & "', losshours = '" & _
                row.Cells("losshrs").Value & "', reghrs = '" & hd & "', Ke = '" & _
                row.Cells("edate").Value & "', modifyby = '" & frmMenu.toolempno.Text & _
                "', modifyon = '" & today & "' where CodeDept = '" & _
                row.Cells("dept").Value & "' and class = '" & row.Cells("class").Value & "' ")
                dt = ctrl.ActionQuery("INSERT INTO log (EmpNo, Tanggal, Ket) VALUES ('" & frmMenu.toolempno.Text & "', '" & today & "', '" & c & "')")
            End With

            MsgBox("LossHours had been updated!")
            dgvLosshours.Rows.Clear()

    ElseIf (mydt1.Tables(0).Rows.Count = 0) Then

        Dim hd1 As Integer = row.Cells("headcount").Value * row.Cells("workhrs").Value - row.Cells("losshrs").Value
        Dim c As String = "Add LossHrs By :" & frmMenu.toolempno.Text & " Period : " & period
        dt = ctrl.ActionQuery("insert into whweek(codedept, class, workhrs, losshours, reghrs, Dari, Ke, createby, createon) values('" & _
        row.Cells("dept").Value & _
        "','" & row.Cells("class").Value & _
        "','" & row.Cells("workhrs").Value & _
        "','" & row.Cells("losshrs").Value & _
        "','" & hd1 & "','" & _
        row.Cells("sdate").Value & "', '" & _
        row.Cells("edate").Value & "', '" & _
        frmMenu.toolempno.Text & "','" & today & "')")
        dt = ctrl.ActionQuery("INSERT INTO log (EmpNo, Tanggal, Ket) VALUES ('" & _
        frmMenu.toolempno.Text & "', '" & today & "', '" & c & "')")
        MsgBox("Successful Save LossHours")
        dgvLosshours.Rows.Clear()
    End If

End If

and i want to update the volume become 100 for all items and '2017-04-26' date2 for all items and when i start new date1 and date2 all items will duplicate is ok, the volume, qty, volume*qty loss stuff date1 and date2 are not same values

ex insert new data my query not insert to my table

update data my code just update the first row i want is update all row.

my code are not return those two image i want. Please show me the way, thanks

What error are you receiving? This example is not very succinct. — IronAces
Hi @DanielShillcock, I'm sorry. I will edit this post and please show the way thanks — Mas Harjo

Mas Harjo Mas Harjo · Accepted Answer · 2017-04-25T03:38:07

Thanks i've know my answer here the code :

update whweek set wh= '" & dgvLosshours.Rows(intI).Cells("wh").Value & "', lh= '" & _
                                       dgvLosshours.Rows(intI).Cells("lh").Value & "', reg= '" & hd & "', Ke = '" & _
                                        dgvLosshours.Rows(intI).Cells("edate").Value & "', modifyby = '" & frmMenu.no.Text & _
                                         "', modifyon = '" & today & "', periode = '" & dgvLosshours.Rows(intI).Cells("periode").Value & "' where id= '" & _
                                         dgvLosshours.Rows(intI).Cells("id").Value & "' and class = '" & dgvLosshours.Rows(intI).Cells("class").Value & _
                                         "' and Dari = '" & dateFrom.Text & "'

vb.net update table with datagridview

1 Answers