Delphi + SQLite + DB express select with parameters error

I use Delphi XE3 and use SQLite database with DB express.

this is my user validation code:

function validateUser(UserName, Password: string): Boolean;
var
  AParams: TParams;
  SQLTxt: string;
  MD5 : TIdHashMessageDigest5;
  RecCount: integer; 
begin
  AParams := TParams.Create(nil);
  MD5 := TIdHashMessageDigest5.Create;
  try
    Result := False;
    AParams.CreateParam(ftString, 'username', ptInput).Value := UserName;
    AParams.CreateParam(ftString, 'password', ptInput).Value :=
      MD5.HashBytesAsHex(MD5.HashString(Password));
    SQLTxt := 'SELECT login_id FROM login WHERE '+
    'login_username = :username AND login_password = :password ;';
    with Form1.sqlqry1 do
    begin
      SQL.Clear;
      SQL.Text :=  SQLTxt;
      Params := AParams;
      //Params.Items[0].Value := AParams.Items[0].Value;
      //Params.Items[1].Value := AParams.Items[1].Value;
      Prepared := true;
      Open;
    end;
    RecCount := Form1.sqlqry1.RecordCount;// I have error here
    if RecCount = 0 then
      Result := False
    else
      Result := True;
    // end
  finally
    AParams.Free;
    MD5.Free;
  end;
end;

Application show me [0x0005]: Operation Not Supported error.

What is the problem? why?

Answers


You also don't need to use RecordCount (which is where you indicate the error is occurring). Use TDataSet.IsEmpty instead.

Also, from the documentation (emphasis mine):

Params is a collection of TParam objects that represent the parameters of a query or stored procedure specified by the SQL dataset. When you specify a query by setting the CommandText property (or the SQL property in TSQLQuery), the SQL dataset automatically parse the query and fills Params with a TParam object for every parameter in the query.

You don't need to manually create the Params; the dataset will do that for you automatically when you assign the SQL.

function validateUser(UserName, Password: string): Boolean;
var
  SQLTxt: string;
  MD5 : TIdHashMessageDigest5;
  RecCount: integer; 
begin
  MD5 := TIdHashMessageDigest5.Create;
  try
    Result := False;
    SQLTxt := 'SELECT login_id FROM login WHERE '+
    'login_username = :username AND login_password = :password ;';
    with Form1 do
    begin
      // Clear not needed when setting SQL.Text directly.
      sqlqry1.SQL.Text :=  SQLTxt; 
      sqlqry1.Params.ParamByName('username').AsString := UserName;
      sqlqry1.Params.ParamByName('password').AsString :=
               MD5.HashBytesAsHex(MD5.HashString(Password));

      sqlqry1.Open;
      Result := not sqlqry1.IsEmpty;
    end;
  finally
    MD5.Free;
  end;
end;

Need Your Help

Arduino sketch that reads serial chars as a command and does something

ios5 arduino xcode4.5

Currently I am trying to get a sketch working where the Arduino will read a series of chars as a command, and do something based on the series of chars sent from an iDevice. I am using an iPhone 3GS

Need help to create a second level sub-folder

vb.net visual-studio-2010 vs2010-express

I have the following code which creates a sub-folder in a desktop directory. This works fine, no problem.