Lucynda Lucynda - 1 month ago 6
Vb.net Question

Foreach Loop Only Updating One Database Row

I'm trying to develop a feature in vb.net 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"
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

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

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

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

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

Comments