How use the insert query using parameters?

Question

When i try with this query i get an error says that Perameter email doesn't exist, i am sure that the variables : email, login_pass, payment_method,operateur are valid and exists.

SQLQuery2.sql.Text := 'INSERT INTO registered (email,login_pass,payment_method,operateur) VALUES (":email",":login_pass",":payment_method",":avecpuce")';
SQLQuery2.ParamByName('email').AsString := email;
SQLQuery2.ParamByName('login_pass').AsString := login_pass;
SQLQuery2.ParamByName('payment_method').AsString := payment_method;
SQLQuery2.ParamByName('avecpuce').AsString := avecpuce;
SQLQuery2.ExecSQL(true);

I tried removing the quotation, but i get

You have an error in your Sql syntax, check the manual that corresponds to your SQL server for the right syntax to use near ':email,:login_pass,:payment_method,:avecpuce)' at line 1

How to use the insert query above using parameters?

What type of database are you connecting to? Is it MySql, SQL Server, etc? — codeulike
– codeulike, Commented Dec 29, 2011 at 13:31

mjn · Accepted Answer · 2011-12-29 16:43:25Z

7

From the TSQLQuery.ExecSQL documentation:

ExecDirect indicates that the query does not need to be prepared before it is executed. This parameter can be set to true if the query does not include any parameters.

So if the code uses

SQLQuery2.ExecSQL(true);

this means that there will be no support for parameters.

But because you use parameters, just use

SQLQuery2.ExecSQL;

and also remove the quotes around parameters.

answered Dec 29, 2011 at 16:43

mjn

36.7k30 gold badges185 silver badges392 bronze badges

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

Comments

kludg · Accepted Answer · 2011-12-29 12:22:47Z

5

Remove quotation marks:

SQLQuery2.sql.Text := 'INSERT INTO registered (email,login_pass,payment_method,operateur)
   VALUES (:email, :login_pass, :payment_method, :avecpuce)';

answered Dec 29, 2011 at 12:22

kludg

27.6k7 gold badges72 silver badges122 bronze badges

4 Comments

Rafik Bari Over a year ago

You have an error in your Sql syntax, check the manual that corresponds to your SQL server for the right syntax to use near ':email,:login_pass,:payment_method,:avecpuce)' at line 1

kludg Over a year ago

SQL syntax is perfect (I am using Firebird). I can only guess what is wrong in your case, may be you should use a different symbol as a parameter prefix ('?' or '@' instead of ':').

Rafik Bari Over a year ago

Yep, that corrected the syntax problem, but i still get an error EDatabase Error 'SQLQuery2: parameter 'email' not found'

Rafik Bari Over a year ago

that is the same error when i use quotations.Thankyou for ure reply !

Rafik Bari · Accepted Answer · 2011-12-30 17:17:19Z

4

Found the answer !

MySQLQuery2.SQL.Clear;
MySQLQuery2.SQL.Add('INSERT INTO COUNTRY (NAME, CAPITAL, POPULATION)');
MySQLQuery2.SQL.Add('VALUES (:Name, :Capital, :Population)');
MySQLQuery2.Params[0].AsString := 'Lichtenstein';
MySQLQuery2.Params[1].AsString := 'Vaduz';
MySQLQuery2.Params[2].AsInteger := 420000;
MySQLQuery2.ExecSQL;

Thankyou All !!

answered Dec 30, 2011 at 17:17

Rafik Bari

5,03718 gold badges78 silver badges125 bronze badges

5 Comments

mjn Over a year ago

your answer is my answer :) glad I could help

Sertac Akyuz Over a year ago

You would have saved yourself a day if you've read mjn's answer, and also would have saved the trouble posting an answer yourself.

Rafik Bari Over a year ago

mjn answer didn't work for me, the problem wasn't only because of the execSQL but also in the query string it self which shouldn't be as i mentioned in my answer but should be entred by using MySQLQuery.SQL.Add . Thankyou for mjn post for help with the execSQL true or false issue.

mjn Over a year ago

@Rafik Bari: SQLQuery2.sql.Text := ... and SQLQuery2.sql.Clear; ... SQL.Add both do the same.

Rafik Bari Over a year ago

Ok! Thankyou anyway your answer also helped me too much!

Andomar · Accepted Answer · 2011-12-29 12:23:17Z

3

You don't usually quote parameters, only literals. So instead of:

VALUES (":email",":login_pass",":payment_method",":avecpuce")

Try:

VALUES (:email,:login_pass,:payment_method,:avecpuce)

answered Dec 29, 2011 at 12:23

Andomar

239k55 gold badges387 silver badges412 bronze badges

2 Comments

Rafik Bari Over a year ago

You have an error in your Sql syntax, check the manual that corresponds to your SQL server for the right syntax to use near ':email,:login_pass,:payment_method,:avecpuce)' at line 1

Andomar Over a year ago

That's strange, check if there is unusual whitespace characters? (Retype the query manually, without copy/paste)

Mikael Eriksson · Accepted Answer · 2011-12-29 13:46:05Z

1

You should not use quotes around the parameter name.

Parameters are automatically generated for you if your TSQLQuery has a connection assigned and ParamCheck is true and you assign TSQLQuery.CommandText.

It will not generate the parameters when you assign the query to TSQLQuery.SQL.Text.

You can have the parameters generated for you by calling TSQLQuery.Params.ParseSQL:

SQLQuery2.Params.ParseSQL(SQLQuery2.SQL.Text, True);

Or you can add them yourself by calling TSQLQuery.Params.AddParameter.

answered Dec 29, 2011 at 13:46

Mikael Eriksson

139k22 gold badges223 silver badges293 bronze badges

Collectives™ on Stack Overflow

How use the insert query using parameters?

5 Answers 5

Comments

4 Comments

5 Comments

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

Comments

4 Comments

5 Comments

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related