0

This code is not working. An error message pops up saying:

Syntax Error (missing operator) in query exprssion SubName = Gussing Game.

Here is my code please help. I need this code to update the Microsoft Access Database.

 Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click

    Try
        Dim Str As String
        Con.Open()

        Str = "update vb set AssignDate="
        Str += """" & txtADate.Text & """"
        Str += "where SubName = "
        Str += txtAName.Text.Trim()
        Cmd = New OleDbCommand(Str, Con)
        Cmd.ExecuteNonQuery()
        Con.Close()
        Con.Open()

        Str = "update vb set DueDate="
        Str += """" & txtDDate.Text & """"
        Str += "where SubName = "
        Str += txtAName.Text.Trim()
        Cmd = New OleDbCommand(Str, Con)
        Cmd.ExecuteNonQuery()
        Con.Close()
        Con.Open()

        Str = "update vb set Weight="
        Str += """" & txtWeight.Text & """"
        Str += "where SubName = "
        Str += txtAName.Text.Trim()
        Cmd = New OleDbCommand(Str, Con)
        Cmd.ExecuteNonQuery()
        Con.Close()
        Con.Open()

        Str = "update vb set Reference="
        Str += """" & txtReference.Text & """"
        Str += "where SubName = "
        Str += txtAName.Text.Trim()
        Cmd = New OleDbCommand(Str, Con)
        Cmd.ExecuteNonQuery()
        Con.Close()
        Con.Open()

        Str = "update vb set Comment="
        Str += """" & txtComment.Text & """"
        Str += " where SubName ="
        Str += txtAName.Text.Trim()
        Cmd = New OleDbCommand(Str, Con)
        Cmd.ExecuteNonQuery()
        Con.Close()
        Con.Open()

        Str = "update vb set Statues="
        Str += """" & txtStatues.Text & """"
        Str += " where SubName ="
        Str += txtAName.Text.Trim()
        Cmd = New OleDbCommand(Str, Con)
        Cmd.ExecuteNonQuery()
        Con.Close()

        Dst.Clear()
        Dad = New OleDbDataAdapter("SELECT * FROM vb ORDER BY SubName", Con)
        Dad.Fill(Dst, "assignment")
        MessageBox.Show("Record Updated!", "Caution", MessageBoxButtons.OK, MessageBoxIcon.Error)
    Catch ex As Exception
        MsgBox(ex.Message & "," & ex.Source)
    End Try
    Con.Close()
End Sub
Tasos K.
  • 7,979
  • 7
  • 39
  • 63
Dark Horse
  • 13
  • 1
  • If SubName is a Text field then every value for it should go inside single quotes. _where SubName ='value'_. Of course if the value contains itself a quote you need to duplicate the quote. Have you ever heard of [Parameterized queries](http://stackoverflow.com/questions/542510/how-do-i-create-a-parameterized-sql-query-why-should-i)? – Steve Nov 01 '15 at 23:12
  • 2
    Parameterize your queries! You are leaving yourself open to SQL injection. – codechurn Nov 01 '15 at 23:14

1 Answers1

1

You didn't quote the string scalar you compare SubName with:

    Str = "update vb set AssignDate="
    Str += """" & txtADate.Text & """"
    Str += "where SubName = "
    Str += txtAName.Text.Trim()  ' should be """"&txtAName.Text.Trim()&""""

I'll skip over what an extremely bad way of querying this is.

Blindy
  • 65,249
  • 10
  • 91
  • 131