EOleException with message 'Syntax error in FROM clause' delphi

Question

Hi I'm trying to get SQL to select from a table which the user has chosen from a list box

Heres my code

activity := cmbActivity.Text; //this is where the user selects a table to choose from
qryStudents.SQL.Text := 'SELECT * FROM :activity WHERE CompNo = :iCompNo'; //error here
qryStudents.Parameters.ParamByName('activity').Value:= activity; 
qryStudents.Parameters.ParamByName('iCompNo').Value := iCompNo;
qryStudents.Open;

I keep getting a syntax error after FROM in the SQL code (:activity) Any help will be appreciated

Marc Guillot · Accepted Answer · 2017-07-06 14:39:56Z

3

You can't define the table part of your select as a parameter, you'll need to dynamically build that part of the select statement.

activity := cmbActivity.Text;
qryStudents.Close;
qryStudents.SQL.Text := 'SELECT * FROM '  + activity + ' WHERE CompNo = :iCompNo';
qryStudents.Parameters.ParamByName('iCompNo').Value := iCompNo;
qryStudents.Open;

:iCompNo OTOH is fine to be defined as a parameter, in order to prevent SQL injection.

edited Jul 6, 2017 at 14:39

answered Jul 6, 2017 at 14:28

Marc Guillot

6,5431 gold badge23 silver badges54 bronze badges

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

9 Comments

Jaynill Gopal Over a year ago

It still gives the same issue with that change

Marc Guillot Over a year ago

Are you sure that the activity variable had a valid table name ?. By the way, you had a qryStudents.Edit at the start of your code, I have changed it for qryStudents.Close, because your query should be closed when you change its SQL property.

Jaynill Gopal Over a year ago

Yes it is a valid table name. I did close the qryStudents.Edit before I worked on this snippet. I get a syntax error on exactly how it is in the title which breaks my program

MartynA Over a year ago

@JaynillGopal: Marc is right that you cannot specify the table you are querying using a parameter, so +1 for that. Which Sql back-end and Delphi query component are you using.

Jaynill Gopal Over a year ago

I'm using an ADOQuery component in delphi with a Provider=Microsoft.Jet.OLEDB.4.0 connection

|

Collectives™ on Stack Overflow

EOleException with message 'Syntax error in FROM clause' delphi

1 Answer 1

9 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

9 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related