Tquery and runtime parameters

Graeme Cox

Delphi Developer

Wed, 18 Jun 1902 08:00:00 GMT

Tquery and runtime parameters

Greetings,

Problem : I am trying to pass runtime values into a tquery... it all
worked fine
using Delphi4 and Access97... now we have changed over to D4 and
SqlServer it comes
up with 'Operation not Applicible'.. on anything other than a integer
being passed.
(Although when I pass an integer the 'LIKE' is an '=' , and it works
fine )

for example..

MySql.SQL.Add('Select * from tbl_MyStuff where MyField LIKE
:PrmNumber);
MySql.Params[0].AsString := 'Thingy';
MySql.Open; ------><Error>

This worked in Access...?????
Can you pass strings ???
is there another or better way to do it?

Thanks

Graeme

Glen Meredit

Delphi Developer

Wed, 18 Jun 1902 08:00:00 GMT

Re:Tquery and runtime parameters

You might have alreay done this, but did you declare the parameter type
and data type of your param from the Params property of the Object
Inspector? If you are creating your Query dynamically, you will need to
do a CreateParam. Here is code for that - It is for a TStoredProc though
- s/b the same for TQuery.::

Params.CreateParam(ftInteger, '@check_num', ptInput);
ParamByName('@check_num').AsInteger :=
StrToInt(edtcheck_no.Text);

Help?

-Glen

Quote

Graeme Cox wrote:
> Greetings,

> Problem : I am trying to pass runtime values into a tquery... it all
> worked fine
> using Delphi4 and Access97... now we have changed over to D4 and
> SqlServer it comes
> up with 'Operation not Applicible'.. on anything other than a integer
> being passed.
> (Although when I pass an integer the 'LIKE' is an '=' , and it works
> fine )

> for example..

> MySql.SQL.Add('Select * from tbl_MyStuff where MyField LIKE
> :PrmNumber);
> MySql.Params[0].AsString := 'Thingy';
> MySql.Open; ------><Error>

> This worked in Access...?????
> Can you pass strings ???
> is there another or better way to do it?

> Thanks

> Graeme

Loi Tha

Delphi Developer

Wed, 18 Jun 1902 08:00:00 GMT

Re:Tquery and runtime parameters

If your query is a live query, the fieldname and/or tablename is case
sensitive. Be sure to have the matched exactly with the definition,
or You will receive the same error.

Loi.

Quote

Graeme Cox wrote in message <364B7EAF.699B9...@innovonics.com.au>...
>Greetings,

>Problem : I am trying to pass runtime values into a tquery... it all
>worked fine
>using Delphi4 and Access97... now we have changed over to D4 and
>SqlServer it comes
>up with 'Operation not Applicible'.. on anything other than a integer
>being passed.
>(Although when I pass an integer the 'LIKE' is an '=' , and it works
>fine )

>for example..

> MySql.SQL.Add('Select * from tbl_MyStuff where MyField LIKE
>:PrmNumber);
> MySql.Params[0].AsString := 'Thingy';
> MySql.Open; ------><Error>

>This worked in Access...?????
>Can you pass strings ???
>is there another or better way to do it?

>Thanks

>Graeme

Russell L. Smit

Delphi Developer

Wed, 18 Jun 1902 08:00:00 GMT

Re:Tquery and runtime parameters

If ParamCheck is True (the default), then using CreateParam is unnecessary.
When the SQL is changed, the VCL will automatically create the necessary
params. I think Graeme's real problem is that the LIKE operator is
typically used with some sort of wildcards. For example, the following code
fragment will dynamically create a TQuery against an Oracle database and
query all personnel rows that start with "S". In Oracle, the "%" wildcard
is like a "*". I don't know whether SQL Server uses wildcards, but it seems
likely.

with TQuery.Create(Self) do
try
DatabaseName := dbMain.DatabaseName;
SQL.Add('select LAST_NM, FIRST_NM, ORACLE_ID');
SQL.Add('from PERSONNEL');
SQL.Add('where LAST_NM like :LAST_NM');
ParamByName('LAST_NM').Value := 'S%';
Open;
ShowMessage(IntToStr(RecordCount));
finally
Free;
end;

Note that I didn't even programmatically type the parameter by setting
ParamType prior to opening the query. Most of the time you don't have to,
because the parameter value assignment will do it for you.

V/R
Russell L. Smith

Quote

Glen Meredith wrote in message <364C31FE.14509...@landasystems.com>...
>You might have alreay done this, but did you declare the parameter type
>and data type of your param from the Params property of the Object
>Inspector? If you are creating your Query dynamically, you will need to
>do a CreateParam. Here is code for that - It is for a TStoredProc though
>- s/b the same for TQuery.::

> Params.CreateParam(ftInteger, '@check_num', ptInput);
> ParamByName('@check_num').AsInteger :=
>StrToInt(edtcheck_no.Text);

>Help?

>-Glen

>Graeme Cox wrote:

>> Greetings,

>> Problem : I am trying to pass runtime values into a tquery... it all
>> worked fine
>> using Delphi4 and Access97... now we have changed over to D4 and
>> SqlServer it comes
>> up with 'Operation not Applicible'.. on anything other than a integer
>> being passed.
>> (Although when I pass an integer the 'LIKE' is an '=' , and it works
>> fine )

>> for example..

>> MySql.SQL.Add('Select * from tbl_MyStuff where MyField LIKE
>> :PrmNumber);
>> MySql.Params[0].AsString := 'Thingy';
>> MySql.Open; ------><Error>

>> This worked in Access...?????
>> Can you pass strings ???
>> is there another or better way to do it?

>> Thanks

>> Graeme

Graeme Co

Delphi Developer

Wed, 18 Jun 1902 08:00:00 GMT

Re:Tquery and runtime parameters

I know how to set up the parameters, (very positve I am doing it right).

The problem is that I cannot pass a string as a parameter without
a 'Operation not applicble' being displayed. The only time I can
get a string passed is if I actually put it in the Sql statement.(but not

passed as a parameter).
The spelling of the table names and such are also correct. And it is not
the
use of the 'LIKE' statement as it will tell me that I need a '%' in it.

It does it if I set it up dynamically or not

Has anybody else had this problem with Sql Sever 6.5.. Delphi 4...

Graeme

Other Threads

1. adding a TQuery parameter add runtime

2. How to pass parameter to TQuery and retrieve value from TQuery

3. Obtaining NextVal from Oracle via Delphi TQuery and parameters

4. Adding parameters to a TQuery component?

5. Q : TQuery and SQL and IN and Parameters ?

6. ODBC: How to avoid parameter substitution in TQuery?

7. TQuery : Invalid Parameter

8. TQuery and "Invalid Parameter"

9. HELP - Setting TQuery Parameters to Null

10. Can TQuery handle table names as parameters?