Error Parsing Update

Question

The following  error is reported on attempting to update a record  "Error parsing Update Query  [Token line number = 1, Token line ofset = 38, Token in error = / ]".  There is no "/" in the query.  Here is the code:

This is the button Save code:

 Private Sub btnSave_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSave.Click

        Select Case state

            Case "n"

                If txtFName.Text = "" Then

                    MsgBox("Name Cannot be null", , "My Telephone Book")

                Else

                    Try

                        Using conn = New SqlCeConnection(connString)

                            Using cmd = New SqlCeCommand

                                cmd.Connection = conn

                                cmd.CommandText = "INSERT INTO Persons (" & _

                                    "Fullname, " &

                                    "DoB, " &

                                    "DoM, " &

                                    "MPhone, " &

                                    "EMail, " &

                                    "Notes, " &

                                    "AddressID) " &

                                    "VALUES " &

                                    "(?,?,?,?,?,?,?)"

                                With cmd.Parameters.Add("FullName", Me.txtFName.Text)

                                    cmd.Parameters.Add("DoB", Me.dtpDoB.Value)

                                    cmd.Parameters.Add("Dom", Me.dtpDoM.Value)

                                    cmd.Parameters.Add("MPhone", Me.txtMPhone.Text)

                                    cmd.Parameters.Add("EMail", Me.txtEMail.Text)

                                    cmd.Parameters.Add("Notes", Me.txtNotes.Text)

                                    cmd.Parameters.Add("AddressID", Me.txtAddressID.Text)

                                End With

                                conn.Open()

                                cmd.ExecuteNonQuery()

                            End Using

                        End Using

                        MsgBox("Record Saved", , "My Telephone Book")

                    Catch sqlex As SqlCeException

                        Dim sqlError As SqlCeError

                        For Each sqlError In sqlex.Errors

                            MessageBox.Show(sqlError.Message)

                        Next

                    Catch ex As Exception

                        MsgBox("Error Saving Record", , "My Telephone Book")

                    Finally

                        conn.Close()

                    End Try

                End If

  

           Case "u"

                If txtFName.Text = "" Then

                    MsgBox("Full Name cannot be empty", "My Telephone Book")

                Else

                    Try

                        conn.Open()

                        Dim cmd As SqlCeCommand = conn.CreateCommand

                        cmd.CommandText = "UPDATE Persons SET FullName" & txtFName.Text &

                         "DoB" & dtpDoB.Value &

                         "DoM" & dtpDoM.Value &

                         "MPhone" & txtMPhone.Text &

                         "EMail" & txtEMail.Text &

                         "Notes" & txtNotes.Text &

                         "FROM Persons WHERE PersonID = " & lstPersonID.Text

                        cmd.ExecuteNonQuery()

                        MsgBox("Record Updated", , "My Telephone Book")

                        conn.Close()

                        Call FillList()

                    Catch sqlex As SqlCeException

                        Dim sqlError As SqlCeError

                        For Each sqlError In sqlex.Errors

                            MessageBox.Show(sqlError.Message)

                        Next

                    Catch ex As Exception

                        'MsgBox("Error Updating Record", , "My
Telephone Book")

                        MessageBox.Show(ex.Message)

                    Finally

                        conn.Close()

                    End Try

                End If

        End Select

        sql = "SELECT * FROM Persons ORDER BY FullName"

        Call FillList()

        txtFind.Clear()

        txtFName.Focus()

    End Sub

Any help will be greatly appreciated as I have tried everything I know in the last 24hrs without success.

Ken 

---

Ken Beale

Answer 1

My guess is that SQL is receving something like this:

UPDATE Persons SET FullNameJohnDoB01/02/1980DoM...

and it just chokes on the slash.

There are quite a few issues with your query: you must use the equal sign between the field name and the value, sequential assignments must be separated by commas, values must be properly quoted when necessary and there is an extraneous FROM clause before your WHERE.

I strongly advise you to use parameters instead of hardcoded values as these open your application to injection attacks and are frankly just boring to get right.

HTH
--mc

Answer 2

Thank you kindly for your response, could you give me a small sample of using parameters instead of hardcoded values.

---

Ken Beale

Answer 3

Hi Kenob,

Welcome to the MSDN forum.

Here are two samples about using code to update or select information.

Sample1:

Using con As New SqlConnection( _
    "Data Source=.;Database=testdb;Integrated Security=true;Trusted_Connection=True")
                con.Open()
                Dim constr As String = "select * from testtb where ID = @ID"
                Using cmd As New SqlCommand(constr, con)
                    Dim prmsn As New SqlParameter("@ID", SqlDbType.Int)
                    prmsn.Value = CType(TextBox1.Text, Integer)
                    cmd.Parameters.Add(prmsn)
                    Dim reader As SqlDataReader = cmd.ExecuteReader()
                    Dim da As New DataTable
                    da.Load(reader)
                    DataGridView1.DataSource = da
                End Using
            End Using

Sample2:

Using con As New SqlConnection( _
  "Data Source=.;Database=testdb;Integrated Security=true;Trusted_Connection=True")
                con.Open()
                Dim constr As String = "update testtb set name='" & TextBox2.Text & "',address='" & TextBox3.Text & "' where id='" & TextBox1.Text & "'"
                Using cmd As New SqlCommand(constr, con)
                    cmd.ExecuteNonQuery()
                End Using
            End Using

Hope it helps.

---

Mark Liu-lxf [MSFT]
MSDN Community Support | Feedback to us

Answer 4

Hi Kenob,

We haven’t heard from you for several days. I’d like to mark the helpful replies as answer firstly. If you have any additional questions, you also can unmark the replay and post your question here. 

Sorry for any inconvenience and have a nice day.

---

Mark Liu-lxf [MSFT]
MSDN Community Support | Feedback to us