Home Reading and writing .accdb file from DataModule in Delphi
Reply: 2

Reading and writing .accdb file from DataModule in Delphi

Ming Lau
1#
Ming Lau Published in 2018-01-10 13:30:28Z

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?

John Easley
2#
John Easley Reply to 2018-01-10 18:44:04Z

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;
Ming Lau
3#
Ming Lau Reply to 2018-01-10 18:03:02Z

It seems to be that the above code worked however when inserting data into the database via Access, Access would fill the remaining character allowance in the database with spaces. SOLUTION: Used the Trim() function on both Password and Username and worked fine afterwards

You need to login account before you can post.

About| Privacy statement| Terms of Service| Advertising| Contact us| Help| Sitemap|
Processed in 0.303785 second(s) , Gzip On .

© 2016 Powered by mzan.com design MATCHINFO