Member Avatar for firoz.raj

can anybody tell me why i got syntax error(missing operator) in query expression'ID='; here is the code what i have written.any help would be greatly appreciated.

Private Sub Command2_Click()
If CheckInput = True Then
    Dim success As Boolean
    Set con = New ADODB.Connection
    success = OpenConnection(con)
 If success = False Then
      MsgBox ("Cannot Open Connection")
      Exit Sub
End If
Set rs = New ADODB.Recordset
rs.Open "select * from supplierS where ID=" & Text1.Text, con, adOpenDynamic, adLockOptimistic
 If rs.BOF And rs.EOF Then rs.AddNew
 ' rs.Fields("Supplier_id") = Text1.Text
   rs.Fields("Supplier_name") = Text2.Text
   rs.Fields("contact_person") = Text3.Text
   rs.Fields("contact_no") = Text4.Text
   rs.Fields("office_address") = Text6.Text
   rs.Fields("emails") = Text7.Text
   rs.Fields("website") = Text8.Text
   rs.Fields("Fax_no") = Text5.Text
   rs.Fields("item_type") = Combo1.Text
   rs.Update
   MsgBox ("data saved")
   rs.Close
 Else
   Exit Sub
End If
con.Close
Set con = Nothing
End Sub
  • 3 Contributors
  • 2 Replies
  • 229 Views
  • 4 Years Discussion Span
  • Latest Post Latest Post by babu.shahulhameed

Dani AI

Generated

The "missing operator (ID=)" error almost always means the SQL you built ends up with no value after the equals sign (or with the wrong delimiter for the column type). built the WHERE clause from Text1.Text, and and correctly flagged quoting and data-type issues — but a few practical checks will quickly fix it and make the code robust.

First, always validate and inspect the SQL you send to the database: trim the input and check it is not empty. Use Debug.Print or MsgBox to see the final SQL string before executing it. Second, pick the right delimiters: numeric columns get no quotes, text needs single quotes, and Access date/time uses #. Always escape any embedded single quote in text (replace ' with '') to avoid syntax errors. Third, prefer parameterized commands so you don’t have to guess delimiters and you avoid SQL injection.

A short parameterized pattern (classic ADO) to update by ID:

' parameterized ADODB.Command example
Dim cmd As ADODB.Command
Set cmd = New ADODB.Command
Set cmd.ActiveConnection = con
cmd.CommandText = "UPDATE supplierS SET Supplier_name = ?, contact_person = ? WHERE ID = ?"
cmd.CommandType = adCmdText
cmd.Parameters.Append cmd.CreateParameter("pName", adVarChar, adParamInput, 100, Trim(Text2.Text))
cmd.Parameters.Append cmd.CreateParameter("pContact", adVarChar, adParamInput, 50, Trim(Text3.Text))
cmd.Parameters.Append cmd.CreateParameter("pID", adInteger, adParamInput, , CInt(Trim(Text1.Text)))
cmd.Execute

If you rely on Recordset.AddNew when no row exists, be sure to assign the primary-key field before calling Update (leaving the key unset causes duplicates or errors). Quick checklist: print the SQL, check Text1 is not blank, verify ID’s data type in the table, escape quotes for text, or switch to parameters for safety.

Recommended Answers

All 2 Replies

Member Avatar for arvin2006

try to add " & Text1.Text & " instead of " & Text1.Text

Member Avatar for babu.shahulhameed

"ID" Varible is Numberic then try this:

rs.Open "select * from supplierS where ID=#" & Text1.Text & "#", con, adOpenDynamic, adLockOptimistic

"ID" Variable is Text then Try this:

A) Unique Record
rs.Open "select * from supplierS where ID LIKE '" & Text1.Text & "'", con, adOpenDynamic, adLockOptimistic

B) More than one Record
rs.Open "select * from supplierS where ID LIKE '" & Text1.Text & "*'", con, adOpenDynamic, adLockOptimistic

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.