OleDbException was unhandled, syntax error in WHERE clause?

Question

Having trouble with a persistent error. My form allows the user to see a list of elements in a particular group. The input for group number is a combobox called groupbox and the output is a combobox called ElementResults. I am getting the error on the line: GroupSearch.ExecuteNonQuery()

Imports System.Data.OleDb
Public Class ElementsSearch
Public Shared connectionString As String = "Provider=Microsoft.ACE.OLEDB.12.0;Data        Source=C:\Documents and Settings\A\My Documents\Visual Studio 2010\Projects\A2\Element.accdb;Persist Security Info=False;"
Public Shared ElementsTable

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

    Dim Search As New OleDbConnection(connectionString)
    Search.Open()

    Dim SearchCriteria As String
    SearchCriteria = Groupbox.Text
    Dim query As String = "SELECT * From ElementsTable WHERE Group='" & SearchCriteria & "'"
    Dim GroupSearch As New OleDbCommand(query, Search)
    GroupSearch.ExecuteNonQuery()
    Dim reader As OleDbDataReader = GroupSearch.ExecuteReader()
    ElementResults.Text = Convert.ToString(reader("Name"))

End Sub
End Class

Can you start with debugger and paste what is coming in SearchCriteria? — Pranit Kothari
– Pranit Kothari, Commented Mar 2, 2014 at 15:51
Why do you execute the command twice? Remove GroupSearch.ExecuteNonQuery(). — Bjørn-Roger Kringsjå
– Bjørn-Roger Kringsjå, Commented Mar 2, 2014 at 16:05
Im quite new to vb, and this was modified from a teachers example. I have removed the line you highlighted, but I still get an error: InvalidOperationException No data exists for the row/column. This error occurs on the line "elementresults.text = Convert.ToString(Reader("Name") — user3371044
– user3371044, Commented Mar 2, 2014 at 16:07
I'm not surprised, see my answer, but accept Patrick Hofman's answer as this solved you're initial question. — Bjørn-Roger Kringsjå
– Bjørn-Roger Kringsjå, Commented Mar 2, 2014 at 16:14

Patrick Hofman · Accepted Answer · 2014-03-02 15:59:39Z

2

You define the field group which is a reserved keyword.

Try [group] instead like in this sample:

SELECT * From ElementsTable WHERE [Group]='aaa'

answered Mar 2, 2014 at 15:59

Patrick Hofman

158k23 gold badges270 silver badges343 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

user3371044 Over a year ago

Ahh I should have thought of the reserved keywords. Your suggestion solved my error, but now I have a new one: InvalidOperationException No data exists for the row/column. This error occurs on the line "elementresults.text = Convert.ToString(Reader("Name"))

Bjørn-Roger Kringsjå · Accepted Answer · 2014-03-02 16:12:28Z

1

To avoid all the errors you're getting, consider changing your code to something like this:

Using connection As New OleDbConnection(connectionString)
    connection.Open()
    Using command As New OleDbCommand("SELECT * From ElementsTable WHERE [Group]=@Group", connection)
        command.Parameters.AddWithValue("@Group", SearchCriteria)
        Using reader As OleDbDataReader = command.ExecuteReader()
            Do While reader.Read()
                ElementResults.Text = reader.GetString("Name")
            Loop
        End Using
    End Using
End Using

answered Mar 2, 2014 at 16:12

Bjørn-Roger Kringsjå

10.1k6 gold badges43 silver badges66 bronze badges

Collectives™ on Stack Overflow

OleDbException was unhandled, syntax error in WHERE clause?

2 Answers 2

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related