CODEWITHSUNDEEP
delphiparametersdelphi-7ado
GuidoG
GuidoG

Reputation: 12014

Error when using parameter in ADOQuery

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.

Upvotes: 3

Views: 2063

Answers (1)

MartynA
MartynA

Reputation: 30715

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 ...

Upvotes: 3

Related Questions