How to pass string parameters to an TADOQuery?

Question

Using Delphi 2010

Can anyone tell me what I am doing wrong here with my code. The comments show the errors that I receive with the particular methods that I tried to pass parameters to my ADOQuery

procedure CreateAdminLogin(const APasswd: string);
var
  qry: TADOQuery;
  //P1, P2: TParameter;
begin
  qry := TADOQuery.Create(nil);
  try
    qry.Connection := frmDataModule.conMain;
    qry.SQL.Text := 'INSERT INTO Users (User_Id, Password) VALUES (:u, :p)';

    //Syntax error in INTO statement
    qry.Parameters.ParamByName('u').Value:= 'Admin';
    qry.Parameters.ParamByName('p').Value:= GetMd5(APasswd);


    //invalid variant operation
    {qry.Parameters.ParamByName('u').Value.AsString:= 'Admin';
    qry.Parameters.ParamByName('p').Value.AsString:= GetMd5(APasswd);}

    //invalid variant operation
    {P1:= qry.Parameters.ParamByName('u');
    P1.Value.asString:= 'Admin';
    P2:= qry.Parameters.ParamByName('p');
    P2.Value.asString:= GetMd5(APasswd);}


    qry.Prepared := True;
    qry.ExecSQL;
  finally
    qry.Free;
  end;

end;

NOTE: GetMD5 is declared as follows

function GetMd5(const Value: String): string;
var
  hash: MessageDigest_5.IMD5;
  fingerprint: string;
begin
  hash := MessageDigest_5.GetMd5();
  hash.Update(Value);
  fingerprint := hash.AsString();
  Result := fingerprint;
end;

Thankx

No, I get error "Syntax error in INTO statement" I tried 1.) qry.SQL.Text := 'INSERT INTO Users (User_Id, Password) VALUES (Admin, Admin))'; and I tried 2.) using quotedStr around each (e.g. QuotedStr('Admin') ) — Blow ThemUp, Apr 13 '13 at 16:39
Well, you have to work out that first. Since your syntax looks good, it surely depends on the database engine you're connecting to. Once you're able to execute your statement without parameters successfully, you can start adding parameters. — jachguate, Apr 13 '13 at 16:43
Every other SQL works fine in my application, so I don't think there are database issues. I say I don't think, because this is the first insert I am attempting to do. Everything else are select queries. There is a ID field in the Users table. But it is AutoIncremented. Do I have to do anything in my insert with that? — Blow ThemUp, Apr 13 '13 at 16:52
and yes, tried passing both values as 'Admin' with and without paramteres and still got the same error — Blow ThemUp, Apr 13 '13 at 17:08
This may have something to do with a reserved word used as a name. `Users`, perhaps? Or `password`? In standard SQL, such names would need to be enclosed in double quotes, but some SQL products may have different ways of escaping such names. — Andriy M, Apr 13 '13 at 17:17
I wonder if your "user_id" field isn't actually an integer. And you are trying to insert string into it. — Riho, Apr 13 '13 at 17:47
@Riho - No, there are three fields (ID - integer (autoinc), User_id - string, Password - string) — Blow ThemUp, Apr 15 '13 at 14:52
@Jake, that's weird. The insert syntax is (almost) standard in MSAccess. AFAICS, this is not an ADO error, but something in your database or engine. You should try creating a new database to do a small and isolated test. You can also try to perform a select via TADOQuery, insert a new record over it and post the changes to the database, to see if it raises the same error or not. If the insert succeeds that way, you can [monitor](http://stackoverflow.com/a/2073527/255257) the SQL conversation to learn what syntax works for your database. — jachguate, Apr 15 '13 at 15:02

Ken White · Accepted Answer · 2021-02-24T01:12:58.287

This works fine for me, using the DBDemos.MDB file that shipped with Delphi (C:\Users\Public\Documents\RAD Studio\9.0\Samples\Data\dbdemos.mdb by the default installation)

