database usernames and passwords in delphi

Question

Using Delphi XE2.

I have a database with a table in it called users. It has a user_id field, username field, a password field and an Active field in it. (the user_id is a unique number that identifies each user... 1,2,3 etc).

I am writing a database software package which requires username and passwords to login to it. (I already have a login form created).

How do I get it to match/check the usernames and passwords in the database then allow user to continue into the software? Also I would like the Active Field to store in the database 'Y' if user is logged in or 'N' if user is not logged in. Is this doable?

I am connected to the users table via TADQuery and TDataSource.

Example below of function which I thought would get me started(calling it at point of clicking the login button on the login form).

function TfrmLogin.CheckUser: Boolean;
begin
while not dmData.qryUser.Eof do
begin
if(editName.Text <> qryUser.FieldByName('uname').AsString) or (editPassword.Text <> qryUser.FieldByName('pword').AsString)
  then ShowMessage('Username and/or Password not recognised');
    Exit;
End;

Answer 1

Looping through all rows in the database will not suffice, especially when you get more than a handful of users. You need to SELECT from the database for the specific user, and see if you get results back. You can do the SELECT based just on the username:

qryUser.SQL.Text := 'SELECT uname, pword FROM users WHERE uName = :uname';
qryUser.ParamByName('uname').AsString := editName.Text;
try
  qryUser.Open;
  if qryUser.IsEmpty then // No record found for user
    // Handle error
  else
  begin
    if qryUser.FieldByName('pword').AsString <> editPassword.Text then
      // Handle password mismatch;
  end;
finally
  qryUser.Close;
end;

It's not clear from your question which database components you're using (TADQuery might be a typo for TADOQuery, or it might be something else). If in fact it is TADOQuery, you'll need to make a couple of small changes to the code. (Actually, only three minor changes; two in the assignment of the parameter and one that reads the password value.)

qryUser.SQL.Text := 'SELECT uname, pword FROM users WHERE uName = :uname';
qryUser.Params.ParamByName('uname').Value := editName.Text;
try
  qryUser.Open;
  if qryUser.IsEmpty then // No record found for user
    // Handle error
  else
  begin
    if qryUser.FieldByName('pword').Value <> editPassword.Text then
      // Handle password mismatch;
  end;
finally
  qryUser.Close;
end;