时间戳

首页 > 解决方案 > 我如何阅读 HasRow 然后更新？

vb.net - 我如何阅读 HasRow 然后更新？

问题描述

是否可以阅读 HasRow 然后 Update ？这是我到目前为止尝试过的代码：

 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

但它给了我一个错误说：

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

请避免评论Use Parameters Your code is Prone to SQL injection attack

标签： vb.netidatareader

解决方案

如果您将连接保持在使用它们的方法的本地，则不必检查连接状态。连接和命令等数据库对象需要尽快关闭和处置。Using...End Using即使出现错误，blocks 也会为您解决这个问题。在.Execute....

只需要 Count 时不要拉取数据。.ExecuteScalar返回结果集第一行的第一列，在本例中为 Count。如果您有一张大桌子，您需要查看 If Exists ，它将在找到匹配项后立即停止，而 Count 则查看整个表格。

始终使用参数。切勿连接字符串来构建 sql 查询以避免 sql 注入。我不得不猜测参数的数据类型。检查您的数据库以获取实际类型并相应地调整代码。

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

推荐阅读