Login Program in Delphi XE7 which gives an error: Parameter object is improperly defined. Inconsistent or incomplete information was provided

Question

I have the following problem with a simple and basic program that I am writing in Delphi. It is a Login program where the user enters a username and password. The program will then get the password from an access database where the username equals to the username that the user entered. Then the program will compare the password that it got from the access database with the password that the user entered.

Here is a copy of my code:

(I have the following variables: Password, Username, sPassword)

Username := edtUsername.Text;
Password := edtPassword.Text;

UserQuery.SQL.Add('Select Password as Password1 from Users where Username = :Username');
UserQuery.Parameters.ParamByName('Username').Value := Username;
UserQuery.Open;

sPassword := UserQuery['Password1'];
if sPassword = Password then
begin
  showmessage('Correct');
end
else
begin
  showmessage('Incorrect');
end;

It saves the query value to a variable. If the username is correct and the password that the user entered is correct the program works fine. My problem is that the second time or if anything like the username or password was typed in wrong by the user it gives me an error : Parameter object is improperly defined. Inconsistent or incomplete information was provided. I think it has to check if the query exists but I do not know how to do it. How can I solve this problem?

(I am still a learner)

Agustin Ortu Agustin Ortu · Accepted Answer · 2016-07-11T14:23:18

The problem is, that every time your method is executed, you're adding a SQL statement into your query. If you inspect while debugging for its value, or just ShowMessage(UserQuery.SQL.Text) you'll clearly see it

It will look like this:

Select Password as Password1 from Users where Username = :Username
Select Password as Password1 from Users where Username = :Username
Select Password as Password1 from Users where Username = :Username
...

The query then fails because from the second to the last :Username parameter, no value is provided

There are a few ways to fix it. One of the may involve Clearing the Query then assign the SQL statement again:

UserQuery.Clear; // clear the query before adding the SQL statement 
UserQuery.SQL.Add('Select Password as Password1 from Users where Username = :Username');
UserQuery.Parameters.ParamByName('Username').Value := Username;
UserQuery.Open;

Or you can just assign directly to the Text property which will replace the whole string with the new supplied value:

UserQuery.SQL.Text := 'Select Password as Password1 from Users where Username = :Username';
UserQuery.Parameters.ParamByName('Username').Value := Username;
UserQuery.Open;

Since you are using the same query over and over, the most ideal setup would be initialize it (on a constructor for example) and leave it on a Prepared state. A prepared SQL statement is preparsed and sent to the DB engine, leaving it ready to execute. It should be used when you have a query or command that you need to execute over and over and the only thing you change are the parameters values (just your case)

procedure TForm1.Create(Sender: TObject);
begin
    UserQuery.SQL.Text := 'Select Password as Password1 from Users where Username = :Username';
    // it's a good practice to set the parameter type
    UserQuery.Parameters.ParamByName('Username').DataType := ftString;
    // prepares the query: preparses sql, sends it to the DB engine, etc
    UserQuery.Prepared := True;
end;

// usage
procedure TForm1.YourLoginMethod;
begin
    UserQuery.Parameters.ParamByName('Username').Value := Username;
    UserQuery.Open;
    try
      sPassword := UserQuery.FieldByName('Password1').AsString;
      // perform login logic
    finally
      UserQuery.Close;
    end;  
end;

Just a couple things more to note. I recommend that, similar to set a parameters DataType, you use type-safe TField properties, aka .AsString, .AsInteger, etc

The default property of TDataSet will return a Variant for the given field value, and the perform an implicit conversion. I suggest being explicit since you know your data types better than the RTL

Also a try-finally block is needed here. When using prepared querys, every time you execute them, you need to have a closed query, set the parameters and then call open. The try-finally will grant that every time you open the query, it will be closed regardless of execptions that may ocurr

Login Program in Delphi XE7 which gives an error: Parameter object is improperly defined. Inconsistent or incomplete information was provided

1 Answers