CODEWITHSUNDEEP
sqldatabasedelphi
Rohan8088
Rohan8088

Reputation: 15

"Could not convert variant of type (Null) into type (OleStr)" when using numbers in SQL query

I am trying to check if a specific username exists within a database (Access) using SQL. If I use only letter or string characters the code works fine but when I use numbers as in the example below even though I insert them as a string I get the error:

"Could not convert variant of type (Null) into type (OleStr)" 

  DataModule.Query.Active := False;
  sQuery := 'SELECT * FROM Login WHERE Username = "31"';
  DataModule.Query.SQL.Text := sQuery;
  DataModule.Query.Active := True;

I dont know what I'm doing wrong.

edit:

The debugger gives the error just after the if line in the following code

 if UpperCase(DataModule.Query['Username']) = sUsername then
  begin
    if DataModule.Query['Password'] = sPassword then
    begin
      bPass := True;
    end

[Solved]

Thank you all for the advice I got it working!

Upvotes: 1

Views: 12503

Answers (1)

kobik
kobik

Reputation: 21252

You should use Query.FieldByName('Password').AsString (same for Username field) to eliminate any NULL issues. .AsString will convert db NULLs to Delphi empty string ''.

After you open your query you need to test if there are any records before you actually test username/password logic.

Note: You don't need to check the Username again because if the query returned results there is a match and that user exists in your table:

bPass := False;
if not DataModule.Query.IsEmpty then
  if DataModule.Query.FieldByName('Password').AsString = sPassword then
    begin
      bPass := True;
    end

As already mentioned you better use parameterized query. or at least use QuotedStr to avoid SQL injection. e.g.:

sQuery := 'SELECT * FROM Login WHERE Username = ' + QuotedStr('31');

A parameterized query should look like this:

sQuery := 'SELECT * FROM Login WHERE Username = :Username';
DataModule.Query.SQL.Text := sQuery;
DataModule.Query.ParamByName('Username').Value := '31';

Upvotes: 4

Related Questions