Error when using parameter in ADOQuery

Question

I have this simple code to check if a record exists in a table, but it always returns a runtime error :

Arguments are of the wrong type, are out of acceptable range, or are in conflict with one another.

my code is this :

function TDataModuleMain.BarCodeExists(barCode: string): boolean;
begin
   if ADOQuerySql.Active then
     ADOQuerySql.Close;

   ADOQuerySql.SQL.Clear;
   ADOQuerySql.SQL.Text := 'select count(1) from Card where BarCode = (:TestBarcode)';
   ADOQuerySql.Parameters.ParamByName('TestBarcode').Value := barCode;

   ADOQuerySql.Open; // HERE THE RUNTIME ERROR APPEARS

   Result := ADOQuerySql.Fields[0].AsInteger = 1;
   ADOQuerySql.Close;
   ADOQuerySql.Parameters.Clear;
end;

The field BarCode in table Card is of type nvarchar(100)

In debug I see that the parameter is created, and gets populated with the correct value.
Running the query in sql server management studio also works.

I also found this How to pass string parameters to an TADOQuery? and checked my code with the code in the answer but I don't see any problems here.
Also this AdoQuery Error using parameters did not help me.

It will no doubt be something very simple that I have missed but I just dont see it now.

EDIT : things I tried from suggestions in the comments:

.ParamCheck := True (default)  
.Parameters.ParamByName('TestBarcode').DataType := ftString  
.Parameters.ParamByName('TestBarcode').DataType := ftWideString

None of these worked however.

What did help was using a non-shared AdoQuery for this, and that one did the job without any errors. I am using that now as the solution but I am still looking at the shared AdoQuery out of curiousity what the exact problem is.

EDIT: the source of the problem is found.

I used the function provided by MartinA to examine both the dynamic created query and the shared AdoQuery and I found one difference.
The shared AdoQuery had the this property filled :

ExecuteOption := [eoExecuteNoRecords]

and the dynamic created query does not.
Since this property is not set in designtime I did not see it. After clearing the property to [] the shared AdoQuery worked again.
I am going to switch to using non shared AdoQuery for this kind of work as been suggested.

Thanks everyone for your assistance.

