How can i import data/information values from databases and display them into labels captions?

Question

What I'm using for my database (all connected together): ADOConnection1, ADOQuery1, DataSource1, MS-Access, Delphi 10.3

I'm trying to get the values(of all types, mostly strings) from my Database (that's on ms access Database) and put them to text labels, as a displayable text from the database for that exact connected user.

So I will provide you as much information as possible...

Here is the full code that I am using:

procedure TLogin_Page.BitBtn2Click(Sender: TObject);

  begin
      with DataModule5 do
        begin
          // using the following code will check if the credentials are correct, if its correct then Login
          ADOQuery1.SQL.Text := 'SELECT UserCode FROM Credentials ' +
                                'WHERE (UserCode = :UserCode) ' +
                                'AND (Password = :Password)';
          ADOQuery1.Parameters.ParamByName('UserCode').Value := username_field.Text;
          ADOQuery1.Parameters.ParamByName('Password').Value := THashMD5.GetHashString(password_field.Text);
          ADOQuery1.Open;
      if not ADOQuery1.IsEmpty then
        begin
          ShowMessage('Welcome ' + username_field.text + ' !');
          EasyPharmacy_Page.Show;
          Login_Page.Hide;
          // clears fields once logged in
          username_field.Text:='';
          password_field.Text:='';
        end else
        messageDlg('Incorrect Credentials', mtCustom, [mbOK], 0);
      end;
      // once the user connected, import all of the informations of the connected user
      // the infos should be imported to labels texts in "MyAccountPage" form
      // code below doesnt show values from database to label texts
      with DataModule5 do
      begin
        with ADOQuery1 do
          begin
            Close;
            SQL.Clear;
            SQL.add ('SELECT * FROM Credentials ' +
                              'WHERE (UserCode = :UserCode) ' +
                              'AND (Password = :Password) ' +
                              'AND (FirstName = :FirstName) ' +
                              'AND (LastName = :LastName) ' +
                              'AND (Age = :Age) ' +
                              'AND (Adminstrator = :Adminstrator) ');
            Parameters.ParamByName('UserCode').Value;
            Parameters.ParamByName('UserCode').DataType:= ftString;
            // password hashed
            Parameters.ParamByName('Password').GetHashCode;
            Parameters.ParamByName('Password').Value;
            Parameters.ParamByName('Password').DataType:= ftString;
            // first name
            Parameters.ParamByName('FirstName').Value;
            Parameters.ParamByName('FirstName').DataType:= ftString;
            // last name
            Parameters.ParamByName('LastName').Value;
            Parameters.ParamByName('LastName').DataType:= ftString;
            // age
            Parameters.ParamByName('Age').Value;
            Parameters.ParamByName('Age').DataType:= ftInteger;
            // administrator permissions
            Parameters.ParamByName('Adminstrator').Value;
            Parameters.ParamByName('Adminstrator').DataType:= ftboolean;
            Prepared := true;
            with MyAccountPage do
              begin
                open;
                UsernameDetail.Caption := FieldByName('UserCode').AsString;
                PasswordDetail.Caption := FieldByName('Password').asString;
                FirstName.Caption      := FieldByName('FirstName').AsString;
                LastName.Caption       := FieldByName('LastName').AsString;
                MyAge.Caption          := FieldByName('Age').AsString;
              end;
          end;
      end;
  end;

I'm not sure if the problem that I'm having is in this code I'm using here, doesn't seem like its working, or doing what I wanted (display values to labels?):

          UsernameDetail.Caption := FieldByName('UserCode').AsString;
          PasswordDetail.Caption := FieldByName('Password').asString;
          FirstName.Caption      := FieldByName('FirstName').AsString;
          LastName.Caption       := FieldByName('LastName').AsString;
          MyAge.Caption          := FieldByName('Age').AsString;

When I run my program, it only clears the labels captions where the user information were supposed to be displayed

In case you want to know what I wrote in the "sign up" page that inserts the data into the database:

 with DataModule5 do
  begin
    ADOquery1.Close;
    ADOquery1.SQL.Clear;
    ADOquery1.SQL.Add('INSERT INTO Credentials ([UserCode], [Password], [FirstName], [LastName], [Age], [Adminstrator]) ');
    ADOquery1.SQL.Add('VALUES (:UserCode, :Password, :FirstName, :LastName, :Age, :Adminstrator) ');
    // Username + Password Data
    ADOquery1.Parameters.ParamByName('UserCode').Value := username_text.Text;
    ADOquery1.Parameters.ParamByName('Password').Value := THashMD5.GetHashString(Confirm_Password_Text.Text);

    // User's First and Last name Data
    ADOquery1.Parameters.ParamByName('FirstName').Value := FName_input.Text;
    ADOquery1.Parameters.ParamByName('LastName').Value := LName_input.Text;
    ADOquery1.Parameters.ParamByName('Age').value := Age_input.Text;
    // Administrator's Permission
    if AdminPrivilege.Checked = true then
      begin
        ADOquery1.Parameters.ParamByName('Adminstrator').Value:=-1;
      end;
    if AdminPrivilege.Checked = False then
      begin
        ADOquery1.Parameters.Parambyname('Adminstrator').Value:=0;
      end;

    ADOquery1.ExecSQL;

    ShowMessage('a new account has been created successfully');

  end;

