Parameterized ADO Query 1

[acabrera]

I get this message when an ADO Query with a parameter is activated: NOT ENOUGH STORAGE IS AVAILABLE TO COMPLETE THIS OPERATION. The ADO Query is a simple 'SELECT * FROM COMPANY WHERE CO_KEY=:CO_KEY.' So I just remove the parameter clause using the Object Inspector and define the query and parameter in the code itself. I am able to avoid the error message but another one appears when the ADO query is activated: EOleException with message 'UNSPECIFIED ERROR.'
Our applications run properly using BDE but we get errors when we convert it to ADO.

 

[ChrisGSchultz]

Have you checked that [tt]Parameter.Datatype[/tt] and [tt]Parameter.Value.Type[/tt] matches? Very important!
Your original query may have been retrieving to many records.

Similarly, ensure that your SQL statement meets the syntax criteria of LocalSQL - check out the LocalSQL help (its in [tt]C:\Program Files\Common Files\Borland Shared\BDE[/tt]) if you can't get to it from the IDE. I've found that the SQL syntax can vary somewhat.

Finally, what sort of table is it? You may have to explicitly specify the name in the SQL - viz:
[tt]
SELECT *
FROM "k_upload.dbf" K_Upload
WHERE (((ExamCode NOT LIKE 'n125' + '%') OR (ExamCode IS NULL))
AND
(ExamDate BETWEEN :startdate AND :enddate))
ORDER BY ExamDate,Scanner
[/tt]

Cheers


Chris ;-)

 

[acabrera]

Hi Chris,

Thanks for your suggestion which worked. What concerns me now is that in converting our Delphi-BDE applications to ADO, we have to do a lot of coding because all the ADO parameterized queries have to be coded in the application. In our BDE applications, they were just defined using the Object Inspector and Parameter Collection Editor. Would you know how to use the Object Inpector in defining ADO queries without getting the 'Not Enough Storage' error? Thanks.

acabrera

 

[ChrisGSchultz]

Not exactly sure of the problem here, but it sounds like you hard code the parameters.
When you create the query, the parameters should automatically be placed in the Parameter inspector.
If you want to hard code the parameters, just use the Parameter collection editor (the elipsis next to [tt]Params[/tt]). Then
[ul][li]set [tt]Params.Value.Type[/tt] to your parameters data type (date, OleString etc)[/li]
[li] then enter the value in the [tt]Params.Value[/tt] field[/li][/ul]

However, its not that hard to programattically alter the parameters either. An example of how I set up queries in linked tables (ACCESS in this case):[tt]
procedure AdjustBMDQuery;
{ Redo the ROI query to get all Densitometry records with same img_handle
The SQL statement:
SELECT img_handle, label, bmd
FROM Densitometry
WHERE Densitometry.img_handle = :image
ORDER by label;
}
begin
ADOquery_prevBMD.close; // redefine the BMD query for the specified ROI
ADOquery_prevBMD.Parameters.ParamByName('image').value :=
ADOquery_image.FieldValues['img_handle']; // get the ID of the image
ADOquery_prevBMD.open;
end; // procedure adjust query[/tt]

Finally, there are a couple of ways to check that your SQL is correct:
[ol][li] Put a DBGrid on your form, and link it to your table.
Set [tt]Active[/tt] to [tt]TRUE[/tt]
You can set the [tt]Parameters[/tt] as described above. Your data should appear in the grid[/li]
[li]Use the Database Desktop and test your query using SQL directly - you can't use parameters here, but substitute them directly viz:
[tt]SELECT ExamDate,ExamCode,Scanner,BillCode
FROM "k_upload.dbf" K_Upload
WHERE (((ExamCode NOT LIKE 'n125' + '%') OR (ExamCode IS NULL))
AND
(ExamDate BETWEEN '1.07.2003' AND '31.08.2003'))
ORDER BY ExamDate[/tt][/li]
[/ol]

Incidentally, I use the same sort of queries on both a BDE query and an ADO query.

Cheers



Chris ;-)

 

[acabrera]

Hi Chris,

Thanks for your reply. We are forced to hardcode our parameters because our ADO does not allow parameterized queries using the Object Inspector and Parameter Collection Editor. The message I got was 'Parameter object is improperly defined.' This came about from:

1. renaming the BDE TQuery called QueryCOMPANY to QueryCOMPANY1; copying its SQL statement
2. adding an ADO Query called QueryCOMPANY (used same name as original query to minimize changes in code)
3. pasting the SQL statement to the ADO Query
4. adding the persistent fields to the ADO Query

Because of the said error, I was forced to define the parameterized ADO query in the application and it is quite cumbersome. I do not understand why our Object Inspector works with BDE but not with ADO updated with d5adoupdate2. Your replies are always appreciated.

 

[ChrisGSchultz]

Hi again Acabrera!

Looks like your using Delphi 5 - I'm quoting these object parameters from D6 & D7.

I believe that the ADO object was in its infancy with D5, even though it draws on MDAC and Jet for most of its properties. Perhaps the limitations you are encountering are related to that, and the properties aren't published for use in the inspector.

Sorry I can't be more specific on this, but some of the other members might know more about ADO differences between versions.

Good luck!

Chris ;-)