#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2004
    Posts
    3
    Rep Power
    0

    Arrow 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
  2. #2
  3. Banned ;)
    Devshed Supreme Being (6500+ posts)

    Join Date
    Nov 2001
    Location
    Woodland Hills, Los Angeles County, California, USA
    Posts
    9,626
    Rep Power
    4247
    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
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2004
    Posts
    3
    Rep Power
    0

    Thumbs up 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)

IMN logo majestic logo threadwatch logo seochat tools logo