I want to have a checker on my vb.net that will also update the specific column on the mysql table-CodePudding

This is my code that will get the value of the columns of the table

 Try
            con.Open()
            cmd = New MySqlCommand("SELECT agent_id, agent_qualifying, agent_accumulated from tbagent", con)
            dr = cmd.ExecuteReader
            While dr.Read
                aid = dr.GetInt32("agent_id")
                qual = dr.GetInt32("agent_qualifying")
                acc = dr.GetInt32("agent_accumulated")
                checker(aid, qual, acc)
            End While
            dr.Close()
            con.Close()
            loadstructure()
        Catch ex As Exception
            con.Close()
            MsgBox(ex.Message, vbCritical)
        End Try

This is my code of my checker

        Dim qstat = "Ready"
        Dim qnstat = "Not Ready"
        Try
            dr.Close()
            If acc >= qual Then
                cmd = New MySqlCommand("update tbagent set agent_qstatus= '" & qstat & "'where agent_id like '" & aid & "'", con)
                dr = cmd.ExecuteReader
            Else
                cmd = New MySqlCommand("update tbagent set agent_qstatus= '" & qnstat & "'where agent_id like '" & aid & "'", con)
                dr = cmd.ExecuteReader
            End If
        Catch ex As Exception
            con.Close()
            MsgBox(ex.Message, vbCritical)
        End Try

basically When the form loads I want to check every agent on the table in MySQL if they have equal or greater than the qualifying sale. So if they accumulated greater than the qualifying their status column in the agent table will be changed into "READY"

CodePudding user response：

You can do this is a single sql statement. I am assuming that when the record is created, the status is 'Not Ready' as the default value for that field.

Several database objects including Connection and Command use unmanaged code. These resources need to be release by calling the objects Dispose method. Using...End Using blocks handle this for us even if there is an error. These objects need to be declared in the method where they are used so these blocks can be utilized.

Private Sub UpdateStatus()
    Using cn As New MySqlConnection(ConnStr),
            cmd As New MySqlCommand("Update tbagent 
                                    Set agent_qstatus = 'Ready' 
                                    Where agent_accumulated > agent_qualifying ;", cn)
        cn.Open()
        cmd.ExecuteNonQuery()
    End Using
End Sub

You can then retrieve the data if you want to see it.