0

I am using data-grid to display the database and used a combo-box and textbox to search the data field I got the error as "Syntax error in FROM clause" and then

"Runtime Error '-2147217900(80040e14)' Method Refresh' of object 'IAdodc' Failed."

Private Sub Search_Click()
   If Category.Text = "Name" Then
      Showdata.RecordSource = "Select * from EmployeeRegisteration where Name = '" + Display.Text + "'"
      Showdata.Refresh
      Showdata.Caption = Showdata.RecordSource
   End If
End Sub

I've created many other forms but never faced such error.

StayOnTarget
  • 11,743
  • 10
  • 52
  • 81
Yogesh Parthe
  • 21
  • 4
  • Which SQL are you using (SQL server, MySQL, etc)? Also the `Name` field could be causing the error. Try putting brackets around the field so that the query instead is as follows: `"Select * from EmployeeRegisteration where [Name] = '" + Display.Text + "'"` – vbguyny Nov 13 '20 at 14:20
  • Make sure Display.Text doesn't also include a quote ('). In general you should refrain from creating your SQL statement with pure string concatenation. Best practice is to use a [Parameterized Query](https://stackoverflow.com/questions/10352211/vba-ado-connection-and-query-parameters) for that, which would take care of proper string santization. And personally I'd use `&` to concat strings. Makes it more obvious what's going an – Hel O'Ween Nov 13 '20 at 15:24
  • 1
    You should form the SQL string as a separate variable, set a breakpoint just after, and see what's in it. That will show the problem. – StayOnTarget Nov 16 '20 at 12:44
  • Is the misspelling of EmpolyeeRegistration deliberate? – Rob Nov 24 '20 at 16:01

1 Answers1

0

Faced the same issue in VB6 with ADO in SQL Server. Changing the LockType from adLockOptimistic to adLockReadOnly fixed it. Hope it helps someone!

Gustavo Boismoreau
  • 1