January 28th, 2004, 04:16 AM
-
Problems with sql queries in delphi 6
Hi, I use queries to manipulate data in a access table and normally all goes well but..
When the querie contains characters like this > ` ' ( ) ' :' and some other ones
I get all kinds of error messages and when i remove those characters all goes well.
But I have to insert this data as it is in a database, does anyone knows how to do this ?
This is a example of a querie is use :
query3.Active := false ;
query3.SQL.Text := 'insert into sneeuwhoogten (code,plaats,sneeuwnieuws) values('+#39 + code + #39 + ',' + #39 + plaats + #39 + ','+#39 + snownews + #39 + ')' ;
Query3.execsql ;
where code,plaats and snownews are strings.
Thanks in advance
January 28th, 2004, 12:07 PM
-
This is why you should always parametrize all queries. Assuming that Query3 is a TADOQuery object:
Code:
query3.Active := false;
query3.SQL.Text := 'insert into sneeuwhoogten (code,plaats,sneeuwnieuws) values (:code, :plaats, :sneewnews)' ;
query3.Parameters.ParamByName('code').Value := code;
query3.Parameters.ParamByName('plaats').Value := plaats;
query3.Parameters.ParamByName('sneewnews').Value := snownews;
query3.ExecSQL;
If you're using a BDE object (i.e.) TQuery instead of TADOQuery, change the part where you set the params to something like this:
Code:
query3.ParamByName('code').AsString := code;
query3.ParamByName('plaats').AsInteger := plaats;
// change AsInteger, AsString, AsDateTime as needed..
The advantage of parametrizing is that
(1) The engine takes care of escaping characters correctly for you, so you don't have to escape any characters yourself.
(2) If you need to execute the statement again with different values of code, plaats and snownews, you don't need to prepare the SQL string again. All you need to do is set the parameter values again and execute:
Code:
with Query3 do
begin
Close;
ParamByName('code').AsString := code;
... Set rest of params here ...
ExecSQL;
end;
Last edited by Scorpions4ever; January 28th, 2004 at 12:09 PM.
Up the Irons
What Would Jimi Do? Smash amps. Burn guitar. Take the groupies home.
"Death Before Dishonour, my Friends!!" - Bruce D ickinson, Iron Maiden Aug 20, 2005 @ OzzFest
Down with Sharon Osbourne
"I wouldn't hire a butcher to fix my car. I also wouldn't hire a marketing firm to build my website." - Nilpo
January 28th, 2004, 04:55 PM
-
Thats it !!!!
It works great, thank you very much I never knew that it could be done this way.
What i did now was that I filtered the string and replace or deleted the character that was causing the problem, but your solution is much much better.
Thanks again !!! :-)
(Excuse my bad englisch)