Board index » delphi » Tquery and runtime parameters

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

 

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

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

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

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