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”
Thank you for visiting the Q&A section on Magenaut. Please note that all the answers may not help you solve the issue immediately. So please treat them as advisements. If you found the post helpful (or not), leave a comment & I’ll get back to you as soon as possible.
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
Command use unmanaged code. These resources need to be release by calling the objects
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.