我如何读取 HasRow 然后更新? [英] How do I read HasRow then Update?

问题描述

是否可以先读取 HasRow 然后再更新?这是我迄今为止尝试过的代码:

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

但它给了我错误说:

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

请避免注释使用参数你的代码容易受到SQL注入攻击

推荐答案

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

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....

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

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.

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

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

这篇关于我如何读取 HasRow 然后更新?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！