database doesn't update automatically

Question

Dim day As String = Format(Today, "dddd") 'string format of days
Dim hoursinterval As Integer  'date borrowed 
Dim retrievtime As Date  'date borrowed 
Dim convertedtimeH As Integer    'hour borrowed 
Dim convertedtimeM As Integer   'minute borrowed 

Dim time As Date 'current date
Dim CurrHour As Integer ' current hour
Dim CurrMinute As Integer 'current minute


Public Sub timeOfOvernight(ByVal id As Integer, ByVal day As Integer)
    If day >= 1 Then
        sql = "UPDATE `tblborrow` SET `Due` = 1,`Remarks`='Over Due' WHERE Status='Borrowed' AND `Purpose` ='Overnight' and `BorrowID` in ('" & id & "')"
        updates(sql)
        Return
    End If
End Sub

here is my updates code:

Function updates(ByVal sql As String)
        Try
            con.Open()
            cmd = New MySqlCommand
            With cmd
                .Connection = con
                .CommandText = sql
                result = cmd.ExecuteNonQuery
                ' If result = 0 Then
                'MsgBox("No updated data", MsgBoxStyle.Information)
                'Else
                'MsgBox("Data in the database has been updated")
                'End If
            End With
            con.Close()
        Catch ex As Exception
            MsgBox(ex.Message & "update")
        End Try
        Return result
End Function

the problem in here is that when my book reaches it's due date it doesn't update the query in my database.. i adjust the date in my laptop but still it doesnt update. i don't get any error in this . that's why im having a hard time how will database update .

can some rebuild my code? im a newbie in vb.net and im still studying thank you so much

Try reposting the code so it's readable. What is the code in "updates(sql)"? I assume that's where the SQL command is executed, so that's likely where the issue is, so include the code for that too. — Jon Roberts, Mar 04 '20 at 10:04
OK so...what is calling the timeOfOvernight Sub? Subs and functions don't just run on their own. In a windows app it must be attached to some kind of event like button click or form load or timer tick. — Jeremy, Mar 04 '20 at 13:37
You have created the variable `day` as a string but if it's the same one that gets used later `If day >= 1` then it will not work as you intended. You can get Visual Studio to tell you about problems like that by using [`Option Strict On`](https://stackoverflow.com/a/29985039/1115360) - you will need to enable it for the current project too, as shown at the end of that answer. — Andrew Morton, Mar 04 '20 at 13:51

score 0 · Accepted Answer · answered Mar 04 '20 at 13:55

Assuming that your Database Id is an Integer (as that's how you pass it to timeOfOvernight), then change your SQL from:

sql = "UPDATE `tblborrow` SET `Due` = 1,`Remarks`='Over Due' WHERE Status='Borrowed' AND `Purpose` ='Overnight' and `BorrowID` in ('" & id & "')"

to

sql = string.format("UPDATE `tblborrow` SET `Due` = 1,`Remarks`='Over Due' WHERE Status='Borrowed' AND `Purpose` ='Overnight' and `BorrowID` in ({0})",id)

or better

sql = string.format("UPDATE `tblborrow` SET `Due` = 1,`Remarks`='Over Due' WHERE Status='Borrowed' AND `Purpose` ='Overnight' and `BorrowID` = {0}",id)

You are enclosing Id in quotes in your SQL command which will convert it to a string. Remove the quotes and it should treat it as an Integer and then - hopefully - find something to update. At least I think so, but worth a try!

This does of course assume that somewhere in your code you are calling timeOfOvernight with correctly. We can't see that from the code posted.

database doesn't update automatically

1 Answers1