If there is something wrong with my code please let me know...

Review https://stackoverflow.com/questions/53137010/how-to-implement-text-box-with-gray-text-hint. Labels are intended for static display of descriptive info, textboxes (or comboboxes, checkboxes, radio buttons) for data. What do you want code to do besides clearing data? — June7, Apr 29 '21 at 01:20
@June7 i didnt want the code to clear data, or anything you mentioned. i just want my data to be imported from the database to labels in the user's profile interface the connected user should have his account informations ( from database) displayed in label's texts thats all. — Charles stealth, Apr 29 '21 at 01:32
Thanks, that explains why using labels. Just want to display not edit. — June7, Apr 29 '21 at 04:30

score 1 · Answer 1 · answered Apr 29 '21 at 01:19

1

In BitBtn2Click you set the Prepared property to true but not the Active property of the query.

answered Apr 29 '21 at 01:19

Michael Warreyn

91
3

the ```prepared``` that i set was meant to be for ```DataModule5.ADOQuery1.Prepared``` – Charles stealth Apr 29 '21 at 01:21

score 1 · Answer 2 · answered Apr 29 '21 at 03:23

1

i managed to solve the problem that i had (thanks to @ken white, for the clarification)

i replaced the TLabel components with TDBText, then i set each TDBText label's datasource+Datafield on the loggin process to display the data from database to labels, and this code worked out:

 with DataModule5 do
    begin
      // using the following code will check if the credentials are correct, if its correct then Login
      ADOQuery1.SQL.Text := 'SELECT * FROM Credentials ' +
                            'WHERE (UserCode = :UserCode) ' +
                            'AND (Password = :Password)';
      ADOQuery1.Parameters.ParamByName('UserCode').Value := username_field.Text;
      ADOQuery1.Parameters.ParamByName('Password').Value := THashMD5.GetHashString(password_field.Text);
      ADOQuery1.Open;
  if not ADOQuery1.IsEmpty then
    begin
      ShowMessage('Welcome ' + username_field.text + ' !');
      EasyPharmacy_Page.Show;
      // displayable profile information:
           with MyAccountPage do
           begin
              //Connecting DataSource
            with DataModule5 do
              begin
                usernamedetail.DataSource := DataSource1;  // UserName
                PasswordDetail.Datasource := DataSource1;  // PassWord
                FirstName.Datasource      := DataSource1;  // FirstName
                LastName.Datasource       := DataSource1;  // LastName
                MyAge.Datasource          := DataSource1;  // MyAge
              end;
              // filling DataField
              UsernameDetail.DataField  := 'UserCode';
              PasswordDetail.DataField  := 'Password';
              FirstName.DataField       := 'FirstName';
              LastName.DataField        := 'LastName';
              MyAge.DataField           := 'Age';
           end;

      Login_Page.Hide;
      // clears fields once logged in
      username_field.Text:='';
      password_field.Text:='';
    end else
    messageDlg('Incorrect Credentials', mtCustom, [mbOK], 0);
  end;

answered Apr 29 '21 at 03:23

Charles stealth

61
7

1

Why are you doing all that work in code? Almost all of it can be done at designtime using the Object Inspector. – Ken White Apr 29 '21 at 03:26
i know but i when set them from the object inspector, i encountered another problems when i tried to "Create a new account", i had to keep the `DBText.Datasource` and `DBText.DataField` empty (from the object inspector properties) to avoid any problems thats why setting those on code side was better, worked without any problem – Charles stealth Apr 29 '21 at 03:32
2

No. YOu can set up the query with hard-coded values at designtime, connect all of your controls, and then edit the query to set it up with parameters instead. Then at runtime all you have to do is set the parameters and open the query, and everything else is done automatically by the VCL. You're writing far too much unnecessary code here. If need be, use two different queries - one to create a new account, and one to do the SELECT afterward. You then use the SELECT query to connect the controls to display the data. Still 80% less code to write. – Ken White Apr 29 '21 at 03:35
i did try that, i connected all of them like you said from the inspector properties, i know that was a faster way to do it and without adding those codes, but it caused me an error when i try to make a new account, whenever i type something in the fields i get "PasswordDetail: Field 'Password' Not Found, thats why i wrote those codes instead – Charles stealth Apr 29 '21 at 03:42
1

As I said, if need be you can use two separate queries instead of one. Not understanding how to do it properly isn't a reason to write a lot of code; the solution is to instead learn to do it properly, so you can save all that time in every app. There is absolutely nothing wrong with having a separate ADOQuery (named, for example, `NewAccountQuery`) and a display query (named like `ShowDataQuery`). It actually makes your code much clearer if you separate the two operationis. – Ken White Apr 29 '21 at 03:45
1

ah i can do that, i didnt think about using 2 queries to separate them, i was very stressed/overthinking about this since i was working on my program the whole day. but i take notes from these, i will also try to separate them reduce my code to make it work like you said. i thank you so much for this, really appreciate you! – Charles stealth Apr 29 '21 at 03:51

How can i import data/information values from databases and display them into labels captions?

2 Answers2