ADOQuery1.SQL.Clear;
ADOQuery1.SQL.Add('INSERT INTO Country (Name, Capital, Continent, Area, Population)');
ADOQuery1.SQL.Add('VALUES (:Name, :Capital, :Continent, :Area, :Population)');

ADOQuery1.Parameters.ParamByName('Name').Value := 'SomePlace';
ADOQuery1.Parameters.ParamByName('Capital').Value := 'Pitsville';
ADOQuery1.Parameters.ParamByName('Continent').Value := 'Floating';
ADOQuery1.Parameters.ParamByName('Area').Value := 1234;
ADOQuery1.Parameters.ParamByName('Population').Value := 56;
ADOQuery1.ExecSQL;
ADOQuery1.Close;

// Open it to read the data back
ADOQuery1.SQL.Text := 'SELECT * FROM Country WHERE Name = :Name';
ADOQuery1.Parameters.ParamByName('Name').Value := 'SomePlace';
ADOQuery1.Open;
ShowMessage(ADOQuery1.FieldByName('Name').AsString);

As a clarification for those who might be misguided, the `ExecSQL` method of `TADOQuery` doesn't leave the component in the `Active = True` state, and so the first `Close` call in this answer (the one just after `ExecSQL`) is unnecessary, albeit harmless. — Andriy M, May 14 '13 at 14:11

score 0 · Answer 2 · edited Sep 01 '20 at 12:54

0

For using like extra thing to know:

Datasource SQL like this

select * from Table where Phone like :param

DataModule.findQuery.Parameters.ParamByName('param').Value:= '%%'+yourEdit.Text + '%%';

edited Sep 01 '20 at 12:54

thatguy

21,059
6
30
40

answered Aug 30 '20 at 19:49

Serega

7
3

score -1 · Answer 3 · answered Apr 13 '13 at 17:38

-1

You should create parameters first:

procedure CreateAdminLogin(const APasswd: string);
var
  qry: TADOQuery;
begin
  qry := TADOQuery.Create(nil);
  try

   // this part is missed in your code
   with qry.Parameters.AddParameter do
    begin
      Name := 'u';
      DataType := ftString;
    end;
    with qry.Parameters.AddParameter do
    begin
      Name := 'p';
      DataType := ftString;
    end;

    qry.Connection := frmDataModule.conMain;
    qry.SQL.Text := 'INSERT INTO Users (User_Id, Password) VALUES (:u, :p)';

    // Now it will be ok!
    qry.Parameters.ParamByName('u').Value:= 'Admin';
    qry.Parameters.ParamByName('p').Value:= GetMd5(APasswd);

    qry.Prepared := True;
    qry.ExecSQL;
  finally
    qry.Free;
  end;
end;

answered Apr 13 '13 at 17:38

Andrei Galatyn

3,322
2
24
38

2

You don't have to do that; see my answer. (You don't have to prepare first either, unless you plan on using the query multiple times.) – Ken White Apr 13 '13 at 17:50
2Ken White: Your example doesn't work for me. I guess you copied example from some project where params created from GUI. I just added required section to original code to make it work. – Andrei Galatyn Apr 15 '13 at 06:24
My example works fine, with or without a GUI, and with the **exact** code I posted in my answer. There is nothing defined in the IDE except for dropping the ADOQuery1 on the form and setting the ConnectionString. No SQL was added, no parameters, no special settings, noting but what's in the code I posted. If it didn't work for you, you did something wrong. :-) I tested it in D2007 and Delphi XE before posting it here. – Ken White Apr 15 '13 at 10:56
@user2106715 - NO, your example did not work. I get the same error. – Blow ThemUp Apr 15 '13 at 14:50
If you provide just the query text, the parameters will be automatically created for you based on your query. So all you have to do is use parameters inside your query properly(example 'select * from A where field1 = :p1') and then you can reference parameters by name(when using :) or by index(when using ?). So make sure that you are not doing Params.Clear(or Parameters.Clear) and make sure that ParamCheck is true. – MelOS May 11 '21 at 13:20

How to pass string parameters to an TADOQuery?

3 Answers3

Linked

Related