shinbona shinbona - 14 days ago 5
Vb.net Question

Why this statement cannot be updated in the database

I am facing this issue with my query. I would like you to kindly help me resolve it.

MD = "UPDATE librarysystem.audit set timeout = '" & Today + "" + TimeOfDay & "' AND status='0' WHERE username = '" & AccountId & "'AND status = '1'"
cmd = New MySqlCommand(MD, con)
cmd.ExecuteNonQuery()


here's the code:

connect()

Dim result As Integer = MessageBox.Show("Are You Sure You Want To LOGOUT?", "Are You?", MessageBoxButtons.YesNo)
If result = DialogResult.No Then
Me.Show()
ElseIf result = DialogResult.Yes Then

connect()
Dim time As DateTime
time = Date.Today
Dim a As Integer = 0
MD = "UPDATE librarysystem.audit set timeout = '" & Today + "" + TimeOfDay & "' AND status='0' WHERE username = '" & AccountId & "'AND status = '1'"
cmd = New MySqlCommand(MD, con)
cmd.ExecuteNonQuery()

AccountSettings.Hide()
BorrowedBooks.Hide()
LogHistory.Hide()
Login.Hide()

ReturnedBooks.Hide()
SearchBooks.Hide()

End If
End Sub


:

Thank you in advance ^^

Answer

One of the deep hassles of SQL is that you're usually embedding one language in another. That makes it hard to read things in both languages clearly. Here's your SQL extracted from your vb.

UPDATE librarysystem.audit
   set timeout = '" & Today + "" + TimeOfDay & "'
   AND status='0'
 WHERE username = '" & AccountId & "'AND status = '1'"

Here it is with some sample values substituted

UPDATE librarysystem.audit
   set timeout = '2016-11-26 13:14:15'
   AND status='0'
 WHERE username = 'SomeUserName'AND status = '1'

You should be able to carry out that statement directly on your dbms and have it function correctly. But, look it over. What do you see?

I see an AND where there should be a , in the list of columns to update. AND only works in WHERE and ON clauses, not in lists of columns.

I see a missing space in the SQL in the sequence 'SomeUserName'AND.

I also see some potential confusion in the way your vb program created the value for timeout. It's not immediately clear whether your vb TimeOfDay variable will render in 24h format (13:14:15) or in am/pm format (01:14:15 PM).

Finally, I see an extraordinarily common mistake. The SQL statement is jammed onto one line, as if it had been written in the 1980s by a psychotic APL programmer. This makes your language-in-a-language program almost impossible to read.