Lucynda Lucynda - 10 months ago 37 Question

Foreach Loop Only Updating One Database Row

I'm trying to develop a feature in to auto-flag an item in mysql database as expired, less than 7 days, less than a month, less than 2 months, less than 3 months.

I know the loops I'm using are right based on the text in the labels on the top right. However, it's only updating one of the rows in the database. Here's an image of what I'm getting. So the question is: How do I code so that all rows in the database are updated?

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"
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
updateQ6 = "update chemicalinventoryrecord.chemicalinventory set remark =' ' where DateDiff(expiryDate, CURDATE())>90"
command3 = New MySqlCommand(updateQ6, connection)
Exit For
End If
End While
Catch ex As Exception
End Try

End Sub`

Answer Source

Used a simple stored procedure to get data from database

Vb side:

    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=;" _
                & "uid=Monica;" _
                & "pwd=12345;" _
                & "database=Monica;"

        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)

        End If

            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 side:

       CREATE PROCEDURE `Monica`.`selectDataWithRemark` ()

       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,
                 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 $$

----output ----- enter image description here

PC @Tomalak used your code but add else condition to handle null exception