shinbona shinbona - 1 year ago 75 Question

Why this statement cannot be updated in the database (CLOSED)

This is solved.

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

Answer Source

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.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download