It appears you're reusing this query for other things. Try moving your `Parameters.Clear` operation from the bottom to the top, just after `Close`. — Jerry Dodge, Aug 23 '16 at 20:29
@JerryDodge I have just tried that it did not help. I do suppose you where talking about the Parameters.Clear; ? — GuidoG, Aug 23 '16 at 20:31
@JerryDodge I tried it as the very first code in the function but it did not help — GuidoG, Aug 23 '16 at 20:33
As a test, try using a different un-shared query. Also, does this query have any events assigned, such as `BeforeOpen`? — Jerry Dodge, Aug 23 '16 at 20:34
@JerryDodge I have created a new AdoQuery in this function and now it works. So I guess something from a previous operation is causing my problem. Thanks for your suggestion — GuidoG, Aug 23 '16 at 20:38
Indeed, something performing a search such as checking if a record exists I presume may be called many different times in a row. I wouldn't embed the query inside this function, I'd make a global one dedicated for this purpose - for performance reasons. Re-using queries might sound like a good idea, but can lead to many issues such as this one. — Jerry Dodge, Aug 23 '16 at 20:40
Try to use type-safe properties `ADOQuerySql.Parameters.ParamByName('TestBarcode').AsString := barCode;`. I do not know about ADO, but in both BDE and DBX assigning to type-safe properties does set the data type. — Arioch 'The, Aug 23 '16 at 20:45
Alternatively try setting data type before setting Value - http://docwiki.embarcadero.com/Libraries/XE8/en/Data.DB.TParam.DataType - but still I definitely suggest against using slow and unsafe .VALUE property when you know the data type in advance — Arioch 'The, Aug 23 '16 at 20:47
you may remove `ADOQuerySql.SQL.Clear;` - it is totally redundant here, calling `.Text := ` would do it for you. `.Clear` is only needed when you call `SQL.Add` instead of assigning to `.Text`. // I also suggest `preparing` the SQL request before setting parameter datatype and value. I dunno how ADOQuery behaves in D7, but with usual database libraries it would be `ADOQuerySql.Prepared := true;` or `ADOQuerySql.Prepare();` right before working with parameters — Arioch 'The, Aug 23 '16 at 20:51
@Arioch'The There seems to be no parameters.ParamByName().AsString method in D7. I can only find a .Value — GuidoG, Aug 23 '16 at 20:53
try to look into ADOQeury sources, perhaps there is soething like FieldsType or Datatype then. also try to remove the unneeded brackets ` '..... where BarCode = :TestBarcode' ` — Arioch 'The, Aug 23 '16 at 21:17
@Arioch'The The brackets came from another question (see second link in my question). Also when I create a new ADOQuery like suggested by Jerry than it works. In debug the parameter is type ftWideString and has the correct value. The Parameters.Count = 1. It just does not makes any sense to me — GuidoG, Aug 23 '16 at 21:22
Well, if an unshared AdoQuery works, all you have to do is to inspect your form's DFM and compare the properties of your original ADoQuery with the unshared one. The answer should lie in the difference(s). — MartynA, Aug 23 '16 at 21:27
the unshared query I tried was created dynamicly so its hard to compare, but the shared adoquery has nothing at all setup in design time because it is used for all the dirty work. It never gave any problems before but its the first time I use a parameter on this adoquery — GuidoG, Aug 23 '16 at 21:31
why is it hard to compare & copy both components to Notepad and compare them line by line, property by property. You also might use diff tools like WinMerge but for one single component I think two Notepad windows would be easier to use — Arioch 'The, Aug 23 '16 at 22:09
` In debug the parameter is type ftWideString ` - in which property of TParam do you see it? IIn the DataType I mentioned earlier? Then try setting DataType like I suggested above before assigning Value! — Arioch 'The, Aug 23 '16 at 22:09
@Arioch'The It was hard to compare because the new query is created in code so there is nothing to copy from the dfm. The DataType is ftWideString as it should be, so I guess setting it again to ftWideString wont make any change ? — GuidoG, Aug 23 '16 at 22:11
@Arioch'The: Judging by the OP's reply to me the difficulty with comparing the components arises because one is created dynamically - that's why I posted my "answer", to make the comparison a bit easier. — MartynA, Aug 23 '16 at 22:11
guess the one who did it would not answer, if he wanted to say - he already would — Arioch 'The, Aug 23 '16 at 22:42
check data type of parameters at both queries - working one and not-working one — Arioch 'The, Aug 23 '16 at 22:43
@GuidoG, My guess down vote is because you seek debugging help. You need to provide MCVE so we are able to **reproduce**, and not guess what might be wrong. You need to debug harder. to address your problem, I would have used a local AdoQuery for this purpose and not shared "junk" query for all tasks. also see: [Fastest way to determine if record exists](http://stackoverflow.com/questions/18114458/fastest-way-to-determine-if-record-exists) — kobik, Aug 24 '16 at 08:03
@kobik Thanks for your input, but I am providing all the MCVE I can give and its a problem that is very hard to reproduce. In my opinion I have done all that is required, I told the problem, I showed complete actual code, I told what I tried and researched so far. So I am a bit confused here. — GuidoG, Aug 24 '16 at 08:25
Have you tried `.ParamCheck := True` (default) and `.Parameters.ParamByName('TestBarcode').DataType := ftString` — kobik, Aug 24 '16 at 08:31
yes I did just as Arioch suggested. Also I am using an unshared adoquery now as Jerry suggested I am only still looking at this out of curiousity why the error appeared. — GuidoG, Aug 24 '16 at 08:33
Look, you answered this only after being asked three times! and you did not updated the question so the info in buried deep inside loooong comments forum. It is not making it easier.... — Arioch 'The, Aug 24 '16 at 08:43
@Arioch'The I am sorry I had no idea I had to update my question with this info. Also as I said before I am only still looking at this out of curiousity because I am using the suggestion from Jerry. I will update my question though — GuidoG, Aug 24 '16 at 09:11
just think from the perspective of those who are reading ur question. The more efforts they need to understand the issue - the less incentive they would have to even try. — Arioch 'The, Aug 24 '16 at 09:35
@GuidoG: "looking at this out of curiousity" Bravo! Ime, its always better to try and get to the bottom of a problem like this, time permitting, even if you have a viable work-around. Anyway, good luck! — MartynA, Aug 24 '16 at 10:43
@MartynA, I would not call it a "work-around". IMO, this is the solution. I also have learned the hard way that using shared queries could potentially cause bugs. (BTW, the same goes for persistent fields). *anything* could change in the life-time of a shared query, so this needs extra care of initializing the shared query each time before using it. saying that I'm also curious to know what caused this specific bug. I could not reproduce no matter how I tried. — kobik, Aug 24 '16 at 11:09
To add, if anyone insists on re-using queries, there should at least be some sort of `ResetQuery` procedure which puts it back into its default state before using. That's how our company's (21 year old) software was designed. I hate it, but that's how everything's been written. — Jerry Dodge, Aug 24 '16 at 19:46

MartynA · Accepted Answer · 2016-08-23T22:30:23.130

3

The following isn't intended to be a complete answer to your q, but to follow up my comment that "all you have to do is to inspect your form's DFM and compare the properties of your original ADoQuery with the unshared one. The answer should lie in the difference(s)" and your reply that the unshared query is created dynamically.

There is no "voodoo" involved in the difference in behaviour between your two ADOQuerys. It's just a question of capturing what the differences actually are.

So, what you need, to debug the problem yourself, is some code to compare the properties of two components, even if one or both of them is created dynamically. Using the following routine on both components will enable you to do exactly that:

function TForm1.ComponentToString(AComponent : TComponent) : String;
var
  SS : TStringStream;
  MS : TMemoryStream;
  Writer : TWriter;
begin
  //  Note:  There may be a more direct way of doing the following, without
  //  needing the intermediary TMemoryStream, MS
  SS := TStringStream.Create('');
  MS := TMemoryStream.Create;
  Writer := TWriter.Create(MS, 4096);

  try
    Writer.Root := Self;
    Writer.WriteSignature;
    Writer.WriteComponent(AComponent);
    Writer.FlushBuffer;
    MS.Position := 0;
    ObjectBinaryToText(MS, SS);
    Result := SS.DataString;
  finally
    Writer.Free;
    MS.Free;
    SS.Free;
  end;
end;

Over to you ...

edited Aug 23 '16 at 22:30

answered Aug 23 '16 at 21:53

MartynA

30,454
4
32
73

Thanks I will try this tomorrow because it is about midnight here now. – GuidoG Aug 23 '16 at 21:55
@GuidoG: No probs - it's 11 here so I know the feeling. – MartynA Aug 23 '16 at 22:01
@GuidoG: Personally, I'm sympathetic to the idea that it could be very hard to come up with an MCVE for a problem which appears randomly and only v. infrequently. Btw, in case it wasn't obvious, I was thinking you could use the above code to capture the properties of the shared query in an except block when the problem occurs. – MartynA Aug 24 '16 at 08:35
Martin I like that idea. I can put this in my log file – GuidoG Aug 24 '16 at 08:39
I found the cause of the problem, see my updated question. – GuidoG Aug 24 '16 at 18:21
@kobik Yes I agree and I just did. – GuidoG Aug 26 '16 at 12:18

Error when using parameter in ADOQuery

1 Answers1