CODEWITHSUNDEEP
vb.net
Iza
Iza

Reputation: 85

Syntax Error in Update Statement in my code

good day, i have the following codes:

Private Sub Timer1_Tick(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Timer1.Tick
    Dim cmd As New OleDb.OleDbCommand
    Dim compDate As Date
    Dim x As New Integer
    Dim profID As New Integer
    Dim date1 As New Date
    compDate = Format(Date.Now, "hh:mm:ss, tt")
    'MsgBox(compDate)
    date1 = #8:00:00 AM#
    profID = 201400001
    x = 1
    If Not cnn.State = ConnectionState.Open Then
        cnn.Open()
    End If
    cmd.Connection = cnn
    'Timer1.Start()
    Timer1.Interval = 5000
    sp.Close()
    Try
        sp.Open()
    Catch
        sp.Close()
    End Try
    If TextBox1.Text = "201400001" Then
        If DateDiff(DateInterval.Minute, date1, Date.Now) > 5 Then
            MsgBox("been here")
            cmd.CommandText = "UPDATE test " & _
                              "SET ProfLog" & x & "" & _
                              "WHERE ProfID='" & Me.TextBox1.Text & "' AND ProfTime=#" & date1 & "#"
            cmd.ExecuteNonQuery()
            MsgBox("Did this")
        End If
        MsgBox("Done!")
    ElseIf TextBox1.Text = "201400002" Then
        MsgBox("Hello World Again!")
    ElseIf TextBox1.Text = "201400003" Then
        MsgBox("My Turn!")
    End If
    TextBox1.Clear()
End Sub

once it reach the cmd.ExecuteNonQuery, a syntax error is being displayed. it says that there is a "Syntax error in UPDATE statement" i would like to know what is the syntax that makes my program go wrong. Thanks in advance.

Upvotes: 0

Views: 63

Answers (1)

Ken White
Ken White

Reputation: 125651

You have an error in your UPDATE statement. You're missing an = after SET ProfLog:

"UPDATE test " & _
    "SET ProfLog = " & x & "" & _
    "WHERE ProfID='" & Me.TextBox1.Text & "' AND ProfTime=#" & date1 & "#"

You could have figured this out yourself by showing the cmd.CommandText in a message box or the immediate window in Visual Studio.

Please do yourself a huge favor and search for "parameterized queries" or "SQL injection". You should learn to do things properly from the start, instead of learning to do them poorly and causing yourself many, many problems later.

Upvotes: 2

Related Questions