Member Avatar for LEAN.HEART.YOU 
  Sub all()
        connection()
        Dim see = "Select * from info where Last Name = '" & txtLast.Text & "' and First Name = '" & txtFirst.Text & "'"
        dcom = New OleDbCommand(see, dcon)
        dcom.ExecuteScalar()

        If dcom.ExecuteScalar > 0 Then
            Dim gather = "Select * from info where Last Name = '" & txtLast.Text & "' and First Name = '" & txtFirst.Text & "'"
            dcom = New OleDbCommand(gather, dcon)
            dr = dcom.ExecuteReader
            dr.Read()

            If dr.HasRows Then

                Dim last = dr(1)
                Dim first = dr(2)

                MsgBox("The name" & last + first & "already exists!")


                dcon.Close()
            End If
        Else
            borrower()


        End If
    End Sub
  • Whenever I click the button for saving. it always says "Syntax error (missing operator) in query expression" What will I do? :|
  • 3 Contributors
  • 8 Replies
  • 263 Views
  • 4 Days Discussion Span
  • Latest Post Latest Post by LEAN.HEART.YOU

Dani AI

Generated

Short diagnosis and next steps you can apply immediately.

The Access error "Syntax error (missing operator)" usually means the Jet/ACE SQL parser hit something it couldn't interpret in your WHERE clause. As already pointed out, identifiers that contain spaces must be wrapped in square brackets; and, as suggested, avoiding spaces in field names (LastName or Last_Name) prevents this whole class of problems. (support.microsoft.com)

A safer, practical fix is to stop building SQL with string concatenation and to use a parameterized check for existence (SELECT COUNT()) and then read the row if needed. With OleDb + Access you must use positional ? placeholders and add parameters in the same order you put the ? marks. Also, ExecuteScalar returns the first column of the first row — so use it with COUNT() and convert the result to an integer. Example pattern (VB.NET):

Using cn As New OleDb.OleDbConnection(connString)
  cn.Open()
  Dim sqlCount = "SELECT COUNT(*) FROM info WHERE [Last Name] = ? AND [First Name] = ?"
  Using cmd As New OleDb.OleDbCommand(sqlCount, cn)
    cmd.Parameters.AddWithValue("?", txtLast.Text.Trim())
    cmd.Parameters.AddWithValue("?", txtFirst.Text.Trim())
    Dim exists = Convert.ToInt32(cmd.ExecuteScalar())
    If exists > 0 Then
      ' read full row with a second parameterized command and DataReader
    Else
      ' insert / borrower()
    End If
  End Using
End Using

This avoids syntax errors from embedded quotes and makes your logic (existence check vs. select) explicit. (learn.microsoft.com)

If you still see the error after applying brackets/parameters, debug with these checks: print or Debug.WriteLine the final SQL/parameters and paste that into Access's Query Designer to get the exact Access error; verify the actual field names in the table (no trailing spaces or different spelling); watch for single quotes in names (use parameters instead of manual escaping); and always wrap connections/commands in Using so things close reliably. Also avoid calling ExecuteScalar twice on the same command — store its result. (learn.microsoft.com)

Quick checklist: bracket any field with spaces, switch to parameterized queries (use ? for OleDb), use SELECT COUNT(*) with ExecuteScalar for existence checks, and prefer renaming fields to remove spaces for long-term stability. This will eliminate the parser error and make the code far more robust.

Recommended Answers

All 8 Replies

Member Avatar for pritaeas

Spaces in column names are the problem. If you are on SQL Server put them in brackets.

Member Avatar for LEAN.HEART.YOU

What do you mean by that? I'm only connecting it to MS Access. Can you explain further more. Sorry I'm just a newbie here. :)

Member Avatar for pritaeas 
Select * from info where [Last Name] ...
Member Avatar for LEAN.HEART.YOU

Even in "and First Name"? Should I put bracket?

Member Avatar for pritaeas

Yes.

Member Avatar for LEAN.HEART.YOU

Okay. Thanks man! :D

Member Avatar for Reverend Jim

To avoid confusion it is better to remove the blanks or replace them with underscores as in LastName or Last_Name

Member Avatar for LEAN.HEART.YOU

Still the same man. No changes. The same error. Syntax error (missing operator) </3

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.