mysql - Foreach 循环仅更新数据库的一行

Question

我正在尝试在 vb.net 中开发一项功能，自动将 mysql 数据库中的项目标记为过期、少于 7 天、少于一个月、少于 2 个月、少于 3 个月.

根据右上角标签中的文本，我知道我使用的循环是正确的。但是，它仅更新数据库中的一行。这是 image我得到的。 所以问题是:如何编码才能更新数据库中的所有行？

Public Sub insertStatus()
    Dim tDate As Date
    Dim eDate As Date
    Dim reader As MySqlDataReader
    Dim query, query2, updateQ1, updateQ2, updateQ3, updateQ4, updateQ5, updateQ6 As String
    Dim adpt As New MySqlDataAdapter
    Dim dSet As New DataSet
    Dim dRow As DataRow

    connection = New MySqlConnection
    connection.ConnectionString = "server=localhost;userid=luce;password=root;database=chemicalinventoryrecord;Allow Zero Datetime = True"
    Try
        connection.Open()
        tDate = Date.Today
        query = "select * from chemicalinventory"
        query2 = "select expiryDate from chemicalinventory"
        command = New MySqlCommand(query, connection)
        command1 = New MySqlCommand(query2, connection)
        adpt.SelectCommand = command
        adpt.Fill(dSet, "chemicalinventory")
        reader = command1.ExecuteReader
        While reader.Read
            For Each dRow In dSet.Tables(0).Rows
                eDate = CType(reader.GetMySqlDateTime("expiryDate"), Date)
                If (eDate - tDate).Days < 7 And (eDate - tDate).Days >= 1 Then
                    lblTest2.Text = "Less than 7 days"
                    updateQ1 = "update chemicalinventoryrecord.chemicalinventory set remark ='" & lblTest2.Text & "'  where DateDiff(expiryDate, CURDATE())<7 and DateDiff(expiryDate, CURDATE())>=1 "
                    command = New MySqlCommand(updateQ1, connection)

                    Exit For
                ElseIf (tDate - eDate).Days >= 0 Then
                    lblTest1.Text = "Expired"
                    updateQ2 = "update chemicalinventoryrecord.chemicalinventory set remark = '" & lblTest1.Text & "' where DateDiff(CURDATE(), expiryDate)>=0"
                    command2 = New MySqlCommand(updateQ2, connection)

                    Exit For
                ElseIf (eDate - tDate).Days < 30 And (eDate - tDate).Days >= 7 Then
                    updateQ3 = "update chemicalinventoryrecord.chemicalinventory set remark = 'Expiring in less than 1 month' where DateDiff(expiryDate, CURDATE())<30 and DateDiff(expiryDate, CURDATE())>=7"
                    command4 = New MySqlCommand(updateQ3, connection)

                    Exit For
                ElseIf (eDate - tDate).Days < 60 And (eDate - tDate).Days >= 30 Then
                    lblTest3.Text = "Less than 2 month"
                    updateQ4 = "update chemicalinventoryrecord.chemicalinventory set remark = '" & lblTest3.Text & "'where DateDiff(expiryDate, CURDATE())<60 and DateDiff(expiryDate, CURDATE())>=30"
                    command5 = New MySqlCommand(updateQ4, connection)

                    Exit For
                ElseIf (eDate - tDate).Days < 90 And (eDate - tDate).Days >= 60 Then
                    updateQ5 = "update chemicalinventoryrecord.chemicalinventory set remark = 'Expiring in less than 3 months' where DateDiff(expiryDate, CURDATE())<90 and DateDiff(expiryDate, CURDATE())>=60"
                    command6 = New MySqlCommand(updateQ5, connection)
                    Exit For
                Else
                    updateQ6 = "update chemicalinventoryrecord.chemicalinventory set remark =' ' where DateDiff(expiryDate, CURDATE())>90"
                    command3 = New MySqlCommand(updateQ6, connection)
                    Exit For
                End If
            Next
        End While
        reader.Close()
        command.ExecuteNonQuery()
        connection.Close()
    Catch ex As Exception
        MessageBox.Show(ex.Message)
    Finally
        connection.Dispose()
    End Try

End Sub`

Answer 1

使用一个简单的存储过程从数据库获取数据

Vb 端:

    Dim conn As New MySql.Data.MySqlClient.MySqlConnection
    Dim cmd As MySqlCommand = conn.CreateCommand()
    Dim myConnectionString As String
    Dim ds As New DataSet

    myConnectionString = "server=127.0.0.1;" _
                & "uid=Monica;" _
                & "pwd=12345;" _
                & "database=Monica;"

    Try
        conn.ConnectionString = myConnectionString
        conn.Open()  '' open connection

        If conn.State = ConnectionState.Open Then  '' check connection state 
            cmd.CommandText = "selectDataWithRemark"
            cmd.CommandType = CommandType.StoredProcedure
            Dim adap As MySqlDataAdapter = New MySqlDataAdapter(cmd)
            adap.Fill(ds)

        End If

        Try
            If Not ds Is Nothing Then  '' check ds before assigning to datagridview 

                If ds.Tables.Count > 0 Then
                    If ds.Tables(0).Rows.Count > 0 Then
                        dgvChemicalTable.DataSource = ds.Tables(0)
                    End If
                End If
            End If
        Catch ex As Exception
            MessageBox.Show("DataGridView Error : " & ex.Message)
        End Try

        conn.Close() '' close connection 


    Catch ex As MySql.Data.MySqlClient.MySqlException
        MessageBox.Show("Database Coonection Error : " & ex.Message)
    End Try

MySql 端:

       CREATE PROCEDURE `Monica`.`selectDataWithRemark` ()
       BEGIN


       SELECT Serial as Serial_Number,Name as Chemical_Name, 
              MolecularFormula  as Formula, DateReceived as Received_Date,
              DateExpired as Expiry_Date,
              DATEDIFF(DateExpired, DateReceived)     as Days_left,
              CASE
                 WHEN DATEDIFF(DateExpired, DateReceived) <= 0 THEN 'Expired'
                 WHEN DATEDIFF(DateExpired, DateReceived) < 7 THEN 'Expiring in less Less than 7 days'
                 WHEN DATEDIFF(DateExpired, DateReceived) < 30 THEN 'Expiring in less than 1 month'
                 WHEN DATEDIFF(DateExpired, DateReceived) < 60 THEN 'Expiring in less than 2 months'
                 WHEN DATEDIFF(DateExpired, DateReceived) < 90 THEN 'Expiring in less than 3 months'
                 ELSE 'Plenty of days left'
              END AS Remark

     FROM  test;

    END $$

----输出-----

PC @Tomalak 使用了您的代码，但添加了 else 条件来处理空异常