how to update mysql database in vb.net

Question

Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click

    Dim remarks As String
    Dim am_OUT As String = "11:30:01 AM"


    If LblTime.Text >= am_OUT Then
        remarks = "OVERTIME"
    Else
        remarks = "PRESENT"
    End If


    sql = "UPDATE attendance SET  am_out=@am_out, noon_remarks=@noon_remarks " & _
          "WHERE id_no= '" & Txtid.Text & "'AND date LIKE  '" & LblDate.Text & "'and am_out is null"

    Try
        With com
            .Connection = con
            .CommandText = "SELECT * FROM attendance WHERE id_no LIKE '" & Txtid.Text & "' AND date LIKE '" & LblDate.Text & "'AND am_out is NOT NULL"

        End With



        com.Parameters.Add(New MySqlParameter("@am_out", LblTime.Text))
        com.Parameters.Add(New MySqlParameter("@noon_remarks", remarks))

        com.ExecuteNonQuery()

        If remarks = "OVERTIME" Then
            MessageBox.Show("You have little overtime")
        ElseIf remarks = "PRESENT" Then
            MessageBox.Show("You Log out on time")
        End If




    Catch ex As Exception
        MsgBox(ex.Message)
    End Try
End Sub

End Class

i cant update my database table in mysql and mysql database already have amin and am remarks

Steve Steve · Accepted Answer · 2014-01-30T23:01:44

I suppose that the object com is you MySqlCommand. If this is the case the line

 .CommandText = "SELECT * FROM attendance WHERE id_no LIKE '" & Txtid.Text & "' AND date LIKE '" & LblDate.Text & "'AND am_out is NOT NULL"

is a SELECT statement and cannot update anything when this command is executed

Said that, I suggest to correctly implement a parameterized query for every part of your query, and, of course, use the correct statement that updates your record

sql = "UPDATE attendance SET  am_out=@am_out, noon_remarks=@noon_remarks " & _
      "WHERE id_no= @id AND date LIKE  @dt and am_out is null"

Try
    With com
        .Connection = con
        .CommandText = sql
        .Parameters.AddWithValue("@am_out",LblTime.Text)
        .Parameters.AddWithValue("@noon_remarks",remarks)
        .Parameters.AddWithValue("@id",Txtid.Text )
        .Parameters.AddWithValue("@dt",Convert.ToDateTime(LblDate.Text ))
    End With
    com.ExecuteNonQuery()

how to update mysql database in vb.net

1 Answers