Error: Syntax error (missing operators) in query expression?

Question

When ever I click on the UPDATE button it showing me the error as "Syntax error (missing operators) in query expression" but the save and refresh functions are working fine. I don't know what is the syntax error in the update button. Here is my code:

Private Sub FillDataGridView(ByVal Query As String)
    da = New OleDbDataAdapter(Query, cn)
    dt.Clear()
    da.Fill(dt)
    With DataGridView1
        .DataSource = dt
        .Columns(0).HeaderText = "ID"
        .Columns(1).HeaderText = "Name"
        .Columns(2).HeaderText = "Age"
        .Columns(1).AutoSizeMode = DataGridViewAutoSizeColumnMode.Fill
    End With
End Sub

Private Sub BtnSearch_Click(sender As Object, e As EventArgs) Handles BtnSearch.Click
    Try
        FillDataGridView("select * from [edit$] where ID='" & TxtId.Text & "'")
        TxtFamilyname.Text = dt.Rows(0).Item(1)
    Catch ex As Exception
        MsgBox(ex.Message, MsgBoxStyle.Critical, Text)
    End Try
End Sub

Private Sub BtnSave_Click(sender As Object, e As EventArgs) Handles BtnSave.Click
    Try
        With cm
            .Connection = cn
            .CommandText = "insert into [edit$]values('" & TxtId.Text & "','" & TxtFamilyname.Text & "', '" & TxtAge.Text & "')"
            .ExecuteNonQuery()
        End With
        FillDataGridView("select * from [edit$]")
    Catch ex As Exception
        MsgBox(ex.Message, MsgBoxStyle.Critical, Text)
        Return
    End Try
    MsgBox("successfully Saved!", MsgBoxStyle.Information, Text)
End Sub

Private Sub BtnUpdate_Click(sender As Object, e As EventArgs) Handles BtnUpdate.Click
    Try
        With cm
            .Connection = cn
            .CommandText = "Update [edit$] set [Family Name] = '" & TxtFamilyname.Text & "' where id ='" & TxtId.Text & "' where Age= '" & TxtAge.Text & "'"
            .ExecuteNonQuery()
        End With
        FillDataGridView("select * from [edit$]")
    Catch ex As Exception
        MsgBox(ex.Message, MsgBoxStyle.Information, Text)
        Return
    End Try
    MsgBox("Successfully updated!", MsgBoxStyle.Information, Text)
End Sub

You have a massive sql injection security vulnerability. – Chris Dunaway Apr 29 '15 at 15:17 — Chris Dunaway, Apr 29 '15 at 15:17

score 1 · Answer 1 · edited Apr 29 '15 at 08:44

1

Use AND with WHERE clause between two or more conditions

 where id ='" & TxtId.Text & "' AND Age= '" & TxtAge.Text & "'"

edited Apr 29 '15 at 08:44

SysDragon

9,692
15
60
89

answered Apr 29 '15 at 08:26

Tushar Gupta

15,504
1
29
47

score 1 · Answer 2 · edited May 23 '17 at 12:30

You probably shouldn't have single quotes around your id and age fields (assuming these are numeric data types) Also - you have two where clauses which is not allowed.

So it should look more like this:

.CommandText = "Update [edit$] set [Family Name] = '" & TxtFamilyname.Text & "' where id =" & TxtId.Text & " AND Age= " & TxtAge.Text

But having said that you shouldn't write sql queries like this anyway. Search for Little Bobby Tables, then search for parameterised queries - you don't need to worry about single quotes when using parameterised queries

score 0 · Answer 3 · answered Apr 29 '15 at 08:26

0

You cant have two where clauses...

Use "AND"/"OR" to extend your query.

SQL AND & OR Operators

answered Apr 29 '15 at 08:26

Louis van Tonder

3,664
3
31
62

Guffa · Answer 4 · 2015-04-29T08:36:05.820

0

You can't have two where clauses in the update. Use and between the conditions:

.CommandText = "Update [edit$] set [Family Name] = '" & TxtFamilyname.Text & "' where id ='" & TxtId.Text & "' and Age= '" & TxtAge.Text & "'"

That said, your code is wide open for SQL injection attacks. You should rather use parameterised queries than to concatenate the values into the queries. Example:

With cm
  .Connection = cn
  .CommandText = "Update [edit$] set [Family Name] = @name where id = @id and Age = @age"
  .Parameters.AddWithValue("@name", TxtFamilyname.Text)
  .Parameters.AddWithValue("@id", TxtId.Text)
  .Parameters.AddWithValue("@age", TxtAge.Text)
  .ExecuteNonQuery()
End With

edited Apr 29 '15 at 08:36

answered Apr 29 '15 at 08:27

Guffa

687,336
108
737
1,005

Can some one explain me that how to delete the data of a row in my excel sheet DB from datagridview through a Delete button "BtnDelete" – shaik izaz Apr 30 '15 at 09:17
@shaikizaz: You should start by looking if there are some already existing answers that would be useful, otherwise you can ask a new question to reach the persons who knows about it. You posted this as a comment to an answer in a question that isn't all related to your new question, so you only reached me, and anyone who just happens to read this answer. – Guffa Apr 30 '15 at 09:23
Thanks a lot for the info.. I'll post it as a question now. – shaik izaz Apr 30 '15 at 09:25

score 0 · Answer 5 · answered Apr 29 '15 at 08:29

0

There are two WHERE statements in your Update CommanText, try the following;

.CommandText = "Update [edit$] set [Family Name] = '" & TxtFamilyname.Text & "' where id ='" & TxtId.Text & "' and Age= '" & TxtAge.Text & "'"

answered Apr 29 '15 at 08:29

Oliver Gray

874
6
17

Error: Syntax error (missing operators) in query expression?

5 Answers5