How do I read HasRow then Update?

Question

Is it possible to read HasRow and then Update ? This is the code what I have tried so far :

 If conn.State = ConnectionState.Closed Then
        conn.Open()
    End If

Dim sqlcmd As New MySqlCommand("SELECT * FROM tbl_online_attendance where employees_id = '" & lvRealAtt.Items(itms).SubItems(0).Text & "' and in_time = '" & lvRealAtt.Items(itms).SubItems(1).Text & "' ", conn)

            Dim dr As MySqlDataReader
            dr = sqlcmd.ExecuteReader
            If dr.HasRows Then
                Dim query As String

                query = "UPDATE tbl_online_attendance SET out_time = '" & lvRealAtt.Items(itms).SubItems(2).Text & "' where employees_id = '" & lvRealAtt.Items(itms).SubItems(0).Text & "' and in_time = '" & lvRealAtt.Items(itms).SubItems(1).Text & "'  "
                sqlcmd.Connection = conn
                sqlcmd.CommandText = query
                sqlcmd.ExecuteNonQuery() 'It error in this part
            Else

            End If

But it give's me error saying:

There is already an open DataReader associated with this Connection which must be closed first

Please avoid commenting Use Parameters Your code is Prone to SQL injection attack

Answer 1

You should not have to check connection state if you keep your connections local to the method that they are used. Database objects like connections and commands need to be closed and disposed as soon as possible. Using...End Using blocks take care of this for you even if there is an error. Don't open a connection until directly before the .Execute....

Don't pull down data when you only need Count. .ExecuteScalar returns the first column of the first row of the result set, which in this case, is the Count. If you have a large table you need to look into If Exists which will stop as soon it finds a match whereas Count looks at the whole table.

Always use Parameters. Never concatenate strings to build sql queries to avoid sql injection. I had to guess at the datatypes of the parameters. Check your database to get the actual types and adjust the code accordingly.

Private Sub OPCode(ByVal itms As Integer)
    Dim RowCount As Integer
    Using conn As New MySqlConnection("Your connection string"),
        sqlcmd As New MySqlCommand("SELECT Count(*) FROM tbl_online_attendance where employees_id = @id and in_time = @inTime;", conn)
        sqlcmd.Parameters.Add("@id", MySqlDbType.Int32).Value = CInt(lvRealAtt.Items(itms).SubItems(0).Text)
        sqlcmd.Parameters.Add("@inTime", MySqlDbType.String).Value = lvRealAtt.Items(itms).SubItems(1).Text
        conn.Open()
        RowCount = CInt(sqlcmd.ExecuteScalar)
        If RowCount > 0 Then
            sqlcmd.CommandText = "UPDATE tbl_online_attendance SET out_time = @outTime where employees_id = @id and in_time = @inTime;"
            sqlcmd.Parameters.Add("@outTime", MySqlDbType.String).Value = lvRealAtt.Items(itms).SubItems(2).Text
            sqlcmd.ExecuteNonQuery()
        End If
    End Using
End Sub