delphi - SQL not finding results

Question

This query currently is returning no results, and it should. Can you see anything wrong with this query

field title are NEED_2_TARGET, ID, and CARD

NEED_2_TARGET = integer

CARD = string

ID = integer

value of name is 'Ash Imp'

{this will check if a second target is needed}
//**************************************************************************
function TFGame.checkIf2ndTargetIsNeeded(name: string):integer;
//**************************************************************************
var
  targetType : integer; //1 is TCard , 2 is TMana , 0 is no second target needed.
begin
    TargetType := 0;
    Result := targetType;
       with adoquery2 do
        begin
          close;
          sql.Clear;
          sql.Add('SELECT * FROM Spells WHERE CARD = '''+name+''' and NEED_2_TARGET = 1');
          open;
        end;

        if adoquery2.RecordCount < 1 then
          Result := 0
       else
         begin
           Adoquery2.First;
           TargetType := adoquery2.FieldByName(FIELD_TARGET_TYPE).AsInteger;
           result := TargetType;
         end;

end;

sql db looks like below

ID  CARD    TRIGGER_NUMBER  CATEGORY_NUMBER QUANTITY    TARGET_NUMBER   TYPE_NUMBER PLUS_NUMBER PERCENT STAT_TARGET_NUMBER  REPLACEMENT_CARD_NUMBER MAX_RANDOM  LIFE_TO_ADD REPLACED_DAMAGE NEED_2_TARGET   TYPE_OF_TARGET
27  Ash Imp     2                   2          15             14                                                                                                                                  1             1

Answer 1

There are a number of things that could be going wrong.

First and most important in your trouble-shooting is to take your query and run it directly against your database. I.e. first confirm your query is correct by eliminating possibilities of other things going wrong. More things confirmed working, the less "noise" to distract you from solving the problem.

1. As others having pointed out if you're not clearing your SQL statement, you could be returning zero rows in your first result set.
   • Yes I know, you've since commented that you are clearing your previous query. The point is: if you're having trouble solving your problem, how can you be sure where the problem lies? So, don't leave out potentially relevant information!
2. Which bring us neatly to the second possibility. I can't see the rest of your code, so I have to ask: are you refreshing your data after changing your query? If you don't Close and Open your query, you may be looking at a previous execution's result set.
   • I'm unsure whether you're even allowed to change your query text while the component is Active, or even whether that depends on exactly which data access component you're using. The point is, it's worth checking.
3. Is your application connecting to the correct database? Since you're using Access, it's very easy to be connected to a different database file without realising it.
   • You can check this by changing your query to return all rows (i.e. delete the WHERE clause).
4. You my want to change the quotes used in your SQL query. Instead of: ...CARD = "'+name+'" ORDER... rather use ...CARD = '''+name+''' ORDER...
   • As far as I'm aware single quotes is the ANSI standard. Even if some databases permit double quotes, using them limits portability, and may produce unexpected results when passed through certain data access drivers.
5. Check the datatype of your CARD column. If it's a fixed length string, then the data values will be padded. E.g. if CARD is char(10), then you might actually need to look for 'Ash Imp '.
   • Similarly, the actual value may contain spaces before / after the words. Use select without WHERE and check the actual value of the column. You could also check whether SELECT * FROM Spells WHERE CARD LIKE '%Ash Imp%' works.

Finally, as others have suggested, you're better off using a parameterised query rather dynamically building the query up yourself.

• Your code will be more readable and flexible.
• You can make your code strongly typed; and so avoid converting things like numbers and dates into strings.
• You won't need to worry about the peculiarities of date formatting.
• You eliminate some security concerns.