Reading and writing .accdb file from DataModule in Delphi

Question

I am making a school project which consists of creating a database and reading and writing into it. Within a DataModule I made the database in run time using a TAdoCommand which worked great and now I need to read and write into it. I placed some test data into the database using access but it is unable to read the database.

DataModule Here is a picture of the datamodule in design. I have created a connection, query, datasource and table which are all linked together. The TAdoCommand was used to make the database. The SQL command in the query is "SELECT Username,Password FROM Users"

I then have a Login form in which I hope to use it to read the Users table with the database to check if the user exists in the database.

procedure TLoginFrm.LoginBtnClick(Sender: TObject);
  var Username, Password : String;
      i, NoOfRecords : Integer;
      IsMatch : Boolean;
  begin
    NoOfRecords := modFile.adoDataSet.RecordCount;
    if NoOfRecords = 0 then
    begin
      NewUserFrm.Show;
      Application.Messagebox('There are currently no users. Please create new user.','Error');
      UsernameBox.Text := '';
      PasswordBox.Text := '';
    end
    else
    begin
      IsMatch := False;
      modFile.adoDataSet.First;
      Username := modFile.adoDataSet.FieldByName('Username').AsString;
      Password := modFile.adoDataSet.FieldByName('Password').AsString;
      for i := 1 to NoOfRecords do
      begin
        if (Username = UsernameBox.Text) and (Password = PasswordBox.Text) then
        begin
          LoginFrm.Hide;
          CurrentUser := Username;
          MainMenuFrm.Show;
          IsMatch := True;
        end
        else
        begin
          modFile.adoDataSet.Next;
          Username := modFile.adoDataSet.FieldByName('Username').AsString;
          Password := modFile.adoDataSet.FieldByName('Password').AsString;
        end;
      end;//End of for loop
      if not IsMatch then
      begin
        Application.MessageBox('Incorrect username or password. Try again.','Error');
        UsernameBox.Text := '';
        PasswordBox.Text := '';
        LoginBtn.SetFocus;
      end;
    end;//End of parent Else
  end;

When I put in test data using Access, it returns the message box "Incorrect username or password. Try again". So it recognises that there are more than 0 reccords in the table however it cannot read the actual data. Where did I go wrong?

Answer 1

You're For loop isn't working for you here. Instead you need to iterate your dataset this way:

 else
 begin
   isMatch := false; 
   modFile.AdoDataset.first;
   while not modFile.AdoDataset.eof do
   begin
     Username := modFile.AdoDataset.fieldbyname('Username').asstring;
     Password := modFile.AdoDataset.fieldbyname('Password').asstring;
     if (uppercase(Username) = uppercase(UsernameBox.text)) and (uppercase(Password) = uppercase(PasswordBox.text)) then
     begin
       IsMatch := True;
       LoginFrm.Hide;
       CurrentUser := Username;
       MainForm.Show;
       Exit; // no need to continue on once you have a match
     end;
     modFile.AdoDataset.next;   
   end;
end
else ...

You could also skip using a loop altogether and just use a locate

 else
 begin
   isMatch := modFile.AdoDataset.Locate('Username;Password', VarArrayOf[UsernameBox.text, PasswordBox.text], [loCaseInsensitive]);// remove loCaseInsensitive if you prefer case sensitivity
   if isMatch then
   begin
     CurrentUser := UsernameBox.text;
     Loginfrm.Hide;
     MainForm.Show;
   end;
 end;