been trying to figure out why the following login authentication procedure doesn't work. I have a simple database table holding pupilID and password (in tblPupil) It seems to connect ok upon compilation i.e. that is procedure Connection, but when i run procedure Login...the program seems to crash. In fact, i get no error messages which could illuminate me further! Could this be a database driver problem?
(Using Delphi7, SQLite Database with DevartSQLiteDirect driver)
// Establish the connection.
SQLConnection1.Connected := true;
label4.Caption := 'OK!';
on E: EDatabaseError do
ShowMessage('Exception raised with message' + E.Message);
var QueryPass : string;
Lcount : String;
cont : boolean;
cont := false;
if InputID.Text = '' then
ShowMessage('Invalid Pupil ID')
cont := True;
While cont = True do
// A random query
QueryPass := 'SELECT password FROM TblPupil Where pupilID = +InputID.Text+';';
// Assign the query to the object SQLQuery1.
SQLQuery1.SQL.Text := QueryPass;
on E: Exception do
ShowMessage('Exception raised with message: ' + E.Message);
Lcount := SQLQuery1.FieldValues['password'];
if Lcount = InputPass.text then
cont := false;
Your query is nonsense. As it's written now, it will not even compile (you have unterminated quotes at the right side), much less execute (because `WHERE pupilID = + Input.Text +' is invalid SQL syntax).
Get out of the habit of concatenating SQL immediately, before you even start, and learn to use parameterized queries. Doing so prevents SQL injection, and allows the database driver to properly do conversions of data types and properly quote values when needed so you don't have to do so.
SQLQuery1.SQL.Text := 'SELECT password from TblPupil'#13 + 'WHERE pupilID = :pupilID'; SQLQuery1.ParamByName('pupilID').AsString := InputID.Text; SQLQuery1.Open;
With that being said, the rest of the code in your
UserLogin procedure is pretty bad as well. It's cluttered with unnecessary variables, it has invalid logic (regardless of whether the username and password match, you end up hiding Form1 and showing Form6, which defeats the entire purpose of logging in, AFAICT). You might try something like this instead:
Procedure TForm1.UserLogin; var UserPass: string; begin if InputID.Text = '' then raise Exception.Create('You must enter a Pupil ID.'); SQLQuery1.SQL.Text := 'SELECT password from TblPupil'#13 + 'WHERE pupilID = :pupilID'; SQLQuery1.ParamByName('pupilID').AsString := InputID.Text; try SQLQuery1.Open; if SQLQuery1.IsEmpty then raise Exception.Create('Invalid Pupil ID or password.'); UserPass := SQLQuery1.FieldValues['password']; finally SQLQuery1.Close; end; if UserPass = InputPass.text then begin Form1.Hide; Form16.show; end else raise Exception.Create('Invalid Pupil ID or password.'); end;
It may be the fact that form16 is not created yet. However, more importantly, you need to get delphi stopping when it hits an exception. I suspect you have accidentally turned it off. Depending on your version of Delphi it may be menu/Options/Debugger Options then choose Stop on Delphi Exceptions.
Note also that the 'first' operation fails on an empty dataset so defensively you should have an 'bof' check in there in case the inputid is invalid.
I think you should do something like this (from the code I used in one application):
procedure TForm4.AdvGlowButton1Click(Sender: TObject); begin LOGIN_QUERY.Active:=false; LOGIN_QUERY.SQL.Clear; LOGIN_QUERY.SQL.Add('select user,password,from users where user='+QuotedStr(cxlookupcombobox1.text)+' and password='+QuotedStr(cxTextEdit1.Text)); LOGIN_QUERY.Open; if LOGIN_QUERY.FieldByName('Password').AsString<>'' then Form16.Show else ShowMessage('Wrong Password'); end;
Though your code is a little fussy, I dont understand how comes your table contains only pupilID and a password. Should not there be a pupil